trigger without trigger call

Hello

you can disable trigger by ALTER TABLE tablename DISABLE TRIGGER
triggername;

but it should be executed with owner rights on table.

This statement works perfect, but a fact, so you need it signalize so you
do some wrong. Triggers should to be used primary for checking, logging,
calculating some simply calculated values. Anything else is can carry some
very negative effects with impacts on readability or performance (although
sometimes it is necessary). Try to move some logic from triggers to
functions with explicit call.

Regards

Pavel Stehule

2013/10/14 Philipp Kraus <philipp.kraus@tu-clausthal.de>

On Oct 14, 2013, at 8:18, Philipp Kraus <philipp.kraus@tu-clausthal.de> wrote:

Hello,

I have written a update & delete trigger of a table.
My delete trigger runs an update statement but this create a (semantic) problem.
How can I disable the update trigger for only this update call within the delete trigger?
So my delete trigger need not call the update trigger

You obviously don't want to disable the trigger entirely for all sessions, as other sessions may be updating records as well and those should trigger the update trigger normally. I think Pavel's suggestion, if executed from your delete trigger function, would disable the trigger for that session only, so that would work. Don't forget to re-enable it again after the update finishes.

Another solution is to make your update trigger smarter about when it needs to do its thing.
For example, you could add a "fake" boolean column to the table and set that to one value when updated from your delete trigger, while you normally leave it at the other value (easiest by means of a column DEFAULT value). In your update trigger you can then test for the value of that column and escape out of the trigger function (RETURN NEW, for example).

Nowadays you can put triggers on views even, in which case you could do the above on a view over the table, omitting the need to actually store the fake column value.

Alban Hertroys
--
If you can't see the forest for the trees,
cut the trees and you'll find there is no forest.

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

Am 14.10.2013 um 11:49 schrieb Alban Hertroys <haramrae@gmail.com>:

On Oct 14, 2013, at 8:18, Philipp Kraus <philipp.kraus@tu-clausthal.de> wrote:

Hello,

I have written a update & delete trigger of a table.
My delete trigger runs an update statement but this create a (semantic) problem.
How can I disable the update trigger for only this update call within the delete trigger?
So my delete trigger need not call the update trigger

You obviously don't want to disable the trigger entirely for all sessions, as other sessions may be updating records as well and those should trigger the update trigger normally. I think Pavel's suggestion, if executed from your delete trigger function, would disable the trigger for that session only, so that would work. Don't forget to re-enable it again after the update finishes.

Another solution is to make your update trigger smarter about when it needs to do its thing.
For example, you could add a "fake" boolean column to the table and set that to one value when updated from your delete trigger, while you normally leave it at the other value (easiest by means of a column DEFAULT value). In your update trigger you can then test for the value of that column and escape out of the trigger function (RETURN NEW, for example).

Nowadays you can put triggers on views even, in which case you could do the above on a view over the table, omitting the need to actually store the fake column value.

I have got a plsql function like that:

begin

if (TG_OP = 'DELETE') then
update simulation.source set parent=old.parent where parent=old.id; *
return old;

elseif (TG_OP = 'UPDATE') then

do something

end if;

end

This function is called on the before delete & update call, so the * call creates a problem. On this call the
elseif (TG_OP = 'UPDATE') then need not run at any time. I think the boolean field can be helpful, but
is there another idea to disable on the * call the update trigger?

Thanks

Phil

--
Sent via pgsql-general mailing list (pgsql-general@postgresql.org)
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general

On Mon, Oct 14, 2013 at 6:02 AM, Philipp Kraus <
philipp.kraus@tu-clausthal.de> wrote:

Am 14.10.2013 um 11:49 schrieb Alban Hertroys <haramrae@gmail.com>:

On Oct 14, 2013, at 8:18, Philipp Kraus <philipp.kraus@tu-clausthal.de>

wrote:

Hello,

I have written a update & delete trigger of a table.
My delete trigger runs an update statement but this create a (semantic)

problem.

How can I disable the update trigger for only this update call within

the delete trigger?

So my delete trigger need not call the update trigger

You obviously don't want to disable the trigger entirely for all

sessions, as other sessions may be updating records as well and those
should trigger the update trigger normally. I think Pavel's suggestion, if
executed from your delete trigger function, would disable the trigger for
that session only, so that would work. Don't forget to re-enable it again
after the update finishes.

Another solution is to make your update trigger smarter about when it

needs to do its thing.

For example, you could add a "fake" boolean column to the table and set

that to one value when updated from your delete trigger, while you normally
leave it at the other value (easiest by means of a column DEFAULT value).
In your update trigger you can then test for the value of that column and
escape out of the trigger function (RETURN NEW, for example).

Nowadays you can put triggers on views even, in which case you could do

the above on a view over the table, omitting the need to actually store the
fake column value.

I have got a plsql function like that:

begin

if (TG_OP = 'DELETE') then
update simulation.source set parent=old.parent where parent=
old.id; *
return old;

elseif (TG_OP = 'UPDATE') then

do something

end if;

end

This function is called on the before delete & update call, so the * call
creates a problem. On this call the
elseif (TG_OP = 'UPDATE') then need not run at any time. I think the
boolean field can be helpful, but
is there another idea to disable on the * call the update trigger?

Thanks

Phil

What about using UPDATE OF?

CREATE TRIGGER test_simulation_trigger BEFORE DELETE OR UPDATE OF C1,C2,C3
on simulation FOR EACH ROW EXECUTE PROCEDURE trigger_function();

You can include all columns to be used for update trigger and omit parent
column.

-Luis