Alvein

Hello,

I've installed a "For Update" trigger for some table that calls a extended stored procedure (ESP). In some cases, the ESP needs to modify the same table, maybe causing a recursive call of the trigger.

Is there a way to "disable" the trigger inside the code just before the operation that updates the table and "re-enable" it just after? I know I can disable recursive triggering at database level, but I'm developing a database add-on so I don't want to interfere with the existing triggers behavior (if any) for the rest of the tables.


10x.

NEOLLE

Hello,
Im not sure this is possible. This causes mutating effect to the table.

Alvein

mutating??

loquin

Try calling the sp_dboption stored procedure to disable direct recursion before you alter the table, and call it again afterwards to enable direct recursion. I haven't tried it myself, so I don't know if this will work or not.

NEOLLE

Is basically the effect doing updates/modification on a table with an active transaction. All in one procedure.

Alvein

Really, the recursion I get is a nested-type, so I would need to work at database level, which I don't want.

I've "solved" the problem by issuing "ALTER TABLE .... DISABLE TRIGGER ...." just before the ESP call, and "ALTER TABLE .... ENABLE TRIGGER ...." just after. It seems so simple, but I was thinking ALTER TABLE was forbidden inside a trigger definition.

However, I still wonder if there are side effects with this approach.