触发器
触发器是与表有关的数据库对象,指在 insert/update/delete 之前或之后,触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性 , 日志记录 , 数据校验等操作 。
使用别名 OLD 和 NEW
来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发,不支持语句级触发。
行级触发:假设执行五条update语句,触发器触发了五次。
语句级触发:假设执行五条update语句,不管影响了多少条数据,但触发器只触发一次。
触发器类型 | NEW和OLD |
---|---|
INSERT触发器 | NEW表示将要或者已经新增的数据 |
UPDATE触发器 | NEW表示修改之前的数据,NEW表示将要或者已经修改后的数据 |
DELETE触发器 | OLD表示将要或者已经删除的数据 |
语法
#创建
create trigger trigger_name
before/after insert/update/delete
on table_name for each row --行级触发器
begin
trigger_stmt;
end;
#查看
show triggers;
#删除
drop trigger [schema_name.]trigger_name;--如果没有指定的数据库,默认为当前数据库
案例
需求:通过 触发器记录beauty表的数据变更日志(user_logs),包括增加、删除、修改
#准备日志表user_logs
CREATE TABLE IF NOT EXISTS user_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(5000) COMMENT '操作参数',
PRIMARY KEY(`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
1、insert类型触发器
DELIMITER $
CREATE TRIGGER bea_insert_trigger
AFTER INSERT
ON beauty FOR EACH ROW
BEGIN
INSERT INTO user_logs VALUES(NULL,'insert',NOW(),new.id,CONCAT('插入的数据为:id = ',new.id,' name = ',new.name,
' sex = ',new.sex,' borndate = ',new.borndate,' phone = ',new.phone));
END$
测试
2、update类型触发器
CREATE TRIGGER bea_update_trigger
AFTER UPDATE
ON beauty FOR EACH ROW
BEGIN
INSERT INTO user_logs VALUES(NULL,'update',NOW(),old.id,CONCAT('更新前的数据为:id = ',old.id,' name = ',old.name,
' sex = ',old.sex,' borndate = ',old.borndate,' phone = ',old.phone,
'更新后的数据为:id = ',new.id,' name = ',new.name,
' sex = ',new.sex,' borndate = ',new.borndate,' phone = ',new.phone));
END$
测试:
3、delete类型触发器
DELIMITER $
CREATE TRIGGER bea_delete_trigger
AFTER DELETE
ON beauty FOR EACH ROW
BEGIN
INSERT INTO user_logs VALUES(NULL,'delete',NOW(),old.id,CONCAT('删除的数据为:id = ',old.id,' name = ',old.name,
' sex = ',old.sex,' borndate = ',old.borndate,' phone = ',old.phone));
END$
测试: