创建用户:
grant all on *.* to ma1@'%' identified by 'ma1' with grant option;
grant all on *.* to ma2@'%' identified by 'ma2' with grant option;
grant all on *.* to ma3@'%' identified by 'ma3' with grant option;
flush privileges;
select user,host,password from mysql.user;
创建与atb表结构相同的5张表:
use test
create table btb(id int,name varchar(50));
create table btb_trace(name varchar(50),time varchar(50),act varchar(20),id varchar(20),sname varchar(50));
create table btb_bak(user varchar(100),id int,name varchar(50),del_time varchar(50));
create table btb_del(user varchar(100),id int,name varchar(50),del_time varchar(50));
create table btb_update(user varchar(100),id int,name varchar(50),del_time varchar(50));
insert into btb_trace (name,time,act,id,sname) values(user(),now(),'insert',0, 'root');
select * from btb_trace;
删除旧触发器:
show triggers \G
drop trigger insert_btb;
drop trigger delete_btb;
drop trigger update_btb;
触发器1(insert触发事件):
\d $$
create trigger insert_btb after insert on btb
for each row
begin
insert into btb_trace (name,time,act,id,sname) values(user(),now(),'insert',new.id,new.name);
insert into btb_bak values(user(),new.id,new.name,now());
end
$$
\d ;
触发器2(delete触发事件):
\d $$
create trigger delete_btb before delete on btb
for each row
begin
insert into btb_trace (name,time,act,id,sname) values(user(),now(),'delete',old.id,old.name);
insert into btb_del values(user(),old.id,old.name,now());
end
$$
\d ;
触发器3(update触发事件):
\d $$
create trigger update_btb after update on btb
for each row
begin
insert into btb_trace(name,time,act,id,sname) values
(user(),now(),'update',concat(old.id,'->',new.id),concat(old.name,'->',new.name));
insert into btb_update values(user(),new.id,new.name,now());
end
$$
\d ;
登录并测试:mysql -uma1 -pma1 -h192.168.50.10
use test;
insert into btb values(1,'tom');
insert into btb values(2,'jack');
insert into btb values(3,'lucy');
update btb set id=10 where id=1;
delete from btb where id=3;
select * from btb_trace;
select * from btb_bak;
select * from btb_update;
select * from btb_del;