0

I'm trying to write a mini-auditing system for individual tables in MySQL.

I had good luck working with basic INSERT/UPDATE/DELETE commands, however now a table I'm interested in auditing uses INSERT ... ON DUPLICATE KEY UPDATE.

By using a trigger ON BEFORE INSERT I can tell this event is occurring, however I can only get half the data I'm interested in. NEW.values are readily available, but I've no idea how to get the OLD.values that came before. I suppose I could do a query using the NEW.ID in existing table, but I'm not sure about performance and reliability.

I need the OLD.values because I'm storing both old and new values for each change event since I read somewhere that was a good idea for collapsing data etc...

Is there a way in MySQL 5.0 (or newer GA release) to reliably retrieve these values as though I were in an UPDATE trigger?

EDIT: Another wrinkle:

Looks like the NEW.values do not match the data after update. They match the INSERT statement not the ON DUPLICATE KEY UPDATE data that will actually go into the record.

1 Answers1

0

It looks like the trigger event ON AFTER UPDATE also catches the ON DUPLICATE KEY UPDATE change. From here, I was able to get OLD/NEW values and perform the logging I needed to perform.