Hello list,I'm interested in other's responce to this question as well.
Is there a way in pg to fire a function when a complete (not row by row) set is updated/inserted/deleted, for instance.
update order_detail set confirmed='S' where modifieddate=current_date;
Then after all the affected rows are updated, I need some code to uptaded other tables from the information of the updated rows in the details table.
Thanks in advance.
I recently had to implement an equivelent trigger mechanism.
My solution consist of the following steps:
create an intermediate table to hold the minimum data needed for single action trigger.
create a before trigger on insert, update, and delete which inserts the needed data from the NEW construct if in insert or update and inserts the needed data from OLD if in update or delete with a check for prior existance (by the end of transaction this table is emptied).
create an after insert trigger on the intermediate table to perform withever action is needed on the data, then delete the specific row in the intermediate table.
The intermediate table would have a primary key which could be imposed by a sequence (which I would set to cycle, so as long as you don't have 2^32, simultaneous transactions updating the base table you should be fine).
Hope this helps.
---------------------------(end of broadcast)--------------------------- TIP 9: the planner will ignore your desire to choose an index scan if your joining column's datatypes do not match
