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

Jim,

How about having the trigger write the pk of the table to a new table.  The
backend processing could then just join the new table on the pk to the
existing table to give you a proper result set.  In the same transaction
delete the contents of the new pk table.  Not as efficient as setting a
flag but relationally sound and portable.

Note that this problem cries out for column triggers.  I don't know if
anyone has them on a to do list.

Rick



                     
                      Jim Archer
                     
                      <jim@archer.net>               To:       pgsql-general@postgresql.org
                     
                      Sent by:                       cc:
                     
                      pgsql-general-owner@pos        Subject:  [GENERAL] How to know a record has been updated, then
resetthe flag?          
                      tgresql.org
                     

                     

                     
                      11/18/2004 12:53 AM
                     
                      Please respond to Jim
                     
                      Archer
                     

                     

                     




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