mysql触发器模板

-- 当我们对dept表中的数据进行insert delete update的时候，请将这些操作记录到日志表当中
-- dept的表结构
/*
CREATE TABLE `dept` (
  `DEPTNO` int NOT NULL COMMENT '部门编号',
  `DNAME` varchar(14) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '部门名',
  `LOC` varchar(13) CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci DEFAULT NULL COMMENT '部门地点',
  PRIMARY KEY (`DEPTNO`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci
*/
-- 创建日志表
drop table if exists oper_log;

create table oper_log(
  id bigint primary key auto_increment,
  table_name varchar(100) not null comment '操作的哪张表',
  oper_type varchar(100) not null comment '操作类型包括insert delete update',
  oper_time datetime not null comment '操作时间',
  oper_id bigint not null comment '操作的那行记录的id',
  oper_desc text comment '操作描述'
);


-- 如果存在就删除触发器
drop trigger if exists dept_trigger_insetr;
-- 创建inster触发器
create trigger dept_trigger_insert 
after insert on dept
for each row
begin
	insert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values
(null,'dept','insert',now(),new.deptno,concat('插入数据：deptno=', new.deptno, ',dname=', new.dname,',loc=', new.loc));
end;


-- 如果存在就删除触发器
drop trigger if exists dept_trigger_update;

-- 创建update触发器
create trigger dept_trigger_update
after update on dept for each row
begin
	insert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values
(null,'dept','update',now(),new.deptno,concat('更新前：deptno=', old.deptno, ',dname=', old.dname,',loc=', old.loc, '，更新后：deptno=',new.deptno,',dname=',new.dname,',loc=',new.loc));
end;


-- 如果存在就删除触发器
drop trigger if exists dept_trigger_delete;

-- 创建update触发器
create trigger dept_trigger_delete
after delete on dept for each row
begin
	insert into oper_log(id,table_name,oper_type,oper_time,oper_id,oper_desc) values
(null,'dept','delete',now(),old.deptno,concat('删除的数据：deptno=', old.deptno, ',dname=', old.dname,',loc=', old.loc));
end;




-- 查看触发器
show triggers;