mysql8.x创建insert/update/delete触发器

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;