I have a problem with a trigger I had implemented as a FOR DELETE trigger.
I need to remove dependent records before executing the DELETE. This is true for the business model as well as database integrity. Due to the foreign key constraint in place to prevent orphaning the dependent records, the delete fails so the trigger which deletes the dependent records never executes. I am thinking of changing the trigger to an INSTEAD OF DELETE trigger which would include deletion of the dependent record first then the base record.
I have 3 related questions:
Will the base record be in the DELETED table throughout the duration of the trigger?
I read that unlike an AFTER trigger, the INSTEAD OF trigger can include the action that it is instead of without recursion? In other words if in my trigger I perform the same delete that it is instead of, it won't try to delete it twice.
And third, would cascading delete be preferable? If so, how would I implement this instead? I prefer to use the Enterprise Manager GUI if possible.