INSTEAD OF DELETE trigger or cascading ?

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.

Thanks,
joefunsmith

LVL 2
joefunsmithAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

LowfatspreadCommented:
1) yes until you delete it
2) correct...
3) perhaps ... if you have the cascade delete then you can't have an instead of trigger on the table
     but your instead of trigger should usually be created on views rather than tables anyway...

good luck.
namasi_navaretnamCommented:
1) Yes
2) Yes.

Delete table will have more than one record if batch delete is performed.
(Batch delete is a sql that deletes more than one row at once.)
Example:
DELETE FROM MyTable where status = 1

If there are many records with status = 1 then delete trigger will fire only once and all the deleted records will be placed into deleted table

So, when you delete join delete table with child table using primary keys

Example

DELETE ChildTable
FROM deleted d, ChildTable c
where d.primarykey = c.primarykey


3) Cascade delete Will work. You have more control if you delete child records yourself.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
joefunsmithAuthor Commented:
lowfatspread:
 
  >  1) yes until you delete it
 
  i assume that what you mean is, when, inside the trigger, I delete the record that caused the trigger to execute, that record is no longer in the DELETED table? or when trigger execution ends?
------------------------------------------------------------------
namasi_navaretnam:

  re: Multiple records in the DELETED table,  Thanks for reminding me of that.
------------------------------------------------------------------
re: cascade delete vs. instead of trigger

  good point re: control.  I think I will stick with the instead of trigger because  I could see possible adding something to it in the future such as causing an insert on an audit trail archive table.
  Just for grins, I am guessing the cascade delete is more efficient even assuming no additional functionality?  The table I am performing this on has 15,000 records, will probably double in size over the next six months, and has a composite, indexed primary key.

Thanks,
joefunsmith
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

LowfatspreadCommented:
1) sorry I'm not sure if you can remove rows from the DELETED / INSERTED Tables
    i was referring (as i thought, you where) to the base table on which the originating
    delete action was raised....
    what ever processing you initiate in the trigger and through any subsequent nested
    trigger levels the record remains until "removed"


hope that clear...
i feel tired.
;-)
namasi_navaretnamCommented:
lowfatspread,

People set them up and you know them down.

Tired..You must be feeling very enegetic with all those big points you scoring today....

:)
namasi_navaretnamCommented:
I meant to say,

People set them up and you knock them down.  
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.