8

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:

  • Neither NEW nor OLD is a table, it's a sort of a language construct, therefor you can't do "SELECT NOW.*" or something similar.
  • Dynamic SQL is not allowed in triggers (this could have solved the problem).
  • Procedures using dynamic SQL are not allowed in triggers (seriously, Oracle, it looks like you have worked really hard to disable this feature no matter what).

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:

  • using EVENTS as a workaround to use triggers in conjunction with dynamic SQL workaround. I have to admit, I don't quite get this, does this mean that EVENT fires every second no matter what?
  • This article says that it is possible to use dynamic SQL inside trigger as long as temporary table is used with it. That is still using dynamic SQL, so I don't quite understand.
Improve this question
2

2 Answers 2

Reset to default
1

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 =)

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

Thanks for your answer, however the sole reason I chose to use triggers is that I wouldn't have to involve PHP - if I chose to implement this functionality with PHP this question wouldn't exist. I have a huge system already coded in PHP and if I had to go in there and implement audit in the code it would be suicidal.
0

i did this for one of the projects by creating a shadow table. if you are not dealing with millions of updates, this might work

  • when the user logs in, SET @user_id = { logged in user id }
  • create a trigger on the table before update to copy the row to be modified to a shadow table with the same structure ( note that you cannot have a primary key in the shadow table nor unique keys )
  • add additional columns to the shadow table ( modified_by, modified_on )
  • create a small php script to show the diff between columns - this way you dont touvh the existing php code base
  • if you are dealing with lots of updates and want to keep the shadow table small, a cron can be written to parse the shadow table and identify which column changed and only store this info to another table
Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.