触发器

触发器 TRIGGER

触发器是一种特殊的存储过程，与表关联
🔺当表发生特定事件(INSERT/UPDATE/DELETE)时自动执行，无需手动调用

触发器基于一个表创建，但是可以针对多个表进行操作
因此触发器可以对表实施复杂的完整性约束。

🔺当建立触发器的表 发生改变时，触发器可以自动激活，从而防止对数据进行不正确的修改。

😀常用于数据校验、日志记录、数据同步、级联操作等场景，是保证数据完整性的重要工具。

优点：

1. 自动执行，减少手动干预。在特定数据表的数据发生了任何修改（比如手动输入或者使用程序采集）后立即激活
   2.审计跟踪。触发器可以自动记录敏感数据的变更，创建完整的审计日志，包括谁、何时、改了什么。
   3.触发器可以对表进行强制限制,远比CHECK强大和复杂。
   4.级联操作。是指当一张表的数据发生变动时，触发器可以自动联动修改其他相关表的数据，形成级联效应，而不需要手动逐表操作。
   5.触发器是即时响应。允许跨相关表进行实时数据处理和更新，数据变更后立即生效，无需等待定时任务或批处理。
   6.捕获错误。触发器可以在数据写入数据库之前拦截并校验，将业务错误在数据库层就捕获，防止脏数据入库。
   例子：

-- BEFORE 触发器可以阻止非法数据写入
CREATE TRIGGER trg_before_insert_salary
BEFORE INSERT ON salaries
FOR EACH ROW
BEGIN
    IF NEW.amount < 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '薪资不能为负数';
    END IF;
END;

创建触发器

触发器用CREATE TRIGGER创建
语法规则：

CREATE TRIGGER trigger_name
     {BEFORE|AFTER}  {ALTER|UPDATE|DELETE} ON tb_name
     FOR EACH ROW
    BEGIN 
            -- 触发器执行体（SQL 语句）
    END;

其中：

trigger_name:触发器名称，数据库中唯一

{BEFORE|AFTER} 触发器触发时机，指明触发器是在{ALTER|UPDATE|DELETE} ON tb_name之前还是之后触发

{ALTER|UPDATE|DELETE} 触发事件类型

ON tb_name:触发器所关联的表

FOR EACH ROW:行级触发，每行受影响的数据都会触发一次,而不是对这个表只执行一次

BEGIN...END：多条 SQL 语句时用此包裹；单条语句可直接写

⚠️触发器中，使用OLD和NEW来引用数据变更后的行值，实际上是将变更前后的行作为一个表用OLD和NEW指代
如OLD.col_name,NEW.col_name
1.OLD.col_name关联现有行的一列在被更新或删除前的值。
2.NEW.col_name 关联一个新行的插入或更新现有行的一列值

NEW和OLD不是对于所有的触发时机都可以使用的。DELETE只有OLD可用，INSERT只有NEW可用。UPDATE两者都可用。

😀例子1
在gradem 数据库的 teacher 表中创建一个触发器，当一个教师的信息被删除时，把该教师的编号和姓名添加到delteacher 表中

USE gradem;
#创建一个空表 delteacher，表由tno 和 tname 两列组成
CREATE TABLE delteacher SELECT tno,tname FROM teacher WHERE 1=0

#创建teacher的触发器

DELIMITER $$

CREATE TRIGGER trig_teacher
              AFTER DELETE ON teacher
              ON EACH ROW
              BEGIN
                        INSERT INTO delteacher (tno,tname )
                                      VALUES (OLD.tno,OLD.tname);
              END$$
DELIMITER ;

😀例子2
插入订单前自动设置创建时间，并校验金额不能为负

DELIMITER //

CREATE TRIGGER before_order_insert
    BEFORE INSERT ON orders
    FOR EACH ROW
BEGIN
    -- 自动填充创建时间
    IF NEW.created_at IS NULL THEN
        SET NEW.created_at = NOW();
    END IF;

    -- 校验金额必须大于 0
    IF NEW.amount <= 0 THEN
        SIGNAL SQLSTATE '45000'
            SET MESSAGE_TEXT = '订单金额必须大于 0';
    END IF;
END //

🤔 SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = '订单金额必须大于 0';

查看触发器

可以使用SHOW TRIGGER或者SHOW CREATE TRIGGER,或者在information_schema.triggers中查看

1.SHOW TRIGGERS

-- 查看当前数据库的所有触发器
SHOW TRIGGERS;

-- 查看指定数据库的触发器
SHOW TRIGGERS FROM db_name;

-- 根据触发器名称精确查询
```sql
SHOW TRIGGERS WHERE `Trigger` = 'before_order_insert';

-- 按表名模糊匹配（注意：LIKE 匹配的是表名，不是触发器名）
SHOW TRIGGERS LIKE 'acc%';

-- 按条件筛选
SHOW TRIGGERS WHERE Table = 'orders';

2.SHOW CREATE TRIGGER

```SQL

SHOW CREATE TRIGGER trigger_name;

3.INFORMATION_SCHEMA.TRIGGERS — 精细查询

字段 说明
TRIGGER_CATALOG 目录名（固定为 def）
TRIGGER_SCHEMA 触发器所在数据库
TRIGGER_NAME 触发器名称
EVENT_MANIPULATION 触发事件（INSERT/UPDATE/DELETE）
EVENT_OBJECT_CATALOG 表目录（固定为 def）
EVENT_OBJECT_SCHEMA 表所在数据库
EVENT_OBJECT_TABLE 关联的表名
ACTION_ORDER 同类触发器的执行顺序号
ACTION_CONDITION 条件（固定为 NULL）
ACTION_STATEMENT 触发器执行体 SQL
ACTION_ORIENTATION 触发粒度（固定为 ROW）
ACTION_TIMING 触发时机（BEFORE/AFTER）
ACTION_REFERENCE_OLD_TABLE 固定为 NULL
ACTION_REFERENCE_NEW_TABLE 固定为 NULL
ACTION_REFERENCE_OLD_ROW 固定为 OLD
ACTION_REFERENCE_NEW_ROW 固定为 NEW
CREATED 创建时间
SQL_MODE SQL 模式
DEFINER 创建者
CHARACTER_SET_CLIENT 客户端字符集
COLLATION_CONNECTION 连接排序规则
DATABASE_COLLATION 数据库排序规则

删除触发器

使用DROP TRIGGER 语句删除当前数据库的触发器

语法：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;