SQL-触发器

MySQL –触发器详解

SQL 中的触发器（Trigger）是一种与表操作有关的数据库对象，当对某个表出现指定操作时自动触发执行，用于实现复杂的数据校验、自动更新、审计日志等功能。

MySQL 从 5.0 版本开始支持触发器，这个触发器是基于行的触发器（row-level trigger），在对特定表执行 INSERT、UPDATE 或 DELETE 操作时会被自动触发执行预定义的 SQL 语句块。

1.触发器语法

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
[trigger_order]
BEGIN
    -- SQL 逻辑
END;

参数说明：

关于trigger_order:

在 MySQL 8.0.19 之前，每张表的每种操作（INSERT、UPDATE、DELETE）每个时机（BEFORE 或 AFTER）只能有一个触发器，即：3 种操作 × 2 个时机 = 最多 6 个触发器/每表，因为 MySQL 在这些版本中不支持多个同类触发器（如两个 BEFORE INSERT）；

从 MySQL 8.0.19 起：引入了 FOLLOWS / PRECEDES，明确支持多个同类触发器并允许你控制它们的执行顺序；

这个语句的写法是 [ FOLLOWS | PRECEDES other_trigger_name ] (other_trigger_name指其他触发器名称 )

关键词	作用
FOLLOWS triggerA	当前触发器在 triggerA 之后执行
PRECEDES triggerA	当前触发器在 triggerA 之前执行

NEW 和 OLD：

• NEW: 表示即将插入或更新后的新数据（新记录）
• OLD: 表示原有的旧数据（旧记录）
• 在 INSERT 中不能使用 OLD ，因为没有旧记录可以参考，使用会报错
• 在 DELETE 中不能使用 NEW，因为没有新记录，使用也会报错
• 使用方法：NEW.columnName（columnName 表示相应的某一列名）

2.触发器注意事项

我们知道，在 MySQL 中，; 是语句结束的标识符，通常意味着一句 SQL 命令的结束，那么如果我们在BEGIN 和 END 之间的SQL语句中需要并使用了 ; ，就会导致语句在遇到第一个 ; 时停止往下读而导致语句报错。

面对这个问题，我们可以使用 DELIMITER 命令来改变语句结束符。

DELIMITER 的用法比较简单：

1. 我们可以先写 DELIMITER $$ 表示将结束符改为$$
2. 在BEGIN 和 END 之间写正常的仍用 ; 分隔的子语句
3. 在END; 后用 $$ 结束整个触发器定义
4. 最后写上 DELIMITER ; 恢复默认分号结束符

整体示例代码如下：

DELIMITER $$

CREATE TRIGGER trg_before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.username = LOWER(NEW.username);
    INSERT INTO log VALUES('insert triggered');
END;
$$

DELIMITER ;

3.查看触发器

SHOW TRIGGERS;

4.删除触发器

DROP TRIGGER IF EXISTS trigger_insert_user;

5.MySQL中触发器的限制