Disable a trigger from a trigger Oracle

Question

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?

Answer 1

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.