各位大哥:
小弟想 一条信息插入到prj表后 得到这条信息的id,在通过触发器将这个id插入到reportnum表中 我该怎么做呢,小弟第一次用mysql,这样写不对,不知道该怎么写CREATE TRIGGER add_reportnum BEFORE insert ON prj
FOR EACH ROW
BEGIN
select id from prj order by id desc LIMIT 1;
set @id=NEW.id;
insert into reportnum(prjid,num) values(@id,0);END;
delimiter //
小弟想 一条信息插入到prj表后 得到这条信息的id,在通过触发器将这个id插入到reportnum表中 我该怎么做呢,小弟第一次用mysql,这样写不对,不知道该怎么写CREATE TRIGGER add_reportnum BEFORE insert ON prj
FOR EACH ROW
BEGIN
select id from prj order by id desc LIMIT 1;
set @id=NEW.id;
insert into reportnum(prjid,num) values(@id,0);END;
delimiter //
FOR EACH ROW
BEGIN
insert into reportnum(prjid,num) values(NEW.id,0);
END;
当您的问题得到解答后请及时结贴.
http://topic.csdn.net/u/20090501/15/7548d251-aec2-4975-a9bf-ca09a5551ba5.html
错误码: 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 'insert into reportnum(prjid,num) values(NEW.id,0)' at line 4Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000错误码: 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 'END' at line 1Execution Time : 00:00:00:000
Transfer Time : 00:00:00:000
Total Time : 00:00:00:000
FOR EACH ROW
BEGIN
insert into reportnum(prjid,num) values(NEW.id,'0');
END;
试试这个
Query OK, 0 rows affected (0.11 sec)mysql> create table reportnum(prjid int ,num int);
Query OK, 0 rows affected (0.05 sec)mysql>
mysql> delimiter //
mysql> CREATE TRIGGER add_reportnum BEFORE insert ON prj
-> FOR EACH ROW
-> BEGIN
-> insert into reportnum(prjid,num) values(NEW.id,0);
-> END;
-> //
Query OK, 0 rows affected (0.16 sec)mysql> delimiter ;
mysql> select * from prj;
Empty set (0.02 sec)mysql> select * from reportnum;
Empty set (0.00 sec)mysql> insert into prj values (1);
Query OK, 1 row affected (0.23 sec)mysql> select * from prj;
+----+
| id |
+----+
| 1 |
+----+
1 row in set (0.00 sec)mysql> select * from reportnum;
+-------+------+
| prjid | num |
+-------+------+
| 1 | 0 |
+-------+------+
1 row in set (0.00 sec)mysql>
Query OK, 1 row affected (0.04 sec)mysql> select * from report;
+----+----------+-------+---------+
| id | rfcardid | prjid | buildid |
+----+----------+-------+---------+
| 5 | 2 | 2 | 3 |
+----+----------+-------+---------+
1 row in set (0.00 sec)mysql> select * from reportnum;
Empty set (0.00 sec)mysql>
mysql> select *from reportnum;
+----+-------+------+
| id | prjid | num |
+----+-------+------+
| 7 | 0 | 0 |
+----+-------+------+
1 row in set (0.00 sec)