MySQL Forums
Forum List  »  Triggers

Dynamically Referencing OLD/NEW values?
Posted by: Chad Bachmeyer
Date: January 29, 2013 03:17PM

I am a trigger newbie working on a trigger that will log table changes into a history table in xml format. Basically when a row is updated, the old row gets written into an xml text field which is inserted into history_table.xml.

CREATE TRIGGER history BEFORE UPDATE ON table1
FOR EACH ROW
BEGIN
DECLARE xmloutput TEXT;

SET xmloutput = concat(
"<field name=field1>",OLD.field1,"</field>",
"<field name=field2>",OLD.field2,"</field>"
"<field name=field3>",OLD.field3,"</field>"
);

insert into `history_table` (`tableName`,`updated_by`,`xml`) values ('table1',NEW.updated_by,xmloutput);
END

To keep from having to write out each field in "table1" (some of these tables can contain over 100 fields) and to keep from having to drop/create a new trigger whenever columns are added/removed from "table1", is it possible to loop through "table1" columns and reference the corresponding OLD/NEW values dynamically?

Any help would be greatly appreciated.
Thank You,
Chad

Options: ReplyQuote


Subject
Views
Written By
Posted
Dynamically Referencing OLD/NEW values?
6791
January 29, 2013 03:17PM


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.