MySQL Forums
Forum List  »  Triggers

Referencing NEW values in Trigger
Posted by: Robert Doyle
Date: October 27, 2005 02:17PM

Hi
I'm trying to create a generic audit trigger I can add to all my user tables.
This is relatively easy to do. But my issue is that I want to make it Dynamic in that if a new column is added to a table or taken away (product still in early development) I want the trigger to react and continue to audit correctly.

At the moment my idea is to use the information_schema and get details on the table which the trigger is on and select the column details and the NEW value.

e.g.

insert into testaudit (AuditTime, AuditUser, TableName, ColumnName,NewValue)
select CURRENT_TIMESTAMP(), USER(), TABLE_NAME, COLUMN_NAME, NEW.COLUMN_NAME from information_schema.columns where Table_name = 'CalledFigures' and Table_schema = 'Ireland';

My problem as you can see from the above is that I can't evaluate the NEW column, I've thought of a loop but I still come back to the evaluate issue.

Any ideas?
I'm using 5.0.15.

Thanks
Rob

Options: ReplyQuote


Subject
Views
Written By
Posted
Referencing NEW values in Trigger
5573
October 27, 2005 02:17PM
2551
October 28, 2005 03:41PM


Sorry, you can't reply to this topic. It has been closed.

Content reproduced on this site is the property of the respective copyright holders. It is not reviewed in advance by Oracle and does not necessarily represent the opinion of Oracle or any other party.