CODEWITHSUNDEEP
databaseoracle-databaseplsqltriggers
Dave Clarke
Dave Clarke

Reputation: 2696

Disable a trigger from a trigger Oracle

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:

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?

Upvotes: 3

Views: 15497

Answers (2)

Michael
Michael

Reputation: 1

Those kinds of problems occurs when you have to customize an existing functionality and you just have full control on database. So you are not able to replace the inserts/updates by a procedure, you can just react. In this situation you have triggers on both tables and propagate values between the tables in both directions.

Upvotes: 0

Ben
Ben

Reputation: 1927

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.

Upvotes: 5

Related Questions