MySQL触发器创建、查看与删除实战指南

核心原理与应用场景

触发器（Trigger）是与表关联的数据库对象，当表中发生特定事件（INSERT、UPDATE、DELETE）时自动激活。它常用于将业务逻辑下沉至数据库层，实现原子性操作约束、审计日志记录或数据合法性校验，避免应用程序中分散的事务包裹逻辑。

创建触发器

创建触发器需指定名称、触发动作时间（BEFORE/AFTER）、触发事件及监听表。若执行多条SQL语句，需使用DELIMITER修改分隔符，并用BEGIN ... END包裹代码块。

第1步：配置分隔符与创建基础触发器

目的：定义一个简单的日志记录机制，在插入数据前记录操作节点。
入口：MySQL客户端或脚本工具。
动作：修改分隔符为//，定义触发器结构。

DELIMITER //
CREATE TRIGGER before_insert_test_tri
BEFORE INSERT ON test_trigger
FOR EACH ROW 
BEGIN
    INSERT INTO test_trigger_log(t_node)
    VALUES('before insert');
END //
DELIMITER ;

重要参数：
- BEFORE INSERT ON test_trigger：指定在test_trigger表插入前触发。
- FOR EACH ROW：表示每插入一行数据都执行一次该逻辑。

验证步骤：插入测试数据并查询日志表确认记录生成。

INSERT INTO test_trigger(t_node) VALUES('test..');
SELECT * FROM test_trigger;
SELECT * FROM test_trigger_log;

第2步：创建带业务逻辑的复杂触发器

目的：实现数据约束，防止新员工薪资超过其直属领导。
入口：针对emp表创建salary_check_trigger。

DELIMITER //
CREATE TRIGGER salary_check_trigger
BEFORE INSERT ON emp
FOR EACH ROW
BEGIN
    DECLARE mgr_sal DOUBLE;
    SELECT salary INTO mgr_sal FROM emp
    WHERE employee_id = NEW.manager_id;
    IF NEW.salary > mgr_sal
        THEN SIGNAL SQLSTATE 'HY000' SET MESSAGE_TEXT = '薪资不得高于领导薪资';
    END IF;
END //
DELIMITER ;

关键逻辑说明：
- NEW关键字引用即将插入的新行数据。
- SIGNAL SQLSTATE 'HY000'：主动抛出错误，中断插入操作，返回自定义提示信息。

完成判断：尝试插入不符合规则的薪资数据，观察是否报错拦截。

DESC emp;
INSERT INTO emp(employee_id,last_name,email,hire_date,job_id,salary,manager_id)
VALUES(300,'test','tom@126.com',CURDATE(),'AD_VP',10000,103);

查看触发器状态

当需要排查触发器逻辑或确认配置是否存在时，可使用以下三种方式查看：

查看当前库所有触发器

使用SHOW TRIGGERS命令获取详细运行信息，包括触发时机、定义者等。

SHOW TRIGGERS\G;

查看单个触发器定义

若仅需了解特定触发器的SQL源码，可使用SHOW CREATE TRIGGER。

SHOW CREATE TRIGGER salary_check_trigger\G;

从系统表查询元数据

通过查询information_schema.TRIGGERS表进行编程式检索或汇总分析。

SELECT * FROM information_schema.TRIGGERS\G;

删除触发器

当触发器不再需要或需要重构逻辑时，应将其移除以释放资源并确保数据一致性。删除操作不可逆，需谨慎执行。

DROP TRIGGER salary_check_trigger;

触发器优缺点分析

理解触发器的利弊有助于在架构设计中权衡使用：

• 优点：
  • 确保数据完整性：通过强制约束（如薪资限制）防止非法数据入库。
  • 自动化日志：无需应用层介入即可记录敏感操作，便于审计。
  • 前置校验：在写入前拦截不合法请求，减少无效I/O。
• 缺点：
  • 可读性差且隐蔽：业务逻辑隐藏于数据库中，应用代码无法直接体现依赖关系，增加维护难度。
  • 调试困难：触发器错误可能阻塞主事务，且难以像普通存储过程那样单独断点调试。
  • 性能损耗：频繁的数据变更会激活触发器，若逻辑复杂可能导致数据库CPU负载上升。

以上就是MySQL触发器创建、查看与删除的详细内容，更多关于MySQL触发器最佳实践的资料请关注本站其它相关文章！