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.
Community
  • 1
  • 1
Caballero
  • 11,546
  • 22
  • 103
  • 163
  • Have you checked this with [Session Information Functions in Postgres as for your last question](http://stackoverflow.com/questions/8759595/within-a-trigger-function-how-to-get-which-fields-are-being-updated) – bonCodigo Feb 05 '13 at 09:32
  • @bonCodigo Thanks, apparently PostgreSQL is more flexible in this case. – Caballero Feb 05 '13 at 09:47

2 Answers2

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

MOCKBA
  • 1,680
  • 11
  • 19
  • 2
    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. – Caballero Feb 06 '13 at 09:06
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
itz_nsn
  • 648
  • 1
  • 8
  • 13