1.需求
针对数据库中demo
数据表进行监控,发生了新增、更新、删除时将这些变更记录到demo_trigger_logs
表中用作他用,使用MySql8.x
版本进行触发器的创建.
2.查看触发器
-- 查看触发器
show triggers;
3.删除触发器
-- 删除已存在触发器 insert update delete
drop trigger if exists demo_trigger_insert;
4.创建脚本
/*
说明:复制修改本脚本时全局替换原始表名_即可
*/
-- 删除已存在触发器 insert update delete
drop trigger if exists demo_trigger_insert;
drop trigger if exists demo_trigger_update;
drop trigger if exists demo_trigger_delete;
-- 删除已存在日志表
drop table if exists demo_trigger_logs;
-- 新建日志表
create table demo_trigger_logs (id varchar(128) not null,opr_type varchar(32),update_date datetime);
-- 新建触发器 insert update delete
delimiter //
create trigger demo_trigger_insert after insert on demo for each row
begin
insert into demo_trigger_logs (id,opr_type,update_date) values (new.id, 'insert', now());
end; //
create trigger demo_trigger_update after update on demo for each row
begin
insert into demo_trigger_logs (id,opr_type,update_date) values (new.id, 'update', now());
end; //
create trigger demo_trigger_delete after delete on demo for each row
begin
insert into demo_trigger_logs (id,opr_type,update_date) values (old.id, 'delete', now());
end; //
delimiter ;
-- 查看触发器
show triggers;