Re: trigger without trigger call

On Oct 14, 2013, at 8:18, Philipp Kraus <philipp(dot)kraus(at)tu-clausthal(dot)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.