Am 14.10.2013 um 11:49 schrieb Alban Hertroys <[email protected]>:
> On Oct 14, 2013, at 8:18, Philipp Kraus <[email protected]> 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 ([email protected])
To make changes to your subscription:
http://www.postgresql.org/mailpref/pgsql-general
