mysql 触发器

参考

• ERROR 1442 (HY000): Can’t update table ‘salary’ in stored function/trigger because it is already use
• oracle中表insert or update or delete触发器

触发器涉及触发表自身的操作时，用before，要删去update 表名。

set new.status=1; ✔
update test set status=1 where sno=new.sno; ✘

delimiter $$
create trigger setstatus before update on test for each row
begin
if new.math<60 or new.chinese<60 then
	set new.status=1;
else
	set new.status=0;
end if;
end$$
delimiter ;

在一个表（User_Info_2017）上放置一个触发器，当对它进行insert，update，delete的操作时，触发器（UserToTemp）启动，把相应的变动数据根据需要放到另外一个表（User_Info_2017_temp）中

create or replace trigger UserToTemp after insert or update or delete
on User_Info_2017 for each row
declare
    integrity_error exception;
    errno            integer;
    errmsg           char(200);
    dummy            integer;
    found            boolean;
 
begin
if inserting then
    insert into User_Info_2017_temp(ID,UserName,PassWord,CreateDate,Status) values(:NEW.ID,:NEW.UserName,:NEW.PassWord,:new.CreateDate,:NEW.Status);
elsif updating then
    update User_Info_2017_temp set ID=:NEW.ID,UserName=:NEW.UserName,PassWord=:NEW.PassWord,Status=:NEW.Status where id=:OLD.id;
elsif deleting then
    delete from User_Info_2017_temp where id=:OLD.id;
end if;
exception
    when integrity_error then
       raise_application_error(errno, errmsg);
end;