触发器
触发器是与表有关的数据库对象,指在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;