- 触发器是在插入、更新和删除语句前后自动执行的一堆SQL代码,但是触发器被触发后只会执行一次,通常我们使用触发器增强数据的一致性。
- 创建触发器
-
-- 创建触发器 drop trigger if exists payments_after_insert; delimiter $$ -- 在 payments表 insert 之后 触发 create trigger payments_after_insert after insert on payments for each row -- 作用于受影响的每一行 begin update invoices set payment_total=payment_total + new.amount where invoice_id = new.invoice_id; end $$ delimiter ; insert into payments values(default,5,3,'2019-01-01',10,1); select * from invoices; -- delete drop trigger if exists payments_after_delete; delimiter $$ create trigger payments_after_delete after delete on payments for each row begin update invoices set payment_total=payment_total-old.amount where invoice_id=old.invoice_id; end$$ delimiter ; delete from payments where invoice_id=3;
-
- 查看触发器
- show triggers like'payments%'
- 使用触发器进行审计
- 触发器另一个常见用途,为了 审计的目的 而 记录对数据库的修改
-
drop trigger if exists payments_after_insert; delimiter $$ -- 在 payments表 insert 之后 触发 create trigger payments_after_insert after insert on payments for each row -- 作用于受影响的每一行 begin update invoices set payment_total=payment_total + new.amount where invoice_id = new.invoice_id; insert into payments_audit -- 审计记录 values(new.client_id,new.date,new.amount,'insert',now()); end $$ delimiter ; drop trigger if exists payments_after_delete; delimiter $$ create trigger payments_after_delete after delete on payments for each row begin update invoices set payment_total=payment_total-old.amount where invoice_id=old.invoice_id; insert into payments_audit -- 审计记录 values(old.client_id,old.date,old.amount,'delete',now()); end$$ delimiter ; insert into payments values(default,5,3,'2019-01-01',10,1); select * from invoices; delete from payments where invoice_id=3; SELECT * FROM payments_audit; -- 查询审计记录表
06-11
2235
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
06-19
215
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
05-27
6万+
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
05-30
9286
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)
03-04
3359
![](https://csdnimg.cn/release/blogv2/dist/pc/img/readCountWhite.png)