MySQL触发器使用指南：创建、NEW/OLD与性能优化

create database if not exists test_trigger;
use test_trigger;
-- 用户表
create table `user` (
	uid int primary key,
	username varchar(50) not null,
	password varchar(50) not null
);
-- 用户信息操作日志表
create table user_logs(
	id int primary key auto_increment,
	time timestamp,
	log_text varchar(255)
); 
-- 需求1：当user表添加一行数据，则会自动在user_log添加日志记录
-- 定义触发器：trigger_test1
create trigger trigger_test1 after insert on user 
for each row 
insert into user_logs values(null,now(),'有新用户添加'); 
-- 在user表添加数据，让触发器自动执行
insert into `user` values(1,'张三','123456');

-- 需求2：当user表数据被修改时，则会自动在user_log添加日志记录
delimiter $$
create trigger trigger_test2 after update on user 
for each row 
begin 
	insert into user_logs values(null,now(),'有用户信息被修改');
end $$
delimiter ; 
update `user` set password ='222222' where uid=1;

create trigger trigger_test3 after insert on user
for each row 
begin 
	insert into user_logs  values(null,now(),concat('有新用户添加，信息为：',new.uid,new.username,new.password)) ;
end; 
insert into `user` values(2,'李四','24234');

-- update 仅显示新值
create trigger trigger_test4 after update on user 
for each row 
begin 
	insert into user_logs values(null,now(),concat_ws('--','用户信息有修改，修改后为：',new.uid,new.username,new.password));
end; 
update `user`  set password='66666' where uid=2;

-- update 同时显示新旧对比
create trigger trigger_test5 after update on user 
for each row 
begin 
	insert into user_logs values(null,now(),concat_ws('--','用户信息有修改，修改后为：',new.uid,new.username,new.password,
															'用户信息有修改，修改前为：',old.uid,old.username,old.password));
end; 
update `user`  set password='988765654' where uid=2;