MySQL触发器
1——格式
- drop trigger if exists 触发器名称
- show triggers
语法1: create trigger 触发器名称 before|after 触发事件 -- 1个语句
on 表名 for each row
执行语句;
语法2:create trigger 触发器名称 before|after 触发事件 -- n个语句
on 表名 for each row
begin
执行语句列表;
end;
例子1:emp表插入数据后, 触发b表数据提示信息
drop trigger if exists my_trigger1
create trigger my_trigger1 after insert on emp for each row
insert into b values(NULL, now(), '有新的数据插入');
例子2:触发器,包含多条语句;emp表更新数据,触发b表
drop trigger if exists my_trigger2
delimiter &&
create trigger my_trigger2 after update on emp for each row
begin
update b set ename = '王**花' where id = 2;
update b set age = 30 where salary > 16000;
end &&;
delimiter ;;
2——NEW and OLD
- insert型触发器——new.字段名
- update型触发器——new.字段名,old.字段名
- delete型触发器——old.字段名
例子1:日志记录insert之后的内容
drop trigger if exists my_trigger3
create trigger my_trigger3 after insert on employee for each row
insert into emp_logs values(NULL, now(), concat_ws('_', '新添加的信息为:', new.id, new.name, new.age));
insert into employee values(1003, '黄飞燕', 22); -- 插入本条记录,自动触发my_trigger3,new指向本条记录
例子2:日志记录update之后的新内容
drop trigger if exists my_trigger4
create trigger my_trigger4 after update on employee for each row
insert into emp_logs values(NULL, now(), concat_ws('_', '修改的信息为:', new.id, new.name, new.age));
update employee set name = '王丽丽' where id = 1005; -- 执行本条记录,自动触发my_trigger3,new指向本条记录
例子3:日志记录delete之前的信息
drop trigger if exists my_trigger4
create trigger my_trigger4 after delete on employee for each row
insert into emp_logs values(NULL, now(), concat_ws('_', '删除的信息为:', old.id, old.name, old.age));
delete from employee where id = 1007; -- 删除本条记录,自动触发my_trigger3,old指向本条记录