Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

I have a legacy application where I have two tables X and Y. Table Y has a trigger that performs an action automatically on delete. There is also a foreign key with cascade delete defined between X and Y (such that entries in Y are deleted when X is deleted).

Is there any way to have the trigger NOT run during a cascade delete?

The only way I currently know that this could be done is to create a package level variable to use as a flag that the trigger would check to determine if it should run. I would then change the value of the flag in my stored procedure before and after running the delete on table X.

I was hoping to find something a bit cleaner where you could simply tell oracle not to execute the trigger if it is a cascade delete.

share|improve this question

1 Answer 1

up vote 2 down vote accepted

Short of disabling the trigger before executing your DELETE statement (which would affect all sessions and would only be appropriate if you are doing an off-hours bulk delete), setting a package variable (or defining your own context and setting a context variable) that your trigger reads is likely the cleanest option without reworking the existing application.

When you have an action that generally needs to be done when a row in Y is deleted but not when Y is deleted because of the cascade delete, that is probably not an action that should be in a trigger in the first place. It ought to be part of the stored procedure (or other API) that allows you do do "normal" deletes on Y. Of course, that sort of refactoring may not be possible with a legacy application so you may be stuck with some of the less elegant workarounds.

share|improve this answer
    
I kind of assumed this would be the answer. I agree the logic in the trigger should be being performed elsewhere (in this case within the application itself). Unfortunately being a legacy application I'm not certain I can remove it entirely without breaking things inadvertently. Thanks for the input. –  Michael Jul 2 '12 at 19:38

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.