mysql> select auto_increment from information_schema.tables where table_name='e' ; +----------------+ | auto_increment | +----------------+ | 10 | +----------------+ 1 row in set (0.00 sec)
CREATE TRIGGER tr_xinhong_bi BEFORE INSERT ON phome_ecms_audio FOR EACH ROW BEGIN SET new.name = new.id; end;MySQL 返回: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
mysql> delimiter | mysql> mysql> CREATE TRIGGER tr_rwxdfbb_bi BEFORE INSERT ON t_rwxdfbb -> FOR EACH ROW BEGIN -> if new.name is null then -> set new.name=new.id; -> end if; -> END; -> | Query OK, 0 rows affected (0.20 sec)mysql> mysql> delimiter ; 建议你能象这样贴你的代码,否则猜不出你错在哪儿。
mysql> use empirecms Database changed mysql> delimiter | mysql> CREATE TRIGGER tr_xinhong_bi BEFORE INSERT ON phome_ecms_audio -> FOR EACH -> ROW BEGIN -> SET new.filename = new.id; -> end; -> | ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers with the same action time and event for one table'
This version of MySQL doesn't yet support 'multiple triggers你的表上已经有了一个触发器了,删除。
按照我的想法,每次插入记录的时候,不理会id和name,只插入其他字段,但是这样操作的结果是:id仍然递增,但是name却一直为0,我估计是插入前触发造成的,这个时候id并没有值,因此name也读不到值,我想改为after触发,发现有语法错误“Updating of NEW row is not allowed in after trigger”,这种情况该怎么实现?
Query OK, 0 rows affected (0.38 sec)mysql> delimiter |
mysql>
mysql> CREATE TRIGGER tr_rwxdfbb_bi BEFORE INSERT ON t_rwxdfbb
-> FOR EACH ROW BEGIN
-> if new.name is null then
-> set new.name=new.id;
-> end if;
-> END;
-> |
Query OK, 0 rows affected (0.20 sec)mysql>
mysql> delimiter ;
mysql>
mysql> insert into t_rwxdfbb (id) values ('rwxdfbb');
Query OK, 1 row affected (0.33 sec)mysql> select * from t_rwxdfbb;
+---------+---------+
| id | name |
+---------+---------+
| rwxdfbb | rwxdfbb |
+---------+---------+
1 row in set (0.00 sec)mysql> insert into t_rwxdfbb (id,name) values ('rwxdfbb2','xxxxxx');
Query OK, 1 row affected (0.06 sec)mysql> select * from t_rwxdfbb;
+----------+---------+
| id | name |
+----------+---------+
| rwxdfbb | rwxdfbb |
| rwxdfbb2 | xxxxxx |
+----------+---------+
2 rows in set (0.00 sec)mysql>
;
+----------------+
| auto_increment |
+----------------+
| 10 |
+----------------+
1 row in set (0.00 sec)
CREATE TRIGGER tr_xinhong_bi BEFORE INSERT ON phome_ecms_audio
FOR EACH ROW BEGIN
SET new.name = new.id;
end;MySQL 返回: #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '' at line 3
mysql>
mysql> CREATE TRIGGER tr_rwxdfbb_bi BEFORE INSERT ON t_rwxdfbb
-> FOR EACH ROW BEGIN
-> if new.name is null then
-> set new.name=new.id;
-> end if;
-> END;
-> |
Query OK, 0 rows affected (0.20 sec)mysql>
mysql> delimiter ;
建议你能象这样贴你的代码,否则猜不出你错在哪儿。
Database changed
mysql> delimiter |
mysql> CREATE TRIGGER tr_xinhong_bi BEFORE INSERT ON phome_ecms_audio
-> FOR EACH
-> ROW BEGIN
-> SET new.filename = new.id;
-> end;
-> |
ERROR 1235 (42000): This version of MySQL doesn't yet support 'multiple triggers
with the same action time and event for one table'
不过还有一个问题,因为我的id是自动增加的,所以我每次插入时没有管id这个字段,这样的话,name字段也没有值,必须要指定id的值,name才能生效,有没有什么办法实现全自动?