I am creating a system where I have a trigger on three different tables.
There is a particular Boolean field in each of these tables that when it is
set in table it should be set the same in the other two.



So I figured I could put a trigger on each table that when the Boolean field
was updated it would go and update the other 2.



However, I am concerned about cascading trigger calls.



Does anyone know if it is possible to run an update statement on a table and
for only that statement disable the trigger on the table?

You could add a TIMESTAMP field on the three tables (lets call it
last_change), and modify your triggers to update this value every time a row
is updated.

Then your trigger should update the boolean fields with the boolean value of
the row with the max(last_change) in the three tables, only if the row of
the table the trigger is being fired for is less than this max(last_change)
value.

Hope this is understandable

Of course you could remove the boolean value from the three tables, create
another table with the boolean value, and forget about the triggers. But I'm
sure you have already though that.

Hope it helps.

2005/5/11, Mark Borins <mark.borins@rigadev.com>:
>
> I am creating a system where I have a trigger on three different tables.
> There is a particular Boolean field in each of these tables that when it is
> set in table it should be set the same in the other two.
>
> So I figured I could put a trigger on each table that when the Boolean
> field was updated it would go and update the other 2.
>
> However, I am concerned about cascading trigger calls.
>
> Does anyone know if it is possible to run an update statement on a table
> and for only that statement disable the trigger on the table?
>

Thank you for the response.

While the TIMESTAMP solution is a good idea.
I was wondering if anyone knew of a SQL like instruction that could be
called.

Like:
UPDATE table SET.... WHERE.... NO TRIGGERS

Or something like that, however, I realize I may just be dreaming.

Mark

________________________________________
From: Franco Bruno Borghesi [mailto:fborghesi@gmail.com]
Sent: May 11, 2005 11:24 AM
To: Mark Borins
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Disabling Triggers

You could add a TIMESTAMP field on the three tables (lets call it
last_change), and modify your triggers to update this value every time a row
is updated.

Then your trigger should update the boolean fields with the boolean value of
the row with the max(last_change) in the three tables, only if the row of
the table the trigger is being fired for is less than this max(last_change)
value.

Hope this is understandable

Of course you could remove the boolean value from the three tables, create
another table with the boolean value, and forget about the triggers. But I'm
sure you have already though that.

Hope it helps.
2005/5/11, Mark Borins <mark.borins@rigadev.com>:
I am creating a system where I have a trigger on three different tables.*
There is a particular Boolean field in each of these tables that when it is
set in table it should be set the same in the other two.
*
So I figured I could put a trigger on each table that when the Boolean field
was updated it would go and update the other 2.
*
However, I am concerned about cascading trigger calls.
*
Does anyone know if it is possible to run an update statement on a table and
for only that statement disable the trigger on the table?



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

"Mark Borins" <mark.borins@rigadev.com> writes:
> Does anyone know if it is possible to run an update statement on a table and
> for only that statement disable the trigger on the table?

No, but why fire the update if not needed? Make the trigger do
something like

UPDATE foo SET boolcol = true WHERE ... AND not boolcol;

regards, tom lane

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org

Mark Borins wrote:
> I am creating a system where I have a trigger on three different tables.
> There is a particular Boolean field in each of these tables that when it is
> set in table it should be set the same in the other two.

Just make sure you only check the boolean value too:

-- Trigger on table a does:
IF NEW.my_bool=OLD.my_bool THEN
RETURN NEW;
END IF;

UPDATE b SET my_bool=NEW.my_bool
WHERE id=NEW.something AND my_bool <> NEW.my_bool
UPDATE c SET my_bool=NEW.my_bool
WHERE id=NEW.something AND my_bool <> NEW.my_bool
-- End code

That way, you always do the minimal amount of work anyway.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster

That looks like a good solution.
And that way it won't cascade.

Thanks

-----Original Message-----
From: pgsql-general-owner@postgresql.org
[mailtogsql-general-owner@postgresql.org] On Behalf Of Richard Huxton
Sent: May 11, 2005 11:53 AM
To: Mark Borins
Cc: pgsql-general@postgresql.org
Subject: Re: [GENERAL] Disabling Triggers

Mark Borins wrote:
> I am creating a system where I have a trigger on three different tables.
> There is a particular Boolean field in each of these tables that when it
is
> set in table it should be set the same in the other two.

Just make sure you only check the boolean value too:

-- Trigger on table a does:
IF NEW.my_bool=OLD.my_bool THEN
RETURN NEW;
END IF;

UPDATE b SET my_bool=NEW.my_bool
WHERE id=NEW.something AND my_bool <> NEW.my_bool
UPDATE c SET my_bool=NEW.my_bool
WHERE id=NEW.something AND my_bool <> NEW.my_bool
-- End code

That way, you always do the minimal amount of work anyway.
--
Richard Huxton
Archonet Ltd

---------------------------(end of broadcast)---------------------------
TIP 4: Don't 'kill -9' the postmaster


---------------------------(end of broadcast)---------------------------
TIP 5: Have you checked our extensive FAQ?

http://www.postgresql.org/docs/faq

Mark Borins wrote:
> I am creating a system where I have a trigger on three different
> tables. There is a particular Boolean field in each of these tables
> that when it is set in table it should be set the same in the other two.
>
>
>
> So I figured I could put a trigger on each table that when the Boolean
> field was updated it would go and update the other 2.
>
>
>
> However, I am concerned about cascading trigger calls.

You could make those triggers like below and let them trigger their
equivalents on one of the other two tables:

table_a:
if old.value != new.value then
update table_b set value = new.value where id=new.id
endif;

table_b:
if old.value != new.value then
update table_c set value = new.value where id=new.id
endif;

table_c:
if old.value != new.value then
update table_a set value = new.value where id=new.id
endif;

This way, if the value is the desired value, no more updates are done.
The chain reaction stops as soon as all three tables have the desired
values.

Nothing wrong with cascading triggers, as long as you're aware of what
you're doing. It can be useful to put this on paper schematically; there
may even be some standard schematic notation for triggers and cascading
(or that would be useful if there isn't...).

> Does anyone know if it is possible to run an update statement on a table
> and for only that statement disable the trigger on the table?

--
Alban Hertroys
MAG Productions

T: +31(0)53 4346874
F: +31(0)53 4346876
E: alban@magproductions.nl
W: http://www.magproductions.nl

---------------------------(end of broadcast)---------------------------
TIP 6: Have you searched our list archives?

http://archives.postgresql.org