MYSQL触发器记录用户操作的命令

来源:转载


假如有一张重要的表btb,需要几个管理员来管理

管理员:[email protected][email protected][email protected]

要求给表btb创建触发器:

trigger触发器需求:

1、当在btb表中做insert、update、delete操作时,在btb_trace表中记录执行操作的用户、时间、操作命令。

1、当在btb表中插入新记录时,atb_bak表中会将atb表中的所有数据记录下来。保持同步备份。

2、当在btb表中删除数据前,在atb_del表中将atb删除的数据记录一份。

3、当在btb表中更新数据时,在atb_update表中将atb数据更新前后的数据记录一份。

修改版(优化)

说明:触发器中用new和old来向触发器操作的表中传递数据。new表示操作字段的新值(用于insert和update触发事件),old表示字段的旧值(用于delete触发事件)。

格式:new.字段名 ;old.字段名。

创建用户:

grant all on *.* to [email protected]'%' identified by 'ma1' with grant option;

grant all on *.* to [email protected]'%' identified by 'ma2' with grant option;

grant all on *.* to [email protected]'%' 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;


分享给朋友:
您可能感兴趣的文章:
随机阅读: