一、介绍
触发器是与表有关的数据库对象,指在insert/update/delete 之前或之后,触发并执行触发器中定义的SQL语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性,日志记录,数据校验等操作。
使用别名OLD和NEW来引用触发器中发生变化的记录内容,这与其他的数据库是相似的。现在触发器还只支持行级触发,不支持语句级触发。
触发器类型 | NEW 和OLD |
INSERT 型触发器 | NEW表示将要或者已经新增的数据 |
UPDATE 型触发器 | OLD表示修改之前的数据,NEW表示将要或已经修改后的数据 |
DELETE型触发器 | OLD表示将要或者已经删除的数据 |
行级触发器: 行级触发器对DML语句影响的每个行执行一次,如执行update语句,update影响多少行,触发多少次。
语句级触发器:语句级触发器对每个DML语句执行一次,如执行update语句,不管update影响多少行,只触发一次。
二、语法
- 创建
CREATE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE
ON 表名 FOR EACH ROW # 行级触发器
BEGIN
trigger_stmt ; # 触发器里的逻辑实现
END;
- 查看
SHOW TRIGGERS;
- 删除
DROP TRIGGER [数据库名]trigger_name; # 删除指定数据库下的触发器,如果没有指定数据库名,默认为当前数据库。
1、insert(插入数据)触发器
例:定义触发器,完成如下需求:
通过触发器记录user表的数据变更日志,将变更日志插入到日志表user_logs中,包含增加, 修改,删除;
# 准备:日志表user_logs
create table 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 cormment '操作的ID',
operate_params varchar(500) comment '操作参数',
primary key( 'id')
)engine=innodb default charset=utf8;
# 插入数据触发器
create trigger user_insert_trigger
after insert on tb_user for each row
begin
insert into user_logs(id,operation,operate_time,operate_id,operate_params) VALUES
(null,'insert' , now(),new.id ,concat( '插入的数据内容为: id=' , new.id, ', name=' , new.name,', phone= ',NEW.phone,', email= ',NEW.email, ', profession=',NEW.profession));
end;
# 查看触发器
show triggers;
# 执行插入语句可以触发插入数据触发器
# 删除触发器
drop trigger user_insert_trigger;
2、update(修改数据)触发器
例:
# 修改数据触发器
create trigger user_update_trigger
after update on tb_user for each row
begin
insert into user_logs(id,operation,operate_time,operate_id,operate_params) VALUES
(null,'update' , now(),new.id ,concat( '更新之前的数据: id=' , old.id, ', name=' , old.name,', phone= ',old.phone,', email= ',old.email, ', profession=',old.profession,
' | 更新之后的数据: id=' , new.id, ', name=' , new.name,', phone= ',NEW.phone,', email= ',NEW.email, ', profession=',NEW.profession
));
end;
# 查看触发器
show triggers;
# 执行修改语句可以触发修改数据触发器
3、delete(删除数据)触发器
例:
# 删除数据触发器
create trigger user_delete_trigger
after delete on tb_user for each row
begin
insert into user_logs(id,operation,operate_time,operate_id,operate_params) VALUES
(null,'delete' , now(),old.id ,concat( '删除之前的数据: id=' , old.id, ', name=' , old.name,', phone= ',old.phone,', email= ',old.email, ', profession=',old.profession));
end;
# 查看触发器
show triggers;
# 执行删除语句可以触发删除数据触发器