1.创建触发器
create trigger stu_trigger before insert on student
for each row
BEGIN
update student set age = age+2 where id > "003";
end;
2.可以创建成功但是执行insert 操作
INSERT INTO student (id,name,age,sex,date) values("008","小倩",20,"女",NOW());
报错:Can't update table 'student' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
3.将触发器修改为
create trigger stu_trigger BEFORE insert on student
for each row
set NEW.age = 25;
然后执行insert操作成功
update 只能用set进行操作,上面是报错信息.由于我是 对一张表同时进行 插入和修改操作,我起初怀疑就是mysql的 触发器不支持这种操作,经过百度证实这一点!
4.另外一种
create trigger stu_trigger after delete on student
for each row
BEGIN
update student set age = age+2 where id > "003";
end;
5.执行delete语句失败报相同的错误
delete from student where id = "001"
报错:报错:Can't update table 'student' in stored function/trigger because it is already used by statement which invoked this stored function/trigger.
总结:
1. MYSQL中触发器中不能对本表进行 insert ,update ,delete操作,以免递归循环触发
2. 对于update 只能用set进行操作,insert与delete只能借助第二张表才能实现需求,如果可以最好用存储过程代替触发器