mysql 触发器简单应用

after/before表记录操作之后还是之前记录到日志表

CREATE TABLE `tbl_student0101` (
  `name` char(20) DEFAULT NULL,
  `age` int(11) DEFAULT NULL,
  `tel` char(11) NOT NULL,
  PRIMARY KEY (`tel`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

#触发器日志表
drop table if exists student001_logs;
create table student001_logs
(
	id int NOT NULL auto_increment PRIMARY key,
	operate_type char(20) not null default '',
	operate_para varchar(500) not null default '',
	operate_tm timestamp null default '1970-01-02 00:00:00'
);

#表记录新增后触发器，new代表新增记录对象
drop trigger if exists stu_insert_trgg;
create trigger stu_insert_trgg
after insert
on tbl_student0101
for each row
begin
	insert into student001_logs(operate_type, operate_para, operate_tm) values ('insert', concat('insert info-','name:', new.name, ', age:', new.age, ', tel:', new.tel), now());
end;

#表记录修改后触发器，old代表修改前记录对象，new代表修改后记录对象
drop trigger if exists stu_update_trgg;
create trigger stu_update_trgg
after update
on tbl_student0101
for each row
begin
	insert into student001_logs(operate_type, operate_para, operate_tm) values ('update', concat('update before info-','name:', old.name, ', age:', old.age, ', tel:', old.tel, '|update after info-', 'name:', new.name, ', age:', new.age, ', tel:', new.tel), now());

end;

#表记录删除触发器，old代表删除记录对象
drop trigger if exists stu_delete_trgg;
create trigger stu_delete_trgg
after delete
on tbl_student0101
for each row
begin
	insert into student001_logs(operate_type, operate_para, operate_tm) values ('delete', concat('delete info-','name:', old.name, ', age:', old.age, ', tel:', old.tel), now());
end;