每行数据在操作前后都会有一个对应的状态
触发器将没有操作之前的状态保存到 old 关键字中
触发器将操作后的状态保存到 new 关键字中<br>
需要注意的是，old 和 new 不是所有触发器都有
子主题
drop table if exists demo_tbl;<br>create table demo_tbl(<br> id int auto_increment,<br> info varchar(100),<br> primary key(id)<br>)engine=innodb default charset=utf8;
drop table if exists demo_log;<br>create table demo_log(<br> id int auto_increment comment '主键',<br> table_name varchar(50) comment '所涉及到的表',<br> data_id int comment '所涉及到的数据ID',<br> operation_people_id int comment '操作人',<br> operation_name varchar(50) comment '什么类型的操作',<br> operation_datetime datetime comment '操作时间',<br> new_value varchar(100) comment '新的值',<br> old_value varchar(100) comment '旧的值',<br> primary key(id)<br>)engine=innodb default charset=utf8;
-- 新增数据的触发器, 当插入一条新的数据时，执行什么样的逻辑（SQL语句）<br>delimiter $$<br>drop trigger if exists after_insert_demo_tbl $$<br>create trigger after_insert_demo_tbl after insert on demo_tbl for each row<br>begin<br> insert into demo_log (table_name, data_id, operation_people_id, operation_name, operation_datetime, new_value, old_value) values ('demo_tbl', new.id, 1, '插入', now(), new.info, null);<br>end $$<br>delimiter ;
-- 测试新增触发器
insert into demo_tbl (info) values ('新增触发器测试 1');
insert into demo_tbl (info) values ('新增触发器测试 2');
insert into demo_tbl (info) values ('新增触发器测试 3');
insert into demo_tbl (info) values ('新增触发器测试 4');
-- 更新数据的触发器
delimiter $$
drop trigger if exists after_update_demo_tbl $$
create trigger after_update_demo_tbl after update on demo_tbl for each row
begin
insert into demo_log (table_name, data_id, operation_people_id, operation_name, operation_datetime, new_value, old_value) values ('demo_tbl', new.id, 1, '更新', now(), new.info, old.info);
end $$
delimiter ;
-- 测试更新的触发器
update demo_tbl set info = '更新触发器测试' where id = 3;
-- 删除数据的触发器
delimiter $$
drop trigger if exists after_delete_demo_tbl $$
create trigger after_delete_demo_tbl after delete on demo_tbl for each row
begin
insert into demo_log (table_name, data_id, operation_people_id, operation_name, operation_datetime, new_value, old_value) values ('demo_tbl', old.id, 1, '删除', now(), null, old.info);
end $$
delimiter ;
-- 测试删除数据的触发器
delete from demo_tbl where id = 4;
