概念
触发器是数据库管理系统中的一个特性,它允许用户定义在特定数据库事件发生时自动执行的代码。在MySQL中,触发器可以响应INSERT
、UPDATE
、DELETE
等事件,并且可以在事件之前(BEFORE
)或之后(AFTER
)执行。
触发器的用途包括对要插入表中的值进行检查或对更新中涉及的值进行计算等。
基本使用方式
创建触发器
CREATE
[DEFINER = user]
TRIGGER [IF NOT EXISTS] trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
[trigger_order]
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }
trigger_order: { FOLLOWS | PRECEDES } other_trigger_name
- DEFINER = user:指定触发器的所有者,通常为创建触发器的用户;
- IF NOT EXISTS:如果同名触发器已存在,则不创建新触发器;
- trigger_name:触发器的名称;
- trigger_time:触发器是在事件发生之前(
BEFORE
)还是之后(AFTER
)执行; - trigger_event:触发器响应的事件,可以是
INSERT
、UPDATE
或DELETE
; - tbl_name:触发器所属的表名;
- trigger_order:触发器的执行顺序,可以是
FOLLOWS
或PRECEDES
; - trigger_body:触发器执行的
SQL
语句块;
示例代码:
DELIMITER //
CREATE TRIGGER before_insert_customer
BEFORE INSERT ON customers
FOR EACH ROW
BEGIN
IF NEW.created_at IS NULL THEN
SET NEW.created_at = NOW();
END IF;
END;//
DELIMITER ;
在这个例子中,我们创建了一个名为before_insert_customer
的触发器,它在向customers
表插入新记录之前自动设置created_at
字段为当前时间。
查询触发器
SHOW TRIGGERS LIKE 'table_name';
或者
SELECT * FROM information_schema.TRIGGERS WHERE TRIGGER_SCHEMA = 'database_name' AND EVENT_OBJECT_TABLE = 'table_name';
删除触发器
DROP TRIGGER IF EXISTS trigger_name;
多个触发器
可以为给定的表定义多个具有相同触发事件和执行时间的触发器。
例如,可以为表设置两个BEFORE UPDATE
触发器。默认情况下,具有相同触发事件和执行时间的触发器会按它们被创建的顺序激活。
为了影响触发器的顺序,在FOR EACH ROW
之后指定一个子句,该子句指示FOLLOWS
或PRECEDES
以及也具有相同触发事件和执行时间的现有触发器的名称。使用FOLLOWS
时,新触发器在现有触发器之后激活。使用PRECEDES
时,新触发器在现有触发器之前激活。
CREATE TRIGGER ins_transaction BEFORE INSERT ON account
FOR EACH ROW PRECEDES ins_sum
SET
@deposits = @deposits + IF(NEW.amount>0,NEW.amount,0),
@withdrawals = @withdrawals + IF(NEW.amount<0,-NEW.amount,0);
这个触发器ins_transaction
与ins_sum
类似,它分别累加存款和取款。它有一个PRECEDES
子句,这导致它在ins_sum
之前被激活;没有这个子句,它将在ins_sum
之后被激活,因为它是在ins_sum
之后创建的。
使用场景
- 数据验证:在数据插入或更新前进行格式、范围或依赖关系的验证。
- 自动更新:在更新一个表时,自动更新另一个相关表。
- 审计日志:记录数据变更的历史,用于审计和监控。
- 复杂计算:执行复杂的计算,如计算字段的默认值或汇总数据。
下面有一个自动求和的例子:触发器充当累加器,对表中某一列中插入的值进行求和。
表结构
CREATE TABLE account (acct_num INT, amount DECIMAL(10,2));
触发器
CREATE TRIGGER ins_sum BEFORE INSERT ON account
FOR EACH ROW SET @sum = @sum + NEW.amount;
插入数据
mysql> SET @sum = 0;
mysql> INSERT INTO account VALUES(137,14.98),(141,1937.50),(97,-100.00);
mysql> SELECT @sum AS 'Total amount inserted';
+-----------------------+
| Total amount inserted |
+-----------------------+
| 1852.48 |
+-----------------------+
注意事项
- 性能考虑:触发器可能会影响数据库性能,特别是在高并发的环境下。
- 调试难度:触发器的调试可能比普通存储过程更复杂。
- 维护成本:随着业务逻辑的变化,触发器可能需要频繁更新。
优点
- 数据完整性:帮助维护数据的完整性和一致性。
- 自动化:自动化复杂的数据库操作,减少应用程序的负担。
- 解耦:将业务逻辑与数据库操作解耦,提高代码的可维护性。
缺点
- 复杂性:过度使用触发器可能会增加数据库的复杂性。
- 测试难度:触发器的逻辑可能难以在开发环境中进行测试。
- 资源消耗:触发器可能会消耗额外的CPU和I/O资源。
结论
MySQL触发器是一个强大的工具,可以在数据库层面自动化各种操作。然而,它们应该谨慎使用,以避免不必要的性能开销和复杂性。在决定使用触发器之前,应该仔细考虑其对系统的影响,并确保它们不会与应用程序逻辑冲突。通过合理设计和使用触发器,可以提高数据库操作的效率和数据的准确性。
参考文献: