insert,delete,update时候旧数据和新数据,会在内存中生成临时的行数据,分别叫old和new。例如要insert插入一条数据的时候,会先将这行数据放在内存中,叫new临时表。update的时候,会先将更新之前的数据放在内存old表中,即将更新的数据放在内存new表中。
create trigger t1
after
insert
on ord
for each row #固定写法,为的是批量操作
begin
update good set good_num = good_num - new.ord_num where good_id=new.good_id;
end$
insert --ord表,因此插入的ord表数据为新数据,叫new数据,new old 是关于ord表的称谓
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 ;
mysql> update account set amount=-10 where acct_num=137;
mysql> select * from account;
+----------+---------+
| acct_num | amount |
+----------+---------+
| 137 | 0.00 |
| 141 | 1937.50 |
| 97 | -100.00 |
+----------+---------+
mysql> update account set amount=200 where acct_num=137;
mysql> select * from account;
+----------+---------+
| acct_num | amount |
+----------+---------+
| 137 | 100.00 |
| 141 | 1937.50 |
| 97 | -100.00 |
+----------+---------+
创建了一个名为trig1的触发器,一旦在work表中有插入动作,就会自动往time表里插入当前时间
mysql> CREATE TRIGGER trig1 AFTER INSERT
-> ON work FOR EACH ROW
-> INSERT INTO time VALUES(NOW());
2、创建有多个执行语句的触发器
CREATE TRIGGER 触发器名 BEFORE|AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN
执行语句列表
END;
work
time