Menu
I have a table with 120 columns. I need to set up audit trail which would log any column if it was changed. As it is now, I guess I have to set up a trigger with condition something like this for every column:
IF(NEW.columnName != OLD.columnName)
THEN //log the old value
This would need to be done 120 times... While I would have accepted this approach 20 years ago, today I refuse to believe it's impossible to automate such a simple procedure finding changed columns automatically.
This is what I discovered so far:
I was thinking to use BEFORE and AFTER triggers in conjunction with temporary tables and variables which would have possibly solved the problem, however yet again dynamic SQL would be required. I feel like I hit a dead end.
Is there a solution to this at all?
A side question: would this be possible in PostgreSQL?
UPDATE: I found 2 potential solutions however neither of them look clear enough to me:
348
Using a SQL Server trigger to check if a column is updated, there are two ways this can be done; one is to use the function update(<col name>) and the other is to use columns_updated().
UPDATE. An UPDATE trigger can refer to both OLD and NEW transition variables. INSERT. An INSERT trigger can only refer to a NEW transition variable because before the activation of the INSERT operation, the affected row does not exist in the database.
In SQL Server, you can create DML triggers that execute code only when a specific column is updated. The trigger still fires, but you can test whether or not a specific column was updated, and then run code only if that column was updated. You can do this by using the UPDATE() function inside your trigger.
AFTER UPDATE Trigger is a kind of trigger in SQL that will be automatically fired once the specified update statement is executed. It can be used for creating audit and log files which keep details of last update operations on a particular table.
interesting, I was facing the same problem couple of years ago with implementing dynamic trigger-based audit log. The solution I came up with was to simply generate the SQL trigger code which then can be (automatically) applied to replace old trigger definitions. If memory serves, I created few SQL templates which were processed by a PHP script which in turn was outputting complete trigger definitions based on "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE ..." Yes, the trigger code was huge, but it worked! Hope that helps a little =)
186
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
