Re: After update complete set

From: DeJuan Jackson <djackson(at)speedfc(dot)com>
To: Josué Maldonado <josue(at)lamundial(dot)hn>
Cc: pgsql-general(at)postgresql(dot)org
Subject: Re: After update complete set
Date: 2004-05-13 23:05:16
Message-ID: 40A3FF2C.6040907@speedfc.com
Views: Raw Message | Whole Thread | Download mbox | Resend email
Thread:
Lists: pgsql-general

Josué Maldonado wrote:

> Hello list,
>
> 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'm interested in other's responce to this question as well.
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.

In response to

Browse pgsql-general by date

  From Date Subject
Next Message Tom Lane 2004-05-14 00:29:33 Re: DBI remote connection problem
Previous Message Jeffrey W. Baker 2004-05-13 22:54:52 Re: pg_xlog becomes extremely large during CREATE INDEX