一般用于在插入表时,检测插入的值,或者在更新时计算某个值。

应用在insert,update,delete某个表时方可操作

语法:

    创建触发器create trigger 触发器名称 before/after insert/update/delete  on 表名 for each row sql语句

如:

    create trigger cal_num before insert on test.account for each row set @sum=@sum+new.amount;

如果有多条sql语句,需要用begin...end包裹sql语句如:

mysql> delimiter //
mysql> CREATE TRIGGER upd_check BEFORE UPDATE ON account
-> FOR EACH ROW
-> BEGIN
-> IF NEW.amount < 0 THEN
-> SET NEW.amount = 0;
-> ELSEIF NEW.amount > 100 THEN
-> SET NEW.amount = 100;
-> END IF;
-> END;//
mysql> delimiter ;

每一列都有个OLD,NEW OLD表示旧行【delete/update】,NEW表示新行[insert/update]