MYSQL触发器

概述

定义

触发器顾名思义即是设置好既定条件，一旦满足既定条件则执行相应目标操作。
在MYSQL中指的是：是指事先为某张表绑定一段代码，当表中的某些内容发生改变（增、删、改）的时候，系统会自动触发代码并执行。

作用

可在写入数据前，保证护数据安全，对数据进行校验和转换
进行数据回滚，触发器发生错误时，前面用户已经执行成功的操作会被撤销
实现表间的数据联动

操作

创建触发器

创建触发器格式：

CREATE [DEFINER]
TRIGGER <trigger_name> <trigger_time> <trigger_event>
ON <table_name> FOR EACH ROW
　　[trigger_order other_trigger_name] 
<trigger_body>

说明：

definer: 用来指定trigger的安全环境，支持 user | CURRENT_USER
trigger_name: 新建触发器名
trigger_time: 触发器触发时机，分别是 AFTER和 BEFORE 分别代表触发条件发生在指定事件之前和事件发生之后
trigger_event: 触发事件，支持 UPDATE, INSERT, DELETE
table_name: 触发器监听的表名
trigger_order: 支持 FOLLOWS（尾随） | PRECEDES(在……之前)，定义多个触发器的执行顺序
other_trigger_name: 其他触发器名
trigger_body: 触发器器执行语句，如果需要批量执行的话，需要用 begin……end 进行录入。在内部可以引用涉及表的字段，OLD.col_name(只读)表示行数据被修改或删除之前的字段数据，NEW.col_name（读写）表示数据被插入或修改之后的字段数据
[]里面的参数，为可选参数

实例1：

定义一个触发器trig1，work表完成insert事件之后触发

mysql> CREATE TRIGGER trig1 AFTER INSERT
    -> ON work FOR EACH ROW
    -> INSERT INTO time VALUES(NOW());

实例2：

定义一个触发器trig2，work表发生delete事件之前触发执行批量语句

mysql> DELIMITER ||
mysql> CREATE TRIGGER trig2 BEFORE DELETE
    -> ON work FOR EACH ROW
    -> BEGIN
    -> 　　INSERT INTO time VALUES(NOW());
    -> 　　INSERT INTO time VALUES(NOW());
    -> END||
mysql> DELIMITER ;

实例3：

定义一个触发器trig3, work表发生update事件之前触发，对amount属性进行了修改

mysql> delimiter ||
mysql> CREATE TRIGGER trig3 BEFORE UPDATE 
    -> ON work
    -> 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 ;

实例4：

定义触发器trig4, 表stduents发生update事件之后触发，向students_bak插入一条修改记录

mysql> delimiter ||
mysql> create trigger trig4
    -> after update
    -> on students for each row
    -> begin
    -> insert into students_bak values(old.sid,old.sname,new.sname,old.gender,new.gender,now());
    -> end ||

查询触发器

查询触发器有两种方法：

方法1：

查看数据库中所有的触发器，只能查询基础信息

SHOW TRIGGERS;

方法2：

所有触发器信息都存储在information_schema数据库下的triggers表中，可以使用SELECT语句查询，如果触发器信息过多，最好通过TRIGGER_NAME字段指定查询

mysql> select * from information_schema.triggers 
    -> where trigger_name=<search_name>;

查看创建语句

查看触发器创建语句：

show create trigger <trigger_name>;

删除触发器

触发器不能进行修改，只能进行删除。
删除命令格式：

drop trigger <trigger_name>;

优缺点

优点

SQL触发器提供了检查数据完整性的替代方法。
SQL触发器可以捕获数据库层中业务逻辑中的错误。
SQL触发器提供了运行计划任务的另一种方法。通过使用SQL触发器，您不必等待运行计划的任务，因为在对表中的数据进行更改之前或之后自动调用触发器。
SQL触发器对于审核表中数据的更改非常有用。

缺点

可能会增加数据库中的开销
SQL触发器只能提供扩展验证，并且无法替换所有验证。一些简单的验证必须在应用层完成
由于触发器发生在数据库层中，对开发人员来说相对不够透明，会导致项目维护困难

一般能够用普通存储进行实现的情况，就使用普通存储，如果普通存储无法完成时，我们再考虑使用触发器