MySQL数据库的触发器

create database if not exists mydb01_trigger;

use mydb01_trigger;

-- 用户表

create table if not exists user(
 uid int primary key auto_increment,
 username varchar(50) not null,
 password varchar(50) not null
)default charset=utf8;

-- 用户信息操作日志表

create table if not exists user_logs(
 id int primary key auto_increment,
 time timestamp,
 log_text varchar(255)
 )default charset=utf8;
 
-- 需求1:当user表添加一行数据，则会自动在user_log添加日志记录
-- 定义触发器： trigger_test1
create trigger trigger_test1 after insert on user for each row
insert into user_logs values(NULL,now(),'new');

-- 在user表添加数据，让触发器自动执行
insert into user values(3,'zbb','123456');
 
 
 -- NEW和OLD
 -- insert 触发器
 -- NEW
 -- 定义触发器： trigger_test2
drop trigger trigger_test1
create trigger trigger_test2 after insert on user for each row
insert into user_logs values(NULL,now(),concat('有新用户添加,信息为：',NEW.username,NEW.password));

insert into user values(4,'abb','123456');

 -- update 触发器
 -- NEW
 -- 定义触发器： trigger_test3
 -- OLD
drop trigger trigger_test2
create trigger trigger_test3 after update on user for each row
insert into user_logs values(NULL,now(),concat('有用户信息修改,旧数据是：',OLD.uid,OLD.username,OLD.password));

update user set password = '00000' where uid=3;

 -- NEW
 drop trigger trigger_test3
create trigger trigger_test4 after update on user for each row
insert into user_logs values(NULL,now(),concat('有用户信息修改：新数据是',NEW.uid,NEW.username,NEW.password));

update user set password = '666666' where uid=3;

-- delete类型触发器
-- OLD
create trigger trigger_test5 after delete on user for each row
insert into user_logs values(NULL,now(),concat('有用户被删除，删除信息为：',OLD.uid,OLD.username,OLD.password));

delete from user where uid=3;