Re: How to know a record has been updated, then reset

Why don't you check in your update trigger if the new record has the
flag "false" ? In that case you replace new with old, except you set the
flag to false. This way you can reset the flag by a simple update to
false of the flag field. All other queries should not touch the field.
In other words, use the trigger to reset it too, instead of disable
it...

HTH,
Csaba.

On Thu, 2004-11-18 at 06:53, Jim Archer wrote:
> Hi All...
>
> I'm been fighting this problem for a few days now, and it seems like it
> should be simple. But the solution has eluded me so far...
>
> I need to flag a record when it is updated or when it is a new insert.
> Then I SELECT for the changed records and do something not related to
> Postgres. Easy enough, I created a trigger procedure and fired it on
> INSERT OR UPDATE and modify NEW to set the flag field to true.
>
> But then the problem is how do I reset the trigger? If I do an UPDATE the
> trigger fires again. I thought I could check for the flag field being NULL
> and that works for an INSERT, but apparently if it is an update NEW
> contains the existing value of the field.
>
> I am trying to avoid modifying the cost the needs to set the flags (I can
> change the schema), but I have full control over the code that has to reset
> them. Is there a way I can update a record without firing the trigger, or
> by bypassing it? This is a multi-user environment, so I can't really drop
> the trigger and readd it.
>
> Is there a solution not related to this?
>
> I would appreciate some help, thanks very much!
>
>
>
> ---------------------------(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