SQL-必知必会-触发器

触发器

触发器是特殊的存储过程，它在特定的数据库活动发生时自动执行。触发器可以与特定表上的INSERT、UPDATE和DELETE操作（或组合）相关联。
与存储过程不一样（存储过程只是简单的存储 SQL 语句），触发器与单个的表相关联。

与Orders表上的INSERT操作相关联的触发器只在Orders表中插入行时执行。类似地，Customers表上的INSERT和UPDATE操作的触发器只在表上出现这些操作时执行。

触发器内的代码具有以下数据的访问权：
INSERT操作中的所有新数据；
UPDATE操作中的所有新数据和旧数据；
DELETE操作中删除的数据。

触发器的一些常见用途。
1.保证数据一致。例如，在INSERT或UPDATE操作中将所有州名转换为大写。
2.基于某个表的变动在其他表上执行活动。例如，每当更新或删除一行时将审计跟踪记录写入某个日志表。
3.进行额外的验证并根据需要回退数据。例如，保证某个顾客的可用资金不超限定，如果已经超出，则阻塞插入。
4.计算计算列的值或更新时间戳。

MYSQL触发器
触发器：trigger，是指事先为某张表绑定一段代码，当表中的某些内容发生改变（增、删、改）的时候，系统会自动触发代码并执行。

创建触发器

CREATE TRIGGER trigger_name trigger_time trigger_event ON tb_name FOR EACH ROW trigger_stmt
trigger_name：触发器的名称
tirgger_time：触发时机，为BEFORE或者AFTER
trigger_event：触发事件，为INSERT、DELETE或者UPDATE
tb_name：表示建立触发器的表明，就是在哪张表上建立触发器
trigger_stmt：触发器的程序体，可以是一条SQL语句或者是用BEGIN和END包含的多条语句
所以可以说MySQL创建以下六种触发器：
BEFORE INSERT,BEFORE DELETE,BEFORE UPDATE
AFTER INSERT,AFTER DELETE,AFTER UPDATE

基本语法

delimiter 自定义结束符号
create trigger 触发器名字 触发时间 触发事件 on 表 for each row
begin
    -- 触发器内容主体，每行用分号结尾
end
自定义的结束符合

delimiter ;

on 表 for each：触发对象，触发器绑定的实质是表中的所有行，因此当每一行发生指定改变时，触发器就会发生

触发时间

当 SQL 指令发生时，会令行中数据发生变化，而每张表中对应的行有两种状态：数据操作前和操作后

before：表中数据发生改变前的状态
after：表中数据发生改变后的状态
PS：如果 before 触发器失败或者语句本身失败，将不执行 after 触发器(如果有的话)

触发事件

触发器是针对数据发送改变才会被触发，对应的操作只有

INSERT
DELETE
UPDATE

注意事项

在 MySQL 中，触发器名必须在每个表中唯一，但不是在每个数据库中唯一，即同一数据库中的两个表可能具有相同名字的触发器
每个表的每个事件每次只允许一个触发器，因此，每个表最多支持 6 个触发器，before/after insert、before/after delete、before/after update

两张表，商品表和订单表

如果订单表发生数据插入，对应的商品库存应该减少。因此这里对订单表创建触发器

delimiter ##
-- 创建触发器
create trigger after_insert_order after insert on orders for each row
begin
    -- 更新商品表的库存，这里只指定了更新第一件商品的库存
    update goods set goods_num = goods_num - 1 where id = 1;
end
##

delimiter ;

查看触发器

查看全部触发器
语法：show triggers;

查看触发器的创建语句
语法：show create trigger 触发器名字;

删除触发器

触发器不能修改，只能删除

语法：drop trigger + 触发器名字

触发器应用

触发器针对的是数据库中的每一行记录，每行数据在操作前后都会有一个对应的状态，触发器将没有操作之前的状态保存到 old 关键字中，将操作后的状态保存到 new 中

语法：old/new.字段名

需要注意的是，old 和 new 不是所有触发器都有

触发器类型 new和old的使用
INSERT型触发器 没有 old，只有 new，new 表示将要(插入前)或者已经增加(插入后)的数据
UPDATE型触发器 既有 old 也有 new，old 表示更新之前的数据，new 表示更新之后的数据
DELETE型触发器 没有 new，只有 old，old 表示将要(删除前)或者已经被删除(删除后)的数据