Using MySQL triggers to log all table changes to a secondary table

by

The insert syntax is

INSERT INTO table (columns_list) VALUES (values_list)

so your insert would look something like this (i’m not a MySQL expert but you can fit the query):

INSERT INTO data_tracking 
(`data_id` , `field` , `old_value` , `new_value` , `modified` ) 
VALUES 
(NEW.data_id, 'field1', OLD.field, NEW.field, CURRENT_DATETIME());

And repeat it for variation on field2 and field3

I think this would be the complete trigger, please try:

DELIMITER $$

DROP TRIGGER `update_data `$$

CREATE TRIGGER `update_data` AFTER UPDATE on `data_table`
FOR EACH ROW
BEGIN
    IF (NEW.field1 != OLD.field1) THEN
        INSERT INTO data_tracking 
            (`data_id` , `field` , `old_value` , `new_value` , `modified` ) 
        VALUES 
            (NEW.data_id, "field1", OLD.field1, NEW.field1, NOW());
    END IF;
    IF (NEW.field2 != OLD.field2) THEN
        INSERT INTO data_tracking 
            (`data_id` , `field` , `old_value` , `new_value` , `modified` ) 
        VALUES 
            (NEW.data_id, "field2", OLD.field2, NEW.field2, NOW());
    END IF;
    IF (NEW.field3 != OLD.field3) THEN
        INSERT INTO data_tracking 
            (`data_id` , `field` , `old_value` , `new_value` , `modified` ) 
        VALUES 
            (NEW.data_id, "field3", OLD.field3, NEW.field3, NOW());
    END IF;
END$$

DELIMITER ;

More Related Contents:

  1. Throw an error preventing a table update in a MySQL trigger
  2. How to abort INSERT operation in MySql trigger?
  3. MySql Error: Can’t update table in stored function/trigger because it is already used by statement which invoked this stored function/trigger
  4. MySQL Trigger after update only if row has changed
  5. mysql after insert trigger which updates another table’s column
  6. How to program a MySQL trigger to insert row into another table?
  7. TRIGGERs that cause INSERTs to fail? Possible?
  8. Use a trigger to stop an insert or update
  9. MySQL Fire Trigger for both Insert and Update
  10. MySQL trigger On Insert/Update events
  11. Can a MySQL trigger simulate a CHECK constraint? [duplicate]
  12. MySQL error 1436: Thread stack overrun, with simple query
  13. MySQL trigger to update a field to the value of id
  14. How can I set a maximum number of rows in MySQL table?
  15. mysql trigger stored trigger is already used by statement which invoked stored trigger
  16. My MySQL trigger doesn’t work, simple syntax, not complicated
  17. MySQL triggers cannot update rows in same table the trigger is assigned to. Suggested workaround?
  18. MySQL Trigger: Delete From Table AFTER DELETE
  19. Is it Possible to Enforce Data Checking in MySQL using Regular expression
  20. Can I launch a trigger on select statement in mysql?
  21. MySQL: How do I use delimiters in triggers?
  22. MySQL listen notify equivalent
  23. MySQL disable all triggers
  24. Calling an url from a trigger in mysql
  25. MySQL – Rows to Columns
  26. MySQL: How do I join same table multiple times?
  27. When to use MyISAM and InnoDB? [duplicate]
  28. How to initialize mysql container when created on Kubernetes?
  29. Find duplicates in the same table in MySQL
  30. Auto-increment is not resetting in MySQL

Leave a Comment