createtrigger name (触发器名称)after/befor(触发时间)insert/update/delete(触发事件,当填写其中一种,其他的操作不会激活触发器)on(监视的表,在哪张表上建立触发器)for each row(行级触发器)begin(sql语句,希望改变的数据,可写多条语句,尽量不操作多次一张表,每条语句必须使用;结尾)end;
insert_trigger
BEGINif(NEW.id notin(select sourceid from site_newsall where catid = NEW.catid and sourceid = NEW.id ))theninsertinto table1 (value1,value2,value3)values(NEW.value1,NEW.value2,NEW.value3);endif;END
update_trigger
BEGINif(NEW.id notin(select sourceid from site_newsall where catid = NEW.catid and sourceid = NEW.id ))theninsertinto table1 (value1,value2,value3)values(NEW.value1,NEW.value2,NEW.value3);ELSEUPDATE table1 SET value1=NEW.value1,value2=NEW.value2,value3=NEW.value3 where id=NEW.ID;endif;END
delete_trigger
BEGINif( OLD.id in(select id from table1 where id = OLD.id ))thenDELETEFROM table1 where id=OLD.id;endif;END
当前表更新后修改当前表
BEGINset NEW.field1 =(SELECT*FROM(SELECT id from table1 WHERE id = NEW.id )AS TEM1);set NEW.field2 =(select*from(selectsum(number)from table3 where userid = NEW.userid));END;