MySQL触发器?
1.啥是触发器呢?
触发器是与表有关的数据库对象,在满足特定的条件触发,并执行触发器中定义的语句集。
说白了,触发器就像一个牛皮糖,依附于某个表上,当表的行记录有增/删/改的操作时,可以触发触发器内提前写好的语句集的执行。
注意,查询时没有触发器的操作。
创建触发器的四大要素
- 监视谁:
table
- 监视什么事件:表中记录执行
insert/update/delete
前后 - 触发条件:
after/before
- 要触发什么事件:
insert/update/delete
另外,还需要注意触发频率:针对每一行记录的操作都会触发触发器的执行。
还有:触发器无法与临时表或视图关联。
再来看触发器的类型:
触发器类型 | 适用语句 |
---|---|
INSERT型触发器 | INSERT/LOAD DATA/REPLACE |
UPDATE型触发器 | UPDATE |
DELETE型触发器 | DELETE/REPLACE |
LOAD DATA
语句是将文件的内容插入到表中,相当于是INSERT
语句,而REPLACE
语句在一般的情况下和INSERT
差不多,但是如果表中存在PRIMARY
或者UNIQUE
索引的时候,如果插入的数据和原来的PRIMARY KEY
或者UNIQUE
相同的时候,会删除原来的数据,然后增加一条新的数据,所以有的时候执行一条REPLACE
语句相当于执行了一条DELETE
和INSERT
语句。
在触发器中,还有NEW
和OLD
语句可用:
触发器类型 | NEW和OLD的使用 | 备注 |
---|---|---|
INSERT型触发器 | NEW表示将要或者已经新增的数据 | 没有OLD |
UPDATE型触发器 | OLD表示原数据;NEW表示修改后的数据 | |
DELETE型触发器 | OLD表示将要或者已经删除的数据 | 没有NEW |
创建触发器# 基本语法: CREATE TRIGGER 触发器名 [BEFORE|AFTER] 触发事件 ON 表名 FOR EACH ROW -- FOR EACH ROW:基于每一行记录变动而触发 BEGIN -- 表示被触发的事件开始 -- 要执行的语句 END -- 表示被触发的事件结束 但是,由于MySQL默认以;作为语句的结束符,而在触发器内部的语句集中难免有语句以;结束,为了保证触发器内部语句逻辑完整性和不影响其他SQL的正常执行,通常使用DELIMITER语句来临时修改默认的语句结束符,所以,触发器的一般形式是这样的: DELIMITER // -- 将MySQL的默认分隔符修改为 // 当然,你也可以指定别的符号 CREATE TRIGGER 触发器名 [BEFORE|AFTER] 触发事件 ON 表名 FOR EACH ROW -- FOR EACH ROW:基于每一行记录变动而触发 BEGIN -- 要执行的语句 END // -- 这个 // 表示触发器部分逻辑执行完毕 DELIMITER ; -- 最后将默认分隔符再修改回来,不影响其他SQL的正常执行 少说多练,来上示例,首先我们模拟一个场景,就是,往user表中新增用户,都将会在log表中记录日志。 创建表结构 before/after insert 创建触发器,每当user表插入一条数据,就往log表写入2条记录: -- 插入前执行触发器 DELIMITER // CREATE TRIGGER user_log_t1 BEFORE INSERT ON t_user FOR EACH ROW BEGIN INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(NEW.t_name, 'before insert', NOW()); END // DELIMITER ; -- 插入后执行触发器 DELIMITER // CREATE TRIGGER user_log_t2 AFTER INSERT ON t_user FOR EACH ROW BEGIN INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(NEW.t_name, 'after insert', NOW()); END // DELIMITER ; 我们往user表插入一条数据,在查看下log表: mysql> INSERT INTO t_user(t_name) VALUE('结束'); Query OK, 1 row affected (0.00 sec) mysql> SELECT * FROM t_log; +----+--------+---------------+---------------------+ | id | t_log | t_log_type | t_log_time | +----+--------+---------------+---------------------+ | 1 | 开始 | before insert | 2020-09-15 21:32:57 | | 2 | 结束 | after insert | 2020-09-15 21:32:57 | +----+--------+---------------+---------------------+ 2 rows in set (0.00 sec) OK,没问题,触发器执行成功。 before/after update 啥也不说,创建触发器吧: -- 更新前执行触发器 DELIMITER // CREATE TRIGGER user_log_t3 BEFORE UPDATE ON t_user FOR EACH ROW BEGIN INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(CONCAT(NEW.t_name, '|', OLD.t_name), 'before update', NOW()); END // DELIMITER ; -- 更新后执行触发器 DELIMITER // CREATE TRIGGER user_log_t4 AFTER UPDATE ON t_user FOR EACH ROW BEGIN INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(CONCAT(NEW.t_name, '|', OLD.t_name), 'after update', NOW()); END // DELIMITER ; 更新记录看效果吧: mysql> UPDATE t_user SET t_name='开始了' WHERE t_name='结束'; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0 mysql> SELECT * FROM t_log; +----+------------------+---------------+---------------------+ | id | t_log | t_log_type | t_log_time | +----+------------------+---------------+---------------------+ | 1 | 开始 | before insert | 2020-09-15 21:32:57 | | 2 | 结束 | after insert | 2020-09-15 21:32:57 | | 3 | 开始|结束 | before update | 2020-09-15 21:42:39 | | 4 | 开始|结束 | after update | 2020-09-15 21:42:39 | +----+------------------+---------------+---------------------+ 4 rows in set (0.00 sec) 是不是很完美! before/after delete 创建触发器: -- 删除前执行触发器 DELIMITER // CREATE TRIGGER user_log_t5 BEFORE DELETE ON t_user FOR EACH ROW BEGIN INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(OLD.t_name, 'before delete', NOW()); END // DELIMITER ; -- 删除后执行触发器 DELIMITER // CREATE TRIGGER user_log_t6 AFTER DELETE ON t_user FOR EACH ROW BEGIN INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(OLD.t_name, 'after delete', NOW()); END // DELIMITER ; 效果: mysql> DELETE FROM t_user WHERE t_name='开始了'; Query OK, 1 row affected (0.01 sec) mysql> SELECT * FROM t_log; +----+------------------+---------------+---------------------+ | id | t_log | t_log_type | t_log_time | +----+------------------+---------------+---------------------+ | 1 | 结束 | before insert | 2020-09-15 21:32:57 | | 2 | 结束 | after insert | 2020-09-15 21:32:57 | | 3 | 开始了|结束 | before update | 2020-09-15 21:42:39 | | 4 | 开始了|结束 | after update | 2020-09-15 21:42:39 | | 5 | 开始了 | before delete | 2020-09-15 21:48:47 | | 6 | 开始了 | after delete | 2020-09-15 21:48:47 | +----+------------------+---------------+---------------------+ 6 rows in set (0.00 sec) 演示到这里,基本上触发器的常用方式也演示完了,通过几个例子,可以发现,一个表中可以建立多个触发器。 查看触发器# 查看我们上面创建的几个触发器该怎么办? SHOW TRIGGERS; +-------------+--------+--------+--------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------+----------------------+----------------------+--------------------+ | Trigger | Event | Table | Statement | Timing | Created | sql_mode | Definer | character_set_client | collation_connection | Database Collation | +-------------+--------+--------+--------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------+----------------------+----------------------+--------------------+ | user_log_t1 | INSERT | t_user | begin insert into t_log(t_log,t_log_type,t_log_time) value(new.t_name, 'before insert', now()); end | BEFORE | 2020-09-15 21:32:44.24 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@192.168.85.% | utf8 | utf8_general_ci | utf8_general_ci | | user_log_t2 | INSERT | t_user | BEGIN INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(new.t_name, 'after insert', NOW()); end | AFTER | 2020-09-15 21:32:47.70 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@192.168.85.% | utf8 | utf8_general_ci | utf8_general_ci | | user_log_t3 | UPDATE | t_user | BEGIN INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(concat(NEW.t_name, '|', OLD.t_name), 'before update', NOW()); END | BEFORE | 2020-09-15 21:41:21.76 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@192.168.85.% | utf8 | utf8_general_ci | utf8_general_ci | | user_log_t4 | UPDATE | t_user | BEGIN INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(CONCAT(NEW.t_name, '|', OLD.t_name), 'after update', NOW()); END | AFTER | 2020-09-15 21:41:17.00 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@192.168.85.% | utf8 | utf8_general_ci | utf8_general_ci | | user_log_t5 | DELETE | t_user | BEGIN INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(OLD.t_name, 'before delete', NOW()); END | BEFORE | 2020-09-15 21:47:17.24 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@192.168.85.% | utf8 | utf8_general_ci | utf8_general_ci | | user_log_t6 | DELETE | t_user | BEGIN INSERT INTO t_log(t_log,t_log_type,t_log_time) VALUE(OLD.t_name, 'after delete', NOW()); END | AFTER | 2020-09-15 21:47:20.81 | ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION | root@192.168.85.% | utf8 | utf8_general_ci | utf8_general_ci | +-------------+--------+--------+--------------------------------------------------------------------------------------------------------------------------------+--------+------------------------+-------------------------------------------------------------------------------------------------------------------------------------------+-------------------+----------------------+----------------------+--------------------+ 6 rows in set (0.00 sec) SHOW TRIGGERS返回了所有的触发器概要信息,无法查看指定的触发器信息,但可以通过视图表来查看: -- 所有的触发器都在 information_schema.triggers 表中 SELECT * FROM information_schema.triggers; -- 可以跟 where条件过滤指定的触发器 SELECT * FROM information_schema.triggers WHERE trigger_name='user_log_t1'; -- 触发器返回字段过多,可以过滤指定字段 SELECT trigger_name,event_manipulation,event_object_table,created FROM information_schema.triggers WHERE trigger_name='user_log_t1'; +--------------+--------------------+--------------------+------------------------+ | trigger_name | event_manipulation | event_object_table | created | +--------------+--------------------+--------------------+------------------------+ | user_log_t1 | INSERT | t_user | 2020-09-15 21:32:44.24 | +--------------+--------------------+--------------------+------------------------+ 1 row in set (0.00 sec) 删除触发器# 无需多说,关门上drop: -- 语法 DROP TRIGGER 触发器名; -- 示例 DROP TRIGGER user_log_t1; 另外,当触发器依附的表被删除后,该表相关的触发器也就没了: mysql> SELECT trigger_name,event_manipulation,event_object_table,created FROM information_schema.triggers WHERE event_object_table='t_user'; +--------------+--------------------+--------------------+------------------------+ | trigger_name | event_manipulation | event_object_table | created | +--------------+--------------------+--------------------+------------------------+ | user_log_t1 | INSERT | t_user | 2020-09-16 09:43:15.66 | | user_log_t2 | INSERT | t_user | 2020-09-16 09:43:18.55 | | user_log_t3 | UPDATE | t_user | 2020-09-16 09:43:24.40 | | user_log_t4 | UPDATE | t_user | 2020-09-16 09:43:33.31 | | user_log_t5 | DELETE | t_user | 2020-09-16 09:43:37.27 | | user_log_t6 | DELETE | t_user | 2020-09-16 09:43:39.95 | +--------------+--------------------+--------------------+------------------------+ 6 rows in set (0.00 sec) mysql> DROP TABLE t_user; Query OK, 0 rows affected (0.01 sec) mysql> SELECT trigger_name,event_manipulation,event_object_table,created FROM information_schema.triggers WHERE event_object_table='t_user'; Empty set (0.00 sec)
小结#
限制和注意事项
触发器会有以下两种限制:
-
触发程序不能调用将数据返回客户端的存储程序,也不能使用采用
CALL
语句的动态SQL
语句,但是允许存储程序通过参数将数据返回触发程序,也就是存储过程或者函数通过OUT
或者INOUT
类型的参数将数据返回触发器是可以的,但是不能调用直接返回数据的过程。 -
不能在触发器中使用以显示或隐式方式开始或结束事务的语句,如
START TRANS-ACTION,COMMIT
或ROLLBACK
。
注意事项:MySQL
的触发器是按照BEFORE
触发器、行操作、AFTER
触发器的顺序执行的,其中任何一步发生错误都不会继续执行剩下的操作,如果对事务表进行的操作,如果出现错误,那么将会被回滚,如果是对非事务表进行操作,那么就无法回滚了,数据可能会出错。
触发器是基于行触发的,所以删除、新增或者修改操作可能都会激活触发器,所以不要编写过于复杂的触发器,也不要增加过多的触发器,这样会对数据的插入、修改或者删除带来比较严重的影响,同时也会带来可移植性差的后果,所以在设计触发器的时候一定要有所考虑。
触发器是一种特殊的存储过程,它在插入,删除或修改特定表中的数据时触发执行,它比数据库本身标准的功能有更精细和更复杂的数据控制能力。
数据库触发器有以下的作用:
-
安全性,可以基于数据库的值使用户具有操作数据库的某种权利。
- 可以基于时间限制用户的操作,例如不允许下班后和节假日修改数据库数据。
- 可以基于数据库中的数据限制用户的操作,例如不允许股票的价格的升幅一次超过10%。
-
审计,可以跟踪用户对数据库的操作。
- 审计用户操作数据库的语句。
- 把用户对数据库的更新写入审计表。
-
实现复杂的数据完整性规则
- 实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同,触发器可以引用列或数据库对象。例如,触发器可回退任何企图吃进超过自己保证金的期货。
- 提供可变的缺省值。
-
实现复杂的非标准的数据库相关完整性规则。触发器可以对数据库中相关的表进行连环更新。
- 在修改或删除时级联修改或删除其它表中的与之匹配的行。
- 在修改或删除时把其它表中的与之匹配的行设成NULL值。
- 在修改或删除时把其它表中的与之匹配的行级联设成缺省值。
- 触发器能够拒绝或回退那些破坏相关完整性的变化,取消试图进行数据更新的事务。当插入一个与其主健不匹配的外部键时,这种触发器会起作用。
-
同步实时地复制表中的数据。
-
自动计算数据值,如果数据的值达到了一定的要求,则进行特定的处理。例如,如果公司的帐号上的资金低于5万元则立即给财务人员发送警告数据。
转自张开老师的博客