一、触发器的基本概念

特性说明
自动执行由数据变更事件触发,无需手动调用
与表紧密关联每个触发器只关联一个特定表
事件驱动响应 INSERT、UPDATE、DELETE 操作
时机选择可以在操作之前(BEFORE)或之后(AFTER)执行


二、创建触发器的语法

CREATE TRIGGER trigger_name
trigger_time trigger_event
ON table_name FOR EACH ROW
trigger_body

参数说明:

参数说明
trigger_name触发器名称
trigger_time触发时机 - BEFORE 或 AFTER
trigger_event触发事件 - INSERTUPDATEDELETE
table_name关联的表名
trigger_body触发器执行的SQL语句


三、三种类型的触发器详解

1. INSERT 触发器

特殊变量:

 `NEW`: 虚拟表,包含要插入的新数据

示例:自动生成学生学号

DELIMITER //

CREATE TRIGGER before_student_insert

BEFORE INSERT ON students

FOR EACH ROW

BEGIN

     自动生成学号: 年份 + 自增数字

    SET NEW.student_no = CONCAT(

        YEAR(CURDATE()),

        LPAD((SELECT COUNT(*) FROM students) + 1, 4, '0')

    );

     验证年龄范围

    IF NEW.age < 15 OR NEW.age > 30 THEN

        SIGNAL SQLSTATE '45000'

        SET MESSAGE_TEXT = '年龄必须在15-30岁之间';

    END IF;

END //

DELIMITER ;

 

2. UPDATE 触发器

特殊变量:

 `OLD`: 更新前的数据(只读)

 `NEW`: 更新后的数据(可修改)

示例:记录数据变更历史

DELIMITER //

CREATE TRIGGER after_student_update

AFTER UPDATE ON students

FOR EACH ROW

BEGIN

     只有实际发生变更时才记录

    IF OLD.name != NEW.name OR OLD.score != NEW.score THEN

        INSERT INTO student_change_log (

            student_id,

            change_type,

            old_name,

            new_name,

            old_score,

            new_score,

            change_time

        ) VALUES (

            OLD.id,

            'UPDATE',

            OLD.name,

            NEW.name,

            OLD.score,

            NEW.score,

            NOW()

        );

    END IF;

END //

DELIMITER ;

 

3. DELETE 触发器

特殊变量:

`OLD`: 被删除的数据(只读)

示例:数据删除备份

DELIMITER //

CREATE TRIGGER before_student_delete

BEFORE DELETE ON students

FOR EACH ROW

BEGIN

     将删除的数据备份到存档表

    INSERT INTO students_archive (

        id, name, age, score, delete_time, deleted_by

    ) VALUES (

        OLD.id,

        OLD.name,

        OLD.age,

        OLD.score,

        NOW(),

        CURRENT_USER()

    );

   

     同时删除关联的成绩记录(级联删除)

    DELETE FROM scores WHERE student_id = OLD.id;

END //

DELIMITER ;

 

四、触发器的实际应用场景

场景1:数据完整性约束

 防止分数被设置为非法值

DELIMITER //

CREATE TRIGGER before_score_update

BEFORE UPDATE ON scores

FOR EACH ROW

BEGIN

    IF NEW.score < 0 OR NEW.score > 100 THEN

        SIGNAL SQLSTATE '45000'

        SET MESSAGE_TEXT = '分数必须在0-100之间';

    END IF;

END //

DELIMITER ;

 

场景2:统计字段自动更新**

 自动维护学生总数统计

DELIMITER //

CREATE TRIGGER after_student_insert

AFTER INSERT ON students

FOR EACH ROW

BEGIN

    UPDATE class_statistics

    SET student_count = student_count + 1

    WHERE class_id = NEW.class_id;

END //

 

CREATE TRIGGER after_student_delete

AFTER DELETE ON students

FOR EACH ROW

BEGIN

    UPDATE class_statistics

    SET student_count = student_count - 1

    WHERE class_id = OLD.class_id;

END //

DELIMITER ;

 

场景3:复杂业务规则验证

 检查选课冲突

DELIMITER //

CREATE TRIGGER before_course_selection

BEFORE INSERT ON student_courses

FOR EACH ROW

BEGIN

    DECLARE conflict_count INT;

   

     检查时间冲突

    SELECT COUNT(*) INTO conflict_count

    FROM student_courses sc

    JOIN courses c1 ON sc.course_id = c1.id

    JOIN courses c2 ON NEW.course_id = c2.id

    WHERE sc.student_id = NEW.student_id

    AND sc.course_id != NEW.course_id

    AND c1.schedule_time = c2.schedule_time;

   

    IF conflict_count > 0 THEN

        SIGNAL SQLSTATE '45000'

        SET MESSAGE_TEXT = '课程时间冲突';

    END IF;

END //

DELIMITER ;

 

五、触发器的管理和查看

查看触发器:

 查看所有触发器

SHOW TRIGGERS;

 查看特定触发器的定义

SHOW CREATE TRIGGER trigger_name;

 从信息模式查看

SELECT * FROM information_schema.TRIGGERS

WHERE TRIGGER_NAME = 'trigger_name';

删除触发器:

DROP TRIGGER [IF EXISTS] trigger_name;

修改触发器:

MySQL 不支持直接修改触发器,需要先删除再重新创建。

 

六、触发器的限制和注意事项

1. 性能影响:触发器会增加数据库开销,应谨慎使用

2. 调试困难:触发器中的错误可能难以定位和调试

3. 递归触发:避免创建可能导致无限循环的触发器

4. 事务考虑:

    对于事务表,触发器失败会导致整个事务回滚

    对于非事务表,已执行的操作无法回滚

5. 权限要求:创建触发器需要 `TRIGGER` 权限

6. 触发时机限制:

    不能在触发器中对触发表执行 AFTER 类型的相同操作

    BEFORE 触发器可以修改 NEW 值,AFTER 触发器不能

 

七、最佳实践建议

1. 保持简单:触发器逻辑应尽量简单明了

2. 错误处理:使用 `SIGNAL` 语句提供清晰的错误信息

3. 性能监控:定期检查触发器的性能影响

4. 文档记录:详细记录每个触发器的用途和逻辑

5. 测试充分:在生产环境使用前进行充分测试

6. 替代方案考虑:有时候应用层的业务逻辑处理可能更合适

点赞(0)

C语言网提供由在职研发工程师或ACM蓝桥杯竞赛优秀选手录制的视频教程,并配有习题和答疑,点击了解:

一点编程也不会写的:零基础C语言学练课程

解决困扰你多年的C语言疑难杂症特性的C语言进阶课程

从零到写出一个爬虫的Python编程课程

只会语法写不出代码?手把手带你写100个编程真题的编程百练课程

信息学奥赛或C++选手的 必学C++课程

蓝桥杯ACM、信息学奥赛的必学课程:算法竞赛课入门课程

手把手讲解近五年真题的蓝桥杯辅导课程

Dotcpp在线编译      (登录可减少运行等待时间)