Dynamically Referencing OLD/NEW values?
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