MySQL update trigger - find modified columns?

Question

I have a table with 120 columns. I need to set up an audit trail that will record any column if it has been modified. As now, I assume that a trigger with the condition must be set for each column:

IF(NEW.columnName != OLD.columnName) THEN //log the old value 

This needs to be done 120 times ... Although I would take this approach 20 years ago, today I refuse to believe that it is impossible to automate such a simple procedure that automatically finds modified columns.

This is what I have discovered so far:

• Neither NEW nor OLD is a table, it is a kind of language construct, so you cannot do "SELECT NOW. *" Or something like that.
• Dynamic SQL is not allowed in triggers (this could solve the problem).
• Procedures using dynamic SQL are not allowed in triggers (seriously, Oracle, it looks like you worked very hard to disable this feature no matter what).

I thought of using BEFORE and AFTER triggers in combination with temporary tables and variables that could solve this problem, however, dynamic SQL would be needed again. I feel like I'm at a standstill.

Is there a solution for this?

Question: is this possible in PostgreSQL?

UPDATE : I found 2 potential solutions, however none of them seem clear enough to me:

• Using EVENTS as a workaround to use triggers in combination with dynamic SQL workaround . I have to admit that I did not quite understand this, does this mean that EVENT is triggered every second no matter what?
• This article says that you can use dynamic SQL inside a trigger while a temporary table is being used with it. It still uses dynamic SQL, so I don’t quite understand.

Answer 1

Interestingly, I encountered the same problem a couple of years ago with the introduction of a dynamic audit log based on triggers. The solution I came up with was simply to generate the SQL startup code, which can then (automatically) be used to replace the old trigger definitions. If memory is used, I created several SQL templates that were processed by a PHP script, which in turn displayed complete trigger definitions based on "SELECT COLUMN_NAME FROM information_schema.COLUMNS WHERE ..." Yes, the startup code was huge, but it works! Hope that helps a bit =)

Answer 2

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

• when the user logs in, SET @user_id = {logged in to the user ID}
• create a trigger in the table before updating to copy the row to be changed into the shadow table with the same structure (note that you cannot have a primary key in the shadow table and unique keys)
• add additional columns to the shadow table (modified_by, modified_on)
• create a small php script to show the difference between the columns - this way you are not using the existing php code base
• If you are dealing with a large number of updates and want the shadow table to be small, you can write cron to analyze the shadow table and determine which column has been changed, and store this information only in another table.