26、触发器

1、SQL 触发器（Trigger）

是一种特殊的存储过程，它在数据库发生特定事件（如 INSERT、UPDATE、DELETE）时自动执行，无需显式调用。

• 触发器与表相关联，当对表执行 INSERT、UPDATE 、DELETE 或 TRUNCATE 操作之前或之后，触发器自动触发。
• 触发器常用于实现数据完整性、审计日志、业务规则自动化等场景。

1）创建触发器：

DELIMITER //        -- 临时更改 SQL 语句的结束符，告诉 MySQL："从现在开始，把 // 当作语句结束符"

CREATE TRIGGER trigger_name                 -- 创建触发器，指定触发器的名称。
{BEFORE | AFTER} {INSERT | UPDATE | DELETE} -- 设置触发器应何时响应特定事件
ON table_name                               -- 提供触发器关联的表的名称。
[FOR EACH ROW]                              -- 定义触发器的类型。
[WHEN (condition)]
BEGIN
    -- 触发器逻辑，定义触发器要执行的具体 SQL 语句，可以包含多条 SQL 语句
END//
DELIMITER ;     -- 恢复默认结束符为 ;

• DELIMITER // ：是 MySQL 特有的语法，临时更改 SQL 语句的结束符，从而让 MySQL 客户端能够正确解析包含多个分号（;）的复杂语句块（如存储过程、函数、触发器、事件等）。在 MySQL 中，分号 ; 是 SQL 语句的默认结束符。但是触发器、存储过程等包含多条 SQL 语句的代码块内部也有分号，这会导致解析器混淆。

触发时机

• BEFORE：在事件发生之前执行

• AFTER：在事件发生之后执行

触发事件

• INSERT：插入数据时触发

• UPDATE：更新数据时触发

• DELETE：删除数据时触发

触发器类型

触发器类型	语法	触发频率	典型用途
行级触发器	FOR EACH ROW	每处理一行数据，触发器就执行一次。	在触发器内部，可以使用： NEW：表示新插入或更新后的行（INSERT/UPDATE 可用） OLD：表示更新前或删除的行（UPDATE/DELETE 可用） 只有行级触发器才能使用 NEW 和 OLD！
语句级触发器	FOR EACH STATEMENT（默认）	整个 DML 语句只触发一次	记录操作日志、发送通知、统计操作次数

如：

DELIMITER //        -- 临时更改 SQL 语句的结束符，告诉 MySQL："从现在开始，把 // 当作语句结束符"

-- 新建触发器，当新增员工时，自动记录到审计日志
CREATE TRIGGER tr_after_employee_insert   -- 创建触发器
AFTER INSERT ON employees     -- AFTER：触发时机，在操作之后执行，INSERT：触发事件，当执行 INSERT 操作时，ON employees：关联的表名
FOR EACH ROW       -- 触发器类型：行级触发器

-- 触发器逻辑，定义触发器要执行的具体 SQL 语句，可以包含多条 SQL 语句
BEGIN
    INSERT INTO audit_log (table_name, action_type, employee_id, change_date, new_value) --向 audit_log 表插入记录，表要事先创建完成，否则触发器会执行失败
    VALUES ('employees', 'INSERT', NEW.employee_id, NOW(),      --  NEW. 代表新插入的数据行
            CONCAT('New employee: ', NEW.first_name, ' ', NEW.last_name));  --CONCAT()将多个字符串连接成一个完整的描述。
END//

DELIMITER ;     -- 恢复默认结束符为 ;

向 employees 表插入数据：

--新增员工
INSERT INTO employees (employee_id, first_name, last_name, department, salary)
VALUES (101, 'John', 'Doe', 'IT', 50000);

-- 生成的数据
employee_id | first_name | last_name | department | salary
------------|------------|-----------|------------|--------
101         | John       | Doe       | IT         | 50000

触发器自动执行后的结果：

--audit_log 表（自动生成）：
log_id | table_name | action_type | employee_id | change_date           | new_value
-------|------------|-------------|-------------|----------------------|--------------------------
1      | employees  | INSERT      | 101         | 2024-01-15 10:30:25  | New employee: John Doe

执行流程：

1. 用户执行：INSERT INTO employees ...

2. 数据库执行：

   • 首先执行实际的 INSERT 操作

   • 数据插入到 employees 表

3. 触发器检测：数据库检测到 AFTER INSERT 条件满足

4. 触发器执行：自动执行 BEGIN ... END 中的逻辑

5. 结果：审计记录插入到 audit_log 表

2）删除触发器

DROP TRIGGER 语句，删除触发器。

DROP TRIGGER [IF EXISTS] trigger_name;