CODEWITHSUNDEEP
mysqltriggersinsertedit
Cosmin
Cosmin

Reputation: 954

Using MySQL triggers to clone entries in a table after insert or edit

I know its possible to clone an entry from one table into another table using MySQL triggers and I would like to make this operation for both insert and edit actions. For insert action I managed to find a way, but I can't seem to make it work for edit.

To put it simple, my tables look something like this:

The main table

+---------------------------------------------------+
| lead_id, title, owner_id, description, date_added |
+---------------------------------------------------+

The audit table

+----------------------------------------------------------------------------+
| audit_id, lead_id, title, owner_id, description, date_added, date_modified |
+----------------------------------------------------------------------------+

If lets say I want to edit the entry with lead_id = '4' from the main table, how can I get that id in the trigger in order to save the modified entry in the audit table with lead_id = '4'?

Upvotes: 0

Views: 177

Answers (1)

Abhik Chakraborty
Abhik Chakraborty

Reputation: 44844

For the insert you can log the data into audit as

delimiter //
create trigger audit_insert after insert on main_table
for each row
begin
  insert into audit_table(lead_id,title,owner_id,description,date_added)
  values
  (new.lead_id,new.title,new.owner_id,new.description,now());
end ; //
delimiter ;

For the update you can have the following trigger

delimiter //
create trigger audit_update after update on main_table
for each row 
begin
  insert into audit_table(lead_id,title,owner_id,description,date_added,date_modified)
  values
  (new.lead_id,new.title,new.owner_id,new.description,old.date_added,now());
end ; //

delimiter ;

Make sure the audit table audit_id is auto incremented and date fields are having datetime datatype.

Upvotes: 2

Related Questions