（2.13）Mysql之SQL基础——触发器
关键词：Mysql触发器
【1】触发器
MySQL语句在需要时被执行，存储过程也是如此，如果希望某条语句（或某些语句）在事件发生时自动执行，这就需要用到触发器。
触发器是MySQL响应以下任意语句而自动执行的一条MySQL语句（或位于begin和end语句之间的一组语句）：
delete；
insert；
update；
其他MySQL语句不支持触发器。。。
【1.0】创建触发器
需要以下4条信息：
①唯一的触发器名；
②触发器关联的表；
③触发器应该响应的活动（delete、insert或update）；
④触发器何时执行（处理之前或之后）；
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
PS：MySQL中，触发器名必须在每个表中唯一，但不限制每个数据库中唯一，即：同一数据库中两个表可以具有相同名字的触发器（其他的DBMS中不被允许）。
触发器使用create teigger语句创建，下面是一个简单的例子：
create trigger newproduct after insert on products
for each row select 'product added';
这里创建了一个newproduct的新触发器，给出了after insert，所以此触发器在insert语句成功执行后执行；其中还指定了for each row，因此代码对每个插入行执行。
PS：只有表才支持触发器，视图不支持（临时表也不支持）。
触发器按每个表每个事件每次的定义，每个表每个事件每次只允许一个触发器；因此每个表最多支持6个触发器（每条insert、update和delete的之前和之后），单一触发器不能与多个事件或多个表关联。
如果before触发器失败，则MySQL将不执行请求的操作；此外，如果before触发器或语句本身失败，MySQL将不执行after触发器（如果有的话）。
【1.1】触发器的触发条件
【1.2】删除触发器
删除触发器使用drop trigger语句，例如：
drop trigger newproduct;
PS:触发器不能更新或覆盖；为了修改一个触发器，必须先删后建。
【2】使用触发器
【2.1】new和old的使用
【2.2】一般形式
-- 0.查看触发器 【1】SHOW TRIGGERS; 【2】SELECT * FROM `information_schema`.`TRIGGERS` WHERE `TRIGGER_NAME`='trg_bm_history'; -- 1.一般形式 create trigger trigger_name trigger_time trigger_event on table_name for each row begin trigger_statement; end 解析： （1）trigger_time:触发时间，取值[before/after]即事件发生之前/之后 （2）trigger_event:触发时间，取值[insert/update/delete]即进行增/删/改事件触发该触发器； （注意replace以及load data等同于Insert事件） （3）for each row:关键字，对每一行进行触发；以行为单位； （4）trigger_statement：触发执行语句 （5）old / new 关键字：可以获取到操作之前、操作之后的行数据，一般old用于after时刻，new多用于before时刻 【1】insert操作：一般只使用new,new为新插入的行的数据 【2】update操作：一般使用new和old，new为更新后的数据行，old为更新前的数据行 【3】delete操作：一般只用old，表示删除前的数据行 -- 2.简单案例 #删除表之前使用触发器把被删除的表插入到历史表备份 delimiter // create trigger trg_bm_history after delete on bm for each row begin insert into bm_history values(old.b_id,old.b_name); end // delimiter ; -- 3.删除 drop trigger trigger_name; -- 4.注意事项 【1】对于相同的表，相同的事件只能创建一个触发器，比如对表 bm 创建了after delete 触发器,就不能再对该表创建 after delete 触发器了;
【2.3】insert触发器
使用insert触发器，需要知道以下三点：
①在insert触发器代码内，可引用一个名为new的虚拟表，访问被插入的行；
②在before insert触发器内，new中的值也可以被更新（允许更改被插入的值）；
③对于auto increment列，new在insert执行之前包含0，在insert执行之后包含新的自动生成值；
例子如下：
create trigger neworder before insert on orders for each row begin set new.order_num=1; -- 修改将要插入插入的新行所在 order_num列值为1,插入进去的值就是1了。 end
此代码创建一个名为neworder的触发器，按照after insert on order执行；在插入一条新的数据orders表时，MySQL生成一个新订单号并保存到order_num中；
触发器从new.order_num取这个值并返回它；此触发器必须按照after insert执行，因为在before insert语句执行之前，新order_num还没生成。
PS：通常将before用于数据验证和净化（目的是保证插入表中的数据确实是需要的数据）。这个规则也适用于update触发器。
【2.4】delete触发器
使用delete触发器，需要知道以下两点：
①在delete触发器代码内，可以引用一个名为old的虚拟表，访问被删除的行；
②old中的值全都是只读的，不能更新；
下面是一个使用old保存将要被删除的行到一个存档表中：
create trigger deleteorder before delete on orders for each row begin insert into archive_orders(order_num,order_date,cust_id) values(OLD_order_num, OLD_order_date, OLD_cust_id); end;
此代码在任意orders表中的数据被删除前执行此触发器；它使用一条insert语句将old中的值（要被删除的数据）保存到一个名为archive_orders的存档表中；
上面的例子中，使用begin end块的好处是触发器能容纳多条SQL语句。
PS：使用before delete触发器的优点：如果由于某些原因，数据不能存档，delete本身将被放弃。
【2.5】update触发器
使用update触发器，需要知道以下三点：
①在update触发器代码内，可以引用一个名为old的虚拟表访问以前（update语句前）的值，引用一个名为new的虚拟表访问新更新的值；
②在before update触发器中，new中的值可能也被更新（允许更改将要用于update语句中的值）；
③old中的值全都是只读的，不能更新；
下面的例子，保证了州名称缩写总是大写：
create trigger updateevendor before update on vendors
for each row set.vend_state = upper(new.vend_state);
这个例子中，每次更新一个行时，new.vend_state中的值（将用来更新表行的值）都用upper(new.vend_state)替换。
△关于触发器一些必须知道的知识：
①创建触发器可能需要特殊的安全访问权限，但触发器的执行是自动的；如果insert、update、delete语句可以执行，则相应触发器也能执行；
②应该用触发器来保证数据的一致性（大小写、格式等）；有点在于它总是进行这种处理，而且是透明的进行，与客户机应用无关；
③触发器的一种非常有意义的使用是创建审计跟踪。使用触发器，把更改记录到另一个表非常容易；
④MySQL触发器不支持call语句，即不能从触发器内调用存储过程，所需的存储过程代码需要复制到触发器内。
案例：插入数据前，记下当条记录的其插入id+1;
delimiter //
create trigger trg_bm_max before
insert on bm
for each row
begin
update dm_count set count_max=new.b_id+1;
end //
delimiter ;
【3】最佳实践
【3.1】当表新增行后，修改该表新增行中的某个字段值
delimiter // create trigger trg_update_prefix_app before insert on prefix_app for each row begin set new.in_icon=replace(new.in_icon,'oss-cn-zhangjiakou-internal.aliyuncs.com','firzjk.oss-cn-zhangjiakou.aliyuncs.com'); end // delimiter ;