1,介绍
触发器是与表有关的数据库对象,指在insert/update/delete之前或之后,触发并执行触发器中定义的SQL语句集合。
2,特性
可以协助应用在数据库端保持数据的完整性、日志记录、数据校验等操作。
3,使用事项
可使用别名old 和 new 来引用触发器中发生变化的记录内容,这与其它的数据库是相似的。在MySQL中只支持行级触发器,不支持语句级触发器。
4,语法
1)创建触发器
create trigger trigger_name
befor/after insert/update/delete --after/before :指代当前触发器触发的时机是之前(before)还是之后(after)
--insert/update/delete : 指代的时插入/修改/删除表table_name时,才会触发该触发器中trigger_stmt中的逻辑
on table_name for each row --行级触发器
begin
trigger_stmt; --触发器里的具体逻辑实现
end;
2)查看触发器
show triggers ;
3)删除触发器
drop trigger [schema_name] trigger_name;
如果没有指定 schema_name,则默认为当前数据库
4,练习
通过触发器记录employee表中的数据变更日志,将变更日志插入到emp_log表中,包含增加、修改、删除
准备工作:日志表emp_log
# 准备工作:日志表emp_log
create table if not exists `emp_log`(
`id` int(11) not null auto_increment,
`operation` varchar(20) not null comment '操作类型,insert/update/delete',
`operation_date` datetime comment '操作时间',
`operation_id` int(11) not null comment '操作的id',
`operation_params` varchar(500) not null comment '操作参数',
primary key(`id`)
)engine=INNODB DEFAULT CHARSET=utf8mb4;
案例1:(insert类型)
创建插入数据时的触发器
# 插入数据时的触发器
create trigger emp_insert_trigger
after insert on employee for each row # before/after :指代当前触发器触发的时机是之前(before)还是之后(after)
# insert : 指代是插入数据时的触发器
# on 后面加表名 :指代的是操作的哪张表后触发该触发器
# for each row : 指代行级触发器
begin
# sql逻辑 (本例子是往日志表中插入数据)
# new : 用old还是new?本次操作是插入数据时的触发器,对于插入数据时来说,要拿到新插入的数据,得用new来获取
# concat() : 字符串拼接
insert into emp_log (id,operation,operation_date,operation_id,operation_params) values
(null,'insert',NOW(),NEW.id,concat('插入数据的内容为:【id = ',NEW.id,'】【name = ',NEW.name,'】【username = ',NEW.username,'】【password = ',NEW.password,'】……'));
end;
完成以上操作之后,查看emp_log表有无记录,OK
案例2:(update类型)
创建修改数据时的触发器
# 修改数据时的触发器
create trigger emp_update_trigger
after update on employee for each row # before/after :指代当前触发器触发的时机是之前(before)还是之后(after )
# update : 指代是修改数据时的触发器
# on 后面加表名 :指代的是操作的哪张表后触发该触发器
# for each row : 指代行级触发器
begin
# sql逻辑 (本例子是往日志表中插入数据)
# old & new : 在修改的操作中,old可以获取修改前的数据,new则获取将要修改或修改之后的数据
# concat() : 字符串拼接
insert into emp_log (id,operation,operation_date,operation_id,operation_params) values
(null,'update',NOW(),NEW.id,concat('修改前数据的内容为:【id = ',OLD.id,'】【name = ',OLD.name,'】【username = ',OLD.username,'】【password = ',OLD.password,'】……',
'修改后数据的内容为:【id = ',NEW.id,'】【name = ',NEW.name,'】【username = ',NEW.username,'】【password = ',NEW.password,'】……' ));
end;
# 修改表employee中的数据,并查询表emp_log测试触发器emp_update_trigger有没有被触发,
update employee set name = '你好你好' where id = 413;
完成以上操作之后,查看emp_log表有无记录,OK
继续,执行如下sql,查看触发器执行多少?
update employee set name = '你好你好' where id >= 5 and id <= 10;
答案是6次,因为加了for each row
行级触发器,update影响了多少行,就会触发多少次
案例3:(delete类型)
创建删除数据时的触发器
# 删除数据时的触发器
create trigger emp_delete_trigger
after delete on employee for each row # before/after :指代当前触发器触发的时机是之前(before)还是之后(after )
# delete : 指代是删除数据时的触发器
# on 后面加表名 :指代的是操作的哪张表后触发该触发器
# for each row : 指代行级触发器
begin
# sql逻辑 (本例子是往日志表中插入数据)
# old : 在删除的操作中,则需要用old获取删除前的数据
# concat() : 字符串拼接
insert into emp_log (id,operation,operation_date,operation_id,operation_params) values
(null,'delete',NOW(),OLD.id,concat('被删除的数据内容为:【id = ',OLD.id,'】【name = ',OLD.name,'】【username = ',OLD.username,'】【password = ',OLD.password,'】……'));
end;
# 删除表employee中id为13的数据,并查询表emp_log测试触发器emp_delete_trigger有没有被触发,
delete from employee where id = 13;
完成以上操作之后,查看emp_log表有无记录,OK
【视图、存储过程、存储函数、触发器总结】