MySQL触发器

粥周粥

于 2025-04-24 14:06:28 发布

阅读量504

点赞数 19

文章标签： mysql 数据库

本文链接：https://blog.csdn.net/2401_86310851/article/details/147477193

版权

MySQL数据库中触发器是一个特殊的存储过程。

不同的是执行存储过程要使用 CALL 语句来调用，而触发器的执行不需要使用 CALL 语句来调用，也不需要手工启动，只要一个预定义的事件发生就会被 MySQL自动调用。

引发触发器执行的事件一般如下：

增加一条学生记录时，会自动检查年龄是否符合范围要求。
每当删除一条学生信息时，自动删除其成绩表上的对应记录。
每当删除一条数据时，在数据库存档表中保留一个备份副本。

触发程序的优点如下：

触发程序的执行是自动的，当对触发程序相关表的数据做出相应的修改后立即执行。
触发程序可以通过数据库中相关的表层叠修改另外的表。
触发程序可以实施比 FOREIGN KEY 约束、CHECK 约束更为复杂的检查和操作。

触发器与表关系密切，主要用于保护表中的数据。特别是当有多个表具有一定的相互联系的时候，触发器能够让不同的表保持数据的一致性。

在 MySQL 中，只有执行 INSERT、UPDATE 和 DELETE 操作时才能激活触发器。

在实际使用中，MySQL 所支持的触发器有三种：INSERT 触发器、UPDATE 触发器和 DELETE 触发器。

INSERT 触发器

在 INSERT 语句执行之前或之后响应的触发器。

使用 INSERT 触发器需要注意以下几点：

在 INSERT 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问被插入的行。
在 BEFORE INSERT 触发器中，NEW 中的值也可以被更新，即允许更改被插入的值（只要具有对应的操作权限）。
对于 AUTO_INCREMENT 列，NEW 在 INSERT 执行之前包含的值是 0，在 INSERT 执行之后将包含新的自动生成值。

UPDATE 触发器

在 UPDATE 语句执行之前或之后响应的触发器。

使用 UPDATE 触发器需要注意以下几点：

在 UPDATE 触发器代码内，可引用一个名为 NEW（不区分大小写）的虚拟表来访问更新的值。
在 UPDATE 触发器代码内，可引用一个名为 OLD（不区分大小写）的虚拟表来访问 UPDATE 语句执行前的值。
在 BEFORE UPDATE 触发器中，NEW 中的值可能也被更新，即允许更改将要用于 UPDATE 语句中的值（只要具有对应的操作权限）。
OLD 中的值全部是只读的，不能被更新。

DELETE 触发器

在 DELETE 语句执行之前或之后响应的触发器。

使用 DELETE 触发器需要注意以下几点：

在 DELETE 触发器代码内，可以引用一个名为 OLD（不区分大小写）的虚拟表来访问被删除的行。
OLD 中的值全部是只读的，不能被更新。

总体来说，触发器使用的过程中，MySQL 会按照以下方式来处理错误。

若对于事务性表，如果触发程序失败，以及由此导致的整个语句失败，那么该语句所执行的所有更改将回滚；

对于非事务性表，则不能执行此类回滚，即使语句失败，失败之前所做的任何更改依然有效。

若 BEFORE 触发程序失败，则 MySQL 将不执行相应行上的操作。

若在 BEFORE 或 AFTER 触发程序的执行过程中出现错误，则将导致调用触发程序的整个语句失败。

仅当 BEFORE 触发程序和行操作均已被成功执行，MySQL 才会执行AFTER触发程序。

基本语法

可以使用 CREATE TRIGGER 语句创建触发器。

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
-- 触发器代码
END;

触发器名

触发器的名称，触发器在当前数据库中必须具有唯一的名称。如果要在某个特定数据库中创建，名称前面应该加上数据库的名称。

BEFORE | AFTER

BEFORE 和 AFTER，触发器被触发的时刻，表示触发器是在激活它的语句之前或之后触发。

若希望验证新数据是否满足条件，则使用 BEFORE 选项；

若希望在激活触发器的语句执行之后完成几个或更多的改变，则通常使用 AFTER 选项。

INSERT | UPDATE | DELETE

触发事件，用于指定激活触发器的语句的种类。

注意：三种触发器的执行时间如下。

INSERT：将新行插入表时激活触发器。例如，INSERT 的 BEFORE 触发器不仅能被 MySQL 的 INSERT 语句激活，也能被 LOAD DATA 语句激活。
DELETE：从表中删除某一行数据时激活触发器，例如 DELETE 和 REPLACE 语句。
UPDATE：更改表中某一行数据时激活触发器，例如 UPDATE 语句。

表名

与触发器相关联的表名，此表必须是永久性表，不能将触发器与临时表或视图关联起来。

在该表上触发事件发生时才会激活触发器。同一个表不能拥有两个具有相同触发时刻和事件的触发器。

对于一张数据表，不能同时有两个 BEFORE UPDATE 触发器，但可以有一个 BEFORE UPDATE 触发器和一个 BEFORE INSERT 触发器，或一个 BEFORE UPDATE 触发器和一个 AFTER UPDATE 触发器。

触发器主体

触发器动作主体，包含触发器激活时将要执行的 MySQL 语句。如果要执行多个语句，可使用 BEGIN…END 复合语句结构。

FOR EACH ROW

一般是指行级触发，对于受触发事件影响的每一行都要激活触发器的动作。例如，使用 INSERT 语句向某个表中插入多行数据时，触发器会对每一行数据的插入都执行相应的触发器动作。

每个表都支持 INSERT、UPDATE 和 DELETE 的 BEFORE 与 AFTER，因此每个表最多支持 6 个触发器。

每个表的每个事件每次只允许有一个触发器。单一触发器不能与多个事件或多个表关联。

另外，在 MySQL 中，若需要查看数据库中已有的触发器，则可以使用 SHOW TRIGGERS 语句。

触发器类型	NEW访问权限	OLD访问权限
BEFORE INSERT	可读可写	不可用
AFTER INSERT	只读	不可用
BEFORE UPDATE	可读可写	只读
AFTER UPDATE	只读	只读
BEFORE DELETE	不可用	只读
AFTER DELETE	不可用	只读

触发器中的错误处理

在触发器中，可以使用SIGNAL语句抛出自定义错误，阻止操作执行：

SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '错误信息';

SQLSTATE	含义
'01000'	一般警告
'23000'	完整性约束违反 (如外键错误)
'42000'	语法错误或访问违规
'45000'	用户定义的异常
'08001'	连接失败
'08S01'	通信链接失败

验证学生年龄(BEFORE INSERT)

drop trigger if exists before_student_insert;

DELIMITER $$
CREATE TRIGGER before_student_insert
BEFORE INSERT ON t_student
FOR EACH ROW
BEGIN
-- 计算年龄，检查是否在合理范围内
DECLARE age INT;
SET age = YEAR(CURDATE()) - YEAR(NEW.sage);

-- 如果年龄无效，抛出错误
IF age < 18 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '学生年龄必须在18岁以上！';
END IF;
END $$
DELIMITER ;

set @max_id = (select max(sid)+1 from t_student);
insert into t_student values(@max_id,'张三','1995-05-01','男');

DELIMITER $$
CREATE TRIGGER before_insert_example
BEFORE INSERT ON your_table
FOR EACH ROW
BEGIN
-- 直接修改NEW的字段值
SET NEW.column_name = 新值;

-- 也可以基于条件修改
IF NEW.some_column = 某个值 THEN
SET NEW.another_column = 计算结果;
END IF;
END$$
DELIMITER ;

drop trigger if exists before_student_insert;

DELIMITER $$
CREATE TRIGGER before_student_insert
BEFORE INSERT
ON t_student
FOR EACH ROW
BEGIN
-- 计算年龄，检查是否在合理范围内
DECLARE age INT;
SET age = YEAR(CURDATE()) - YEAR(NEW.sage);

-- 如果年龄无效，抛出错误
IF age < 18 THEN
set new.sage = '1900-1-1';
END IF;
END $$
DELIMITER ;

set @max_id = (select max(sid) + 1
from t_student);
insert into t_student
values (@max_id, '张三', '2024-05-01', '男');

自动分配默认课程(AFTER INSERT)

DELIMITER $$
CREATE TRIGGER after_student_insert
AFTER INSERT ON t_student
FOR EACH ROW
BEGIN
-- 在插入学生记录后，自动插入默认课程成绩记录
INSERT INTO t_score (sid, cid, score) VALUES (NEW.sid, 1, 0);
END $$
DELIMITER ;

验证成绩范围(BEFORE UPDATE)

DELIMITER $$
CREATE TRIGGER before_score_insert
BEFORE INSERT ON t_score
FOR EACH ROW
BEGIN
-- 如果分数超过100，则设为100
IF NEW.score > 100 THEN
SET NEW.score = 100;
END IF;

-- 如果分数为负数，则设为0
IF NEW.score < 0 THEN
SET NEW.score = 0;
END IF;
END$$
DELIMITER ;

验证关联记录(BEFORE DELETE)

DELIMITER $$
CREATE TRIGGER before_student_delete
BEFORE DELETE ON t_student
FOR EACH ROW
BEGIN
DECLARE score_count INT;

-- 检查该学生是否有成绩记录
SELECT COUNT(*) INTO score_count
FROM t_score
WHERE sid = OLD.sid;

-- 如果有成绩记录，阻止删除操作
IF score_count > 0 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '该学生有成绩记录，不能直接删除！';
END IF;
END $$
DELIMITER ;

级联删除(AFTER DELETE)

DELIMITER $$
CREATE TRIGGER after_student_delete
AFTER DELETE ON t_student
FOR EACH ROW
BEGIN
-- 删除该学生的所有成绩记录
DELETE FROM t_score WHERE sid = OLD.sid;
END $$
DELIMITER ;

记录成绩变更(AFTER UPDATE)

-- 创建日志表
CREATE TABLE IF NOT EXISTS score_logs
(
log_id INT AUTO_INCREMENT PRIMARY KEY,
sid INT,
cid INT,
old_score DECIMAL(5, 2),
new_score DECIMAL(5, 2),
change_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER after_score_update
AFTER UPDATE
ON t_score
FOR EACH ROW
BEGIN
-- 记录成绩变更
IF OLD.score != NEW.score THEN
-- 插入日志表
INSERT INTO score_logs(sid, cid, old_score, new_score)
VALUES (NEW.sid, NEW.cid, OLD.score, NEW.score);
END IF;
END $$
DELIMITER ;

-- 插入学生的时候不允许小于18岁的学生
-- check(age>18)

DROP TRIGGER IF EXISTS CHECK_AGE;
DELIMITER $$
CREATE TRIGGER CHECK_AGE
BEFORE INSERT
ON T_STUDENT
FOR EACH ROW
BEGIN
DECLARE AGE INT;
-- OLD旧表 NEW新表
SELECT YEAR(NOW()) - YEAR(NEW.SAGE) INTO AGE;
-- 不允许小于18岁的学生
IF AGE < 18 THEN
SIGNAL SQLSTATE '45000'
SET MESSAGE_TEXT = '不允许小于18岁';
END IF;
END$$
DELIMITER ;

set @max_id = (select max(sid) + 1
from t_student);
insert into t_student(sid, sname, sage, ssex)
values (@max_id, '简要选', '1989-01-01', '男');

-- 不希望简要选被插入
DROP TRIGGER IF EXISTS CHECK_NAME;
DELIMITER $$
CREATE TRIGGER CHECK_NAME
BEFORE INSERT
ON T_STUDENT
FOR EACH ROW
BEGIN
IF NEW.SNAME = '简要选' then
signal sqlstate '45000'
set message_text = '不允许插入简要选';
end if;
END$$
DELIMITER ;

-- 钱电不能被删除
DROP TRIGGER IF EXISTS CHECK_NAME;
DELIMITER $$
create trigger CHECK_NAME
before delete
on t_student
for each row
begin
if old.sname = '钱电' then
signal sqlstate '45000'
set message_text = '不允许删除钱电';
end if;
end $$
delimiter ;

-- 删除钱电
delete from t_student where sname='钱电';

-- 删除学生的同时将学生对应的成绩删除
DROP TRIGGER IF EXISTS delete_stu;
DELIMITER $$
create trigger delete_stu
after delete
on t_student
for each row
begin
delete from t_score where sid=old.sid;
end $$
delimiter ;

-- 做伪删除
DROP TRIGGER IF EXISTS delete_stu;
DELIMITER $$
create trigger delete_stu
after update
on t_student
for each row
begin
if old.flag != new.flag then
update t_score set flag=new.flag where sid=old.sid;
end if;
end $$
delimiter ;

update t_student set flag=0 where sid=3;

-- 孙风同学的名字不能改成简要选
DROP TRIGGER IF EXISTS update_stu;
DELIMITER $$
create trigger update_stu
before update
on t_student
for each row
begin
if old.sname='孙风' && new.sname='简要选' then
signal sqlstate '45000'
set message_text = '孙风名字不能改成简要选';
end if;
end $$
delimiter ;

update t_student set sname='简要选' where sid=3;

-- 修改的时候如果改完了的名字是简要选，那么就换成哈哈
DROP TRIGGER IF EXISTS update_stu;
DELIMITER $$
create trigger update_stu
before update
on t_student
for each row
begin
if new.sname='简要选' then
set new.sname='哈哈';
end if;
end $$
delimiter ;