Thread: Trigger to identify which column(s) updated
Does anyone know how to write a trigger that would identify which columns have actually changed in an update (and then log them to an archive). I suspect that the function would look something like; CREATE FUNCTION FIND_CHANGED_COLUMNS() RETURNS OPAQUE AS ' BEGIN -- FOR EACH COLUMN IN THE RECORD: -- IF ( NEW COLUMNx <> OLD COLUMNx) -- LOG THE RECORD PRIMARY KEY, COLUMNNAME, OLD VALUE RETURN NEW; END; ' LANGUAGE 'plpgsql'; In other words- How might you parse, in general, old and new records to compare like columns? - How can you find out the primary key of a record? Thanks, Jack
Re: Trigger to identify which column(s) updated
From
"Vishal Kashyap @ [Sai Hertz And Control Systems]"
Date:
Dear Jack , >I suspect that the function would look something like; > >CREATE FUNCTION FIND_CHANGED_COLUMNS() RETURNS OPAQUE AS ' >BEGIN > > -- FOR EACH COLUMN IN THE RECORD: > -- IF ( NEW COLUMNx <> OLD COLUMNx) > -- LOG THE RECORD PRIMARY KEY, COLUMN NAME, OLD VALUE > > RETURN NEW; >END; >' LANGUAGE 'plpgsql'; > > Instead of using a trigger use a rule as <code> </code> CREATE RULE log_allthat AS ON UPDATE TO table_to_scan WHERE ((NEW.coloum_1 != old.NEW.coloum_1) OR (NEW.coloum_2 != old.NEW.coloum_2)) OR (MORE COLUMNS DO INSERT INTO log_changes_table ( PRIMARY_KEY,COLUMN_NAME,OLD_VALUE ) VALUES ( OLD.PRIMARY_KEY,OLD.COLUMN_NAME,OLD.OLD_VALUE ); Yes you will have to create a table as log_changes_table or any name you fancy such that data could be loged </code> Kindly shoot back if this helps. -- Regards, Vishal Kashyap ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* I Know you believe my words so logon to Jabber.org and add vishalkashyap@jabber.org to your roster. ~*~*~*~*~*~*~*~* I am usually called by the name Vishal Kashyap but my Girl friend believes my name should be Vishal CASH UP.This is because others love my nature and my Girl friend loves my CASH. ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*
Re: Trigger to identify which column(s) updated
From
"V i s h a l Kashyap @ [Sai Hertz And Control Systems]"
Date:
Dear Jack , >for each column in new: > if (new.column(1) != old.column(1)) ... > > ...and then some snippet of code to determine the primary key column >of new (or old) > > Check the following link http://gborg.postgresql.org/project/tablelog/projdisplay.php Hope this helps -- Regards, Vishal Kashyap ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~* I Know you believe my words so logon to Jabber.org and add vishalkashyap@jabber.org to your roster. ~*~*~*~*~*~*~*~* I am usually called as Vishal Kashyap but my Girl friend calls me as Vishal CASH UP. This is because others know me because of my generosity and my Girlfriend knows me because of my CASH. ~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*~*