MYSQL 触发器之Can't update table 'student' in stored function/trigger

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只能借助第二张表才能实现需求，如果可以最好用存储过程代替触发器