MySQL trigger for change detection

Question

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.

Have you read this: dev.mysql.com/doc/refman/5.0/en/trigger-syntax.html ? — nfechner
– nfechner, Commented Mar 11, 2015 at 9:15
from your answer I understand that I was two mistakes away from the right solution — Alexander Lomov
– Alexander Lomov, Commented Mar 11, 2015 at 9:33

Abhik Chakraborty · Accepted Answer · 2015-03-11 09:19:53Z

1

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.

answered Mar 11, 2015 at 9:19

Abhik Chakraborty

44.9k6 gold badges56 silver badges63 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Alexander Lomov Over a year ago

OMG, it seem to work! 'Dobby is free' :) Now I can have some sleep. Thank you, man!

nfechner · Accepted Answer · 2015-03-11 09:19:08Z

0

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).

answered Mar 11, 2015 at 9:19

nfechner

17.6k7 gold badges48 silver badges64 bronze badges

Collectives™ on Stack Overflow

MySQL trigger for change detection

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related