By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,710 Members | 1,970 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,710 IT Pros & Developers. It's quick & easy.

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

P: n/a
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

Nov 23 '05 #1
Share this Question
Share on Google+
3 Replies


P: n/a

On Nov 18, 2004, at 2:53 PM, Jim Archer wrote:
This is a multi-user environment, so I can't really drop the trigger
and readd it.


Would it work to drop and readd the trigger within a transaction? Would
that make it multi-user safe? I can't think of another way to bypass an
update trigger.

Michael Glaesemann
grzm myrealbox com
---------------------------(end of broadcast)---------------------------
TIP 7: don't forget to increase your free space map settings

Nov 23 '05 #2

P: n/a
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

---------------------------(end of broadcast)---------------------------
TIP 1: subscribe and unsubscribe commands go to ma*******@postgresql.org

Nov 23 '05 #3

P: n/a
Jim Archer <ji*@archer.net> writes:
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 think you need a three-state value instead of a boolean. The trigger
has to account for four cases:
* freshly inserted row (which will have the field's default value)
* newly updated row
* re-updated row (where we don't want to reset the flag)
* update that is supposed to reset the flag
and you simply cannot tell the third and fourth cases apart without
an additional state.

One possibility is to make the flag field be "int default 0", with
trigger logic along the lines of

if new.flag = 0 then
-- freshly inserted or newly updated row, so set flag
new.flag = 1;
elsif new.flag = 1 then
-- re-update, no change needed
elsif new.flag = 2 then
-- command to reset flag
new.flag = 0;
else
-- possibly raise error here
end if;

and obviously the convention for resetting the flag is to attempt to
update it to 2.

(Thinks some more...) Actually you could stick with a boolean field,
if you make use of NULL as your third state --- that is, the convention
becomes that the command for resetting the flag is to attempt to update
it to NULL. However this might be more fragile than the above, since
you can certainly imagine ordinary inserts or updates accidentally doing
it.

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 2: you can get off all lists at once with the unregister command
(send "unregister YourEmailAddressHere" to ma*******@postgresql.org)

Nov 23 '05 #4

This discussion thread is closed

Replies have been disabled for this discussion.