CODEWITHSUNDEEP
mysqltriggers
Alexander Lomov
Alexander Lomov

Reputation: 119

MySQL trigger for change detection

I have several tables in the database: Users, Profiles, Articles

I also have one table called Changes, which is used for administrative purposes. This table consists of id, table_name, and date_created. What I need to do is whenever something is added, deleted or updated in a regular table (Users, Profiles, Articles), create a new row in the Changes with the name of the updated table and the current timestamps.

I've been browsing for a while and tried many different methods, but nothing really worked. I know the solution should be very simple, may be someone can help me. Thank you for your time.

Upvotes: 0

Views: 679

Answers (2)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44874

So in this case you need 9 trigger 3 for each of the regular table after insert, after update, after delete

Here is for one table you can write for the others

When you insert on Users

delimiter //
create trigger log_user_insert after insert on Users
for each row 
begin
  insert into Changes (table_name,date_created) values ('Users',now());
end; //

delimiter ;

When update happens on Users

delimiter //
create trigger log_user_update after update on Users
for each row 
begin
  insert into Changes (table_name,date_created) values ('Users',now());
end; //

delimiter ;

When delete happens on Users

delimiter //
create trigger log_user_update after delete on Users
for each row 
begin
  insert into Changes (table_name,date_created) values ('Users',now());
end; //

delimiter ;

I would suggest to add a column called action in the table Changes and to insert each action name as well i.e. insert,update and delete.

Upvotes: 1

nfechner
nfechner

Reputation: 17535

You need to create an update, insert and delete trigger on each of the data tables:

CREATE TRIGGER upd_changes_users BEFORE UPDATE ON Users
FOR EACH ROW
 BEGIN
     INSERT INTO changes (table_name, date_created) VALUES ('users', NOW());
 END;

This code assumes, that the id column in changes is auto_generated. You might also want to consider including a type column in the changes table (to differentiate between insert, update and delete).

Upvotes: 0

Related Questions