mysql_技能9_触发器

create trigger trigger_name

before/after insert/update/delete

on tbl_name

[ for each row ] – 行级触发器

begin

trigger_stmt ;
end;

– 删除触发器
– 如果没有指定 schema_name，默认为当前数据库
drop trigger [schema_name.]trigger_name

– SHOW TRIGGERS 命令查看触发器的状态、语法等信息。
show triggers ;

– 创建 insert 型触发器，完成插入数据时的日志记录 :
create trigger emp_logs_insert_trigger
after insert
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values(null,‘insert’,now(),new.id,concat(‘插入后(id:’,new.id,’, name:’,new.name,’, age:’,new.age,’, salary:’,new.salary,’)’));
end;

– 创建 update 型触发器，完成更新数据时的日志记录 :
create trigger emp_logs_update_trigger
after update
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values
(null,‘update’,now(),new.id,concat(‘修改前(id:’,old.id,’, name:’,old.name,’, age:’,old.age,’, salary:’,old.salary,’) , 修改后(id:’,new.id, ‘,name:’,new.name,’, age:’,new.age,’, salary:’,new.salary,’)’));
end;

– 创建delete 行的触发器 , 完成删除数据时的日志记录 :

create trigger emp_logs_delete_trigger
after delete
on emp
for each row
begin
insert into emp_logs (id,operation,operate_time,operate_id,operate_params) values
(null,‘delete’,now(),old.id,concat(‘删除前(id:’,old.id,’, name:’,old.name,’, age:’,old.age,’, salary:’,old.salary,’)’));
end ;

– 测试
insert into emp(name,age,salary)
values
(‘光明左使’,30,3500);

insert into emp(name,age,salary)
values
(‘光明右使’,33,3200);

update emp set age = 39 where id = 2;

delete from emp where id =2;