MySQL触发器

触发器是由MySQL的基本命令事件来触发某种特定操作，这些基本的命令由INSERT、UPDATE、DELETE等事件来触发某些特定操作。满足触发器的触发条件时，数据库系统就会自动执行触发器中的定义的程序语句，可以令某些操作之间的一致性得到协调。

1、创建触发器

在MySQL中，创建具有多个执行语句的触发器语法结构如下：

CREATE TRIGGER 触发器名称 BEFORE | AFTER 触发事件
ON 表名 FOR EACH ROW
BEGIN 
执行语句列表
END

参数说明：

（1）触发器名称：指定要创建的触发器的名字。

（2）BEFORE | AFTER：指定触发器执行的时间。BEFORE指在触发时间之前执行触发语句；AFTER表示在触发时间之后执行触发语句。

（3）触发事件：指数据库操作触发的条件，其中包括INSERT、UPDATE和DELETE。

（4）表名：指定触发事件操作表的名称。

（5）FOR EACH ROW：表示任何一条记录上的操作满足触发事件都会触发该触发器。

（6）执行语句列表：指触发器被触发后执行的程序。

触发器命名规范：

trg_表名_操作。

示例：使用触发器对表数据的新增、修改、删除操作的日志记录。

（1）创建tb_user表（用户信息表）和tb_log表（操作日志表）。

-- 创建用户信息表
CREATE TABLE IF NOT EXISTS tb_user
(
	user_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '用户编号',
	name VARCHAR(50) NOT NULL COMMENT '用户姓名'
) COMMENT = '用户信息表';

-- 创建操作日志表
CREATE TABLE IF NOT EXISTS tb_log
(
	log_id INT AUTO_INCREMENT PRIMARY KEY COMMENT '日志编号',
	user_id INT COMMENT '用户编号',
	operate_tag VARCHAR(30) NOT NULL COMMENT '操作类型',
	operate_time TIMESTAMP DEFAULT CURRENT_TIMESTAMP COMMENT '操作时间',
	operate_remark VARCHAR(100) NOT NULL COMMENT '操作备注'
) COMMENT = '操作日志表';

（2）编写新增、修改、删除操作的触发器。

-- 新增用户记录日志触发器
CREATE TRIGGER trg_tb_user_insert_log AFTER INSERT
ON tb_user FOR EACH ROW
BEGIN
	DECLARE user_id INT;
	DECLARE operate_tag VARCHAR(30) DEFAULT '新增';
	DECLARE operate_remark VARCHAR(100);
	
	-- 获取新增用户的编号
	SET user_id := NEW.user_id;

	-- 设置操作备注
	SET operate_remark := CONCAT('执行新增操作，新增的用户编号：',NEW.user_id, '，用户姓名：',NEW.name);

	-- 保存日志信息
	INSERT INTO tb_log(user_id,operate_tag,operate_remark) 
	VALUES(user_id,operate_tag,operate_remark);

END;

-- 修改用户记录日志触发器
CREATE TRIGGER trg_tb_user_update_log AFTER UPDATE
ON tb_user FOR EACH ROW
BEGIN
	DECLARE user_id INT;
	DECLARE operate_tag VARCHAR(30) DEFAULT '修改';
	DECLARE operate_remark VARCHAR(100);
	
	-- 获取被修改的用户的编号
	SET user_id := OLD.user_id;

	-- 设置操作备注
	SET operate_remark := CONCAT('执行修改操作，修改前的用户姓名：',OLD.name,'，修改后的用户姓名：',NEW.name);

	-- 保存日志信息
	INSERT INTO tb_log(user_id,operate_tag,operate_remark) 
	VALUES(user_id,operate_tag,operate_remark);

END;

-- 删除用户记录日志触发器
CREATE TRIGGER trg_tb_user_delete_log AFTER DELETE
ON tb_user FOR EACH ROW
BEGIN
	DECLARE user_id INT;
	DECLARE operate_tag VARCHAR(30) DEFAULT '删除';
	DECLARE operate_remark VARCHAR(100);
	
	-- 获取新增用户的编号
	SET user_id := OLD.user_id;

	-- 设置操作备注
	SET operate_remark := CONCAT('执行删除操作，被删除的用户编号：',OLD.user_id, '，用户姓名：',OLD.name);

	-- 保存日志信息
	INSERT INTO tb_log(user_id,operate_tag,operate_remark) 
	VALUES(user_id,operate_tag,operate_remark);

END;

（3）编写相关测试语句。

-- 新增用户信息
INSERT INTO tb_user(name) VALUES ('pan_junbiao');

-- 修改用户信息
UPDATE tb_user SET name = 'pan_junbiao的博客' WHERE user_id = 1;

-- 删除用户信息
DELETE FROM tb_user WHERE user_id = 1;

查看tb_log表的执行记录：

说明：在MySQL触发器中可以访问当前正在受到影响（添加、删除、修改等操作）的数据行，这就可以通过列标识符来实现。列标识符可以分为“原值标识符”和“新值标识符”，原值标识符用于标识当前行某个列的原始值，记作“OLD.column_name”（如，OLD.id），通常在update语句和delete语句中使用，因为在insert语句中新插入的行没有原始值；新值标识符用于当前行某个列的新值，记作“NEW.column_name”（如，NEW.id），通常在insert语句和update语句中使用，因为delete语句中被删除的行无法产生新值。

2、查看触发器

在MySQL中，所有触发器的定义都存在该数据库的triggers表中。读者可以通过查询triggers表来查看数据库中所有触发器的详细信息。

SELECT * FROM information_schema.triggers;
SELECT * FROM information_schema.triggers WHERE EVENT_OBJECT_TABLE = '表名';
SELECT * FROM information_schema.triggers WHERE TRIGGER_NAME = '触发器名称';

3、删除触发器

在MySQL中，既然可以创建触发器，同样也可以通过命令删除触发器。删除触发器指删除原来已经在某个数据库中创建的触发器，与MySQL中删除数据库的命令相似。删除触发器应用DROP关键字。其语法格式如下：

DROP TRIGGER IF EXISTS 触发器名称;

示例：删除触发器。

DROP TRIGGER IF EXISTS trg_tb_user_insert_log;
DROP TRIGGER IF EXISTS trg_tb_user_update_log;
DROP TRIGGER IF EXISTS trg_tb_user_delete_log;