create database if not exists test_trigger;
 
use test_trigger;
-- 用戶(hù)表
create table `user` (
	uid int primary key,
	username varchar(50) not null,
	password varchar(50) not null
);
-- 用戶(hù)信息操作日志表
create table user_logs(
	id int primary key auto_increment,
	time timestamp,
	log_text varchar(255)
);
 
-- 需求1：當(dāng)user表添加一行數(shù)據(jù)，則會(huì)自動(dòng)在user_log添加日志記錄
-- 定義觸發(fā)器：trigger_test1
create trigger trigger_test1 after insert on user 
for each row 
insert into user_logs values(null,now(),'有新用戶(hù)添加');
 
-- 在user表添加數(shù)據(jù)，讓觸發(fā)器自動(dòng)執(zhí)行
insert into `user` values(1,'張三','123456');

create database if not exists test_trigger;
 
use test_trigger;
-- 用戶(hù)表
create table `user` (
	uid int primary key,
	username varchar(50) not null,
	password varchar(50) not null
);
-- 用戶(hù)信息操作日志表
create table user_logs(
	id int primary key auto_increment,
	time timestamp,
	log_text varchar(255)
);
 
-- 需求1：當(dāng)user表添加一行數(shù)據(jù)，則會(huì)自動(dòng)在user_log添加日志記錄
-- 定義觸發(fā)器：trigger_test1
create trigger trigger_test1 after insert on user 
for each row 
insert into user_logs values(null,now(),'有新用戶(hù)添加');
 
-- 在user表添加數(shù)據(jù)，讓觸發(fā)器自動(dòng)執(zhí)行
insert into `user` values(1,'張三','123456');
 
-- 需求2：當(dāng)user表數(shù)據(jù)被修改時(shí)，則會(huì)自動(dòng)在user_log添加日志記錄
delimiter $$
create trigger trigger_test2 after update on user 
for each row 
begin 
	insert into user_logs values(null,now(),'有用戶(hù)信息被修改');
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('有新用戶(hù)添加，信息為：',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('--','用戶(hù)信息有修改，修改后為：',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('--','用戶(hù)信息有修改，修改后為：',new.uid,new.username,new.password,
															'用戶(hù)信息有修改，修改前為：',old.uid,old.username,old.password));
end;
 
update `user`  set password='988765654' where uid=2;