Обсуждение: trigger without trigger call

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

Thanks

Phil

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
welland those should trigger the update trigger normally. I think Pavel's suggestion, if executed from your delete
triggerfunction, would disable the trigger for that session only, so that would work. Don't forget to re-enable it
againafter 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
triggeryou 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
theneed 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.

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
aswell and those should trigger the update trigger normally. I think Pavel's suggestion, if executed from your delete
triggerfunction, would disable the trigger for that session only, so that would work. Don't forget to re-enable it
againafter 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
deletetrigger, while you normally leave it at the other value (easiest by means of a column DEFAULT value). In your
updatetrigger 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
theneed 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