Disable a trigger from a trigger Oracle
database, oracle, plsql, triggers
Solution
You can almost certainly disable one trigger from another using an `EXECUTE IMMEDIATE` statement:
EXECUTE IMMEDIATE 'ALTER TRIGGER trigger_name_here DISABLE';
However, you definitely shouldn't be using triggers for application logic. It's a messy business, not least due to the fact that triggers aren't guaranteed to fire in order, but also because of the kind of "problem" you're experiencing.
It would be much easier and significantly safer to move all of the functionality you described to a stored procedure or package, and use triggers only where necessary for validation purposes.
Problem
I've stumbled on a situation where I need to disable a trigger from a trigger before doing an update, and then renable it. Basically, I have two tables: `TIME_SLOTS` has fields such as start time, end time, to set the time slot for a programme as well as programme ID (foreign key) to specify which program. `PROGRAMMES` contains a list of all the different available programs & their details. Also contains a duration. I have an existing trigger that, when updating or inserting to `TIME_SLOTS`, the trigger lookups the duration from `PROGRAMMES` and ensures that End Time = Start Time + Duration. I also want to add a new trigger that updates the End Time in `TIME_SLOTS` when changing the duration in `PROGRAMMES`. I have set these two triggers up, but when changing the duration I get: ``` One error saving changes to table "SE217"."PROGRAMMES": Row 1: ORA-04091: table SE217.PROGRAMMES is mutating, trigger/function may not see it ORA-06512: at "SE217.SCHEDULES_VALID_TIMES", line 19 ORA-04088: error during execution of trigger 'SE217.SCHEDULES_VALID_TIMES' ORA-06512: at "SE217.UPDATE_END_TIME", line 5 ORA-04088: error during execution of trigger 'SE217.UPDATE_END_TIME' ``` This is obviously because when I change the duration, the 2nd trigger goes to update the end time in `TIME_SLOTS`. The trigger on `TIME_SLOTS` fires and looks up the duration - the duration is mutating and I get the error as above. It seems to me that when I update the `TIME_SLOTS` row with the newly calculated end time, I should just disable the trigger before and renable after the update - but as this is trigger I can't alter a trigger... Any ideas? EDIT: I had a thought that I could set a global variable and check this var in the trigger that I don't want to run etc - but wasn't sure how best to implement?