Mysql triggers : OLD and NEW as a string/xml/parameter?
I would like to store all changes to my tables (in MySQL). I created a table 'audit' to track all changes of all tables in one table (so I don't have to create audit-tables for each table).
I then created triggers for each table and a stored procedure that inserts a record into the audit-table. The parameters for the stored procedure are the tablename and the primary id. Now I'm able to track the insert/update/delete dates for each record in my database.
But I also would like to trace all changes to the DATA with this procedure. For this I'd have to find a way to use the OLD and NEW records from the triggers in the stored procedure.
Anybody know how to do this? Maybe some kind of serializing the records OLD and NEW into a string??
The only solution that I could derive was checking the OLD & NEW values of every field in my table.
BEGIN -- record changes to table log_new IF OLD.fieldA != NEW.fieldA OR OLD.fieldB != NEW.fieldB THEN INSERT INTO log_new (