MySQL triggers record commands for user actions

If you have an important table BTB, you need several administrators to manage

admin: [email protected], [email protected], [email protected]

Required to create a trigger for table BTB:

Trigger Trigger Requirements:

1 , when an INSERT, UPDATE, delete operation is made in the BTB table, the user, time, and operation commands that perform the operation are recorded in the Btb_trace table.

1 , when a new record is inserted in the BTB table, all the data in the ATB table is recorded in the Atb_bak table. Keep a synchronous backup.

2 , a copy of the ATB deleted data is recorded in the Atb_del table before the data is deleted in the BTB table.

3 , when the data is updated in the BTB table, a copy of the data before and after the ATB data update is recorded in the Atb_update table.

Modified version (optimized) Description: A trigger uses new and old to pass data to a table in which the trigger operates. New represents the value of the Action field (for INSERT and UPDATE trigger events), and old for the previous value of the field (for the Delete trigger event). Format: New. field name; old. Field name.

To create a user: Grant all on * * to [e-mail protected] '% ' identified by ' ma1 ' with GRANT option; Grant all on * * to [e-mail protected] '% ' identified by ' ma2 ' with GRANT option; Grant all on * * to [e-mail protected] '% ' identified by ' ma3 ' with GRANT option; Flush privileges; Select User,host,password from Mysql.user; Create the same 5 tables as the ATB table structure: Use test CREATE TABLE BTB (ID int,name varchar (50)); CREATE TABLE Btb_trace (name varchar, time varchar (), act varchar, id varchar (), sname varchar (50)); CREATE TABLE Btb_bak (user varchar, id int,name varchar (), del_time varchar (50)); CREATE TABLE Btb_del (user varchar, id int,name varchar (), del_time varchar ) ; CREATE TABLE btb_update (user varchar, id int,name varchar (), del_time varchar (50)); Insert into Btb_trace (name,time,act,id,sname) VALUES (user (), now (), ' Insert ', 0, ' root '); SELECT * from Btb_trace; To delete an old trigger: Show triggers \g Drop trigger INSERT_BTB; Drop trigger DELETE_BTB; Drop trigger UPDATE_BTB; Trigger 1 (Insert trigger Event): \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; Trigger 2 (Delete trigger event): \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; Trigger 3 (Update trigger event): \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; Log in and test: 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;

This article is from the "Network Technology World" blog, please be sure to keep this source http://1364952.blog.51cto.com/1354952/1954611

MySQL triggers record commands for user actions