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?

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.

Comments

[Ben]

No, you can't store triggers in a package but you should have warnings flashing in front of your face. Disabling triggers inside triggers is never the correct option and the error you're getting is indicative of not having a properly normalised database. @Egor is suggesting that you remove all the logic from your triggers and place it in a package instead, which is often the way around this error.

[David Aldridge]

You're doing two unpopular and generally frowned-on things here: using triggers for anything other than auditing activities, and storing redundant data (ie denormalising).

[Dave Clarke]

I sort of understand what you mean but don't really get how to implement this. Surely I need a trigger to update the other table when the first gets changed? Are you suggesting calling the procedure in the trigger or?

[Ben]

What we're all suggesting is that instead of directly issuing an INSERT or UPDATE statement against the first table and then relying on a trigger to update the second, you should simply call a stored procedure to INSERT/UPDATE both tables as necessary and not use triggers for this purpose at all.

[Bob Jarvis - Слава Україні]

@swiss196: what we're suggesting is that you have a procedure which you call which implements all the logic needed to update your data, including cross-table updates, etc. The number of ways this kind of multi-trigger logic can go wrong are astronomical. For example, consider how this will work in a multi-transaction environment. If you think a mutating table issue is difficult to correct, I will absolutely guarantee that keeping your triggers coordinated is going to be worse. Best of luck.