表list1如下:
id mname singerid
1 a 5
2 b 7
3 d 3
4 f 2
5 x 5
6 u 8
表list2的结构和list1相同,但里面内容为空
想建立一个触发器关联表list1,让list1中添加一条记录时,list2也根据条件增加一条记录,加入后删除list2中最早插入的一条记录,加入list2的条件是group by singerid order by id desc limit 1 ,没用触发器时的SQL:
insert into list2 select * from list1 group by singerid order by id desc limit 1;
删除list2中最早的一条记录是DELETE FROM list2 order by id asc limit 1;
为什么这样不对?
¦
CREATE TRIGGER listtrigger2 BEFORE INSERT ON list1
FOR EACH ROW
BEGIN
INSERT INTO list2 SET id=new.id,mname=new.mname,singerid=new.singerid group by singerid order by new.id desc limit 1;
DELETE FROM list2 order by id asc limit 1;
END
¦
这个触发器怎么写?
id mname singerid
1 a 5
2 b 7
3 d 3
4 f 2
5 x 5
6 u 8
表list2的结构和list1相同,但里面内容为空
想建立一个触发器关联表list1,让list1中添加一条记录时,list2也根据条件增加一条记录,加入后删除list2中最早插入的一条记录,加入list2的条件是group by singerid order by id desc limit 1 ,没用触发器时的SQL:
insert into list2 select * from list1 group by singerid order by id desc limit 1;
删除list2中最早的一条记录是DELETE FROM list2 order by id asc limit 1;
为什么这样不对?
¦
CREATE TRIGGER listtrigger2 BEFORE INSERT ON list1
FOR EACH ROW
BEGIN
INSERT INTO list2 SET id=new.id,mname=new.mname,singerid=new.singerid group by singerid order by new.id desc limit 1;
DELETE FROM list2 order by id asc limit 1;
END
¦
这个触发器怎么写?
解决方案 »
免费领取超大流量手机卡,每月29元包185G流量+100分钟通话, 中国电信官方发货