十一、MySQL触发器

触发器

触发器是与表有关的数据库对象，指在insert/update/delete之前或之后，触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性，日志记录，数据校验等操作

使用表名OLD和NEW来引用触发器中发生变化的记录内容，这与其他的数据库是相似的。现在触发器还只支持行级触发（影响多少行就触发多少次），不支持语句级触发（每个语句不管影响多少行数据，只触发一次）

触发器类型	NEW和OLD
INSERT型触发器	NEW 表示将要或者已经新增的数据
UPDATE型触发器	OLD 表示修改之前的数据，NEW表示将要或已经修改好的数据
DELETE型触发器	OLD表示将要或已经删除的数据

语法

创建

CREATE TRIGGER triggerName 
BEFORE/ AFTER INSERT/ UPDATE/ DELETE
ON tableName FOR EACH ROW  -- 行级触发器
BEGIN
	triggerStmt;
END;

查看

SHOW TRIGGERS;

删除

DROP TRIGGER [schemaName.]triggerName; -- 如果没有指定schemaName（数据库名）,默认为当前数据库

案例

通过触发器记录tb_user表的数据变更日志，将变更日志插入到日志表xx_logs中，包含增加、修改、删除

CREATE TABLE xx_logs(
	id INT(11) NOT NULL AUTO_INCREMENT,
    operation VARCHAR(20) NOT NULL COMMENT '操作类型, insert/update/delete',
    operate_time DATETIME NOT NULL COMMENT '操作时间',
    operate_id INT(11) NOT NULL COMMENT '操作的ID',
    operate_params VARCHAR(500) COMMENT '操作参数',
    PRIMARY KEY('id')
)ENGINE=innodb DEFAULT CHARSET=utf8;

创建触发器

# 插入数据触发器
# new是新增新来的行字段数据
CREATE TRIGGER tb_user_insert_trigger
AFTER INSERT ON tb_user FOR EACH ROW
BEGIN
	INSERT INTO xx_logs (id, operation, operate_time, operate_id, operate_params)
	VALUES
	# new 是tb_user表中的每一行数据
	(null, 'insert', now(), new.id, concat('插入的数据内容为：id=', new.id, ', name=', new.name, ', phone=', new.phone));
END;
# 修改数据触发器
# 有new 和 old 对象，new是修改后的数据，old是修改前的数据
CREATE TRIGGER tb_user_update_trigger
AFTER UPDATE ON tb_user FOR EACH ROW
BEGIN
	INSERT INTO xx_logs (id, operation, operate_time, operate_id, operate_params)
	VALUES
	# new 是tb_user表中的每一行数据
	(null, 'update', now(), new.id, concat('更新之前的数据内容为：id=', old.id, ', name=', old.name, ', phone=', old.phone,
                                          '更新之后的数据内容为：id=', new.id, ', name=', new.name, ', phone=', new.phone));
END;
# 删除数据触发器
# 有old对象，old是数据删除前的信息
CREATE TRIGGER tb_user_delete_trigger
AFTER DELETE ON tb_user FOR EACH ROW
BEGIN
	INSERT INTO xx_logs (id, operation, operate_time, operate_id, operate_params)
	VALUES
	# new 是tb_user表中的每一行数据
	(null, 'delete', now(), new.id, concat('删除前的数据内容为：id=', old.id, ', name=', old.name, ', phone=', old.phone));
END;