I am trying to implement database audit task so I can track any backend changes made on any table.
For that, I have created audit table and implemented trigger on the table for which I want to log audit.
Following is my trigger code:
What I am doing is on the event of Insert on TABLE1, I am taking all the columns of table and traverse through each column and insert record in audit table. Here I am facing the problem with the insert query where I try to insert value using @new.columnname which inserts null value. Here columnname is not actual name, it is a variable taken from the column cursor which is not working for me. See following result:
Please provide the solution so I get the value of column in new_value.
I looked in the MySQL documentation (the 5.7 reference) and it said this:
The MySQL Reference Manual wrote:Within the trigger body, the OLD and NEW keywords enable you to access columns in the rows affected by a trigger. OLD and NEW are MySQL extensions to triggers; they are not case sensitive.
In an INSERT trigger, only NEW.col_name can be used; there is no old row. In a DELETE trigger, only OLD.col_name can be used; there is no new row. In an UPDATE trigger, you can use OLD.col_name to refer to the columns of a row before it is updated and NEW.col_name to refer to the columns of the row after it is updated.
But you used "@new" instead of "NEW". Did you find a document somewhere which told you to use the @ character, or am I misreading the reference manual, or something else?
posted 2 years ago
I know the usage of NEW and OLD in triggers. My question is how can we use NEW.<<Variable name which contains column name>> or not. We can user NEW.Column_name directly but what is the way to use a variable instead?
I'd guess you are out of luck -- I didn't find anybody asking your question on e.g. StackOverflow or anywhere else. That's a pity because like I said, what you're trying to do looks like a reasonable thing to do. I could be wrong, but I don't have any other guesses.