26、触发器

1、SQL 触发器（Trigger）

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

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

1）创建触发器：

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

触发时机

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

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

触发事件

• INSERT：插入数据时触发

• UPDATE：更新数据时触发

• DELETE：删除数据时触发

触发器类型

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

如：

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

-- 触发器逻辑，定义触发器要执行的具体 SQL 语句，可以包含多条 SQL 语句
BEGININSERT 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;

向 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 表