how to disable a trigger while he`s opereting?

[mokesql]

hi guys!

is it possible to disable a trigger while during he`s operetion? the problem is that i want that a trigger reacts on updates of a table and then when a condition is activeted the trigger makes him self a update on the table he sits on, so i supose that he reactivete him selfs again and that cause the error i get: ORA-04091: table INVEST.D_NEWS is mutating, trigger/function may not see it

please help me to solve this, thank you

kemo

 

[Dagon]

You seem to be saying you want to update the table that the trigger is firing against. What are you trying to update exactly ? Is it the row which is being updated when the trigger fires or is it some other rows in the table ?

In the first case, you can simply set the :new.<column_name> to a suitable value in the trigger and override/supplement the values given in the update statement.

In the second case, you cannot do this because of the mutating table restrictions. The only option would be to move the update to a statement level rather than row-level trigger.

 

[dudeami]

Yes, you CAN do what you want.

1) Create a package variable - call it something like mypkg.dontfiretrigger.
2) Create an autonomous transaction (procedure) that does what you want (the part that is causing the mutating table now).
3) Modify the existing trigger to:
-- If dontfiretrigger is null or false then
a) set mypkg.dontfiretrigger to true.
b) call the (new) autonomous transaction.
c) set mypkg.dontfiretrigger to false.
-- end if