Creating and ON Instead of Delete Trigger

I am trying to delete records from a child table when records from the subsection table are deleted.  I am getting errors with this, but I am not sure why.  Or what I am doing wrong.  This seems like is should be easy enough.

CREATE Trigger RemoveSubSectionChildren
ON Site
INSTEAD OF Delete
AS
BEGIN
DELETE FROM [Content]
WHERE [Content].ContentID = Deleted.ContentID
DELETE FROM SubSection
WHERE SubSectionID = Deleted.SubSectionID
END
hoyaabanksAsked:
Who is Participating?
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.

brejkCommented:
Try the following:

CREATE Trigger RemoveSubSectionChildren
ON Site
INSTEAD OF Delete
AS
BEGIN
DELETE FROM [Content]
WHERE [Content].ContentID IN (SELECT ContentID FROM Deleted WHERE ContentID IS NOT NULL)
DELETE FROM SubSection
WHERE [SubSection].SubSectionID IN (SELECT SubSectionID FROM Deleted WHERE SubSectionID IS NOT NULL)
END
0
hoyaabanksAuthor Commented:
Lets say I have a table structure like this:

GrandParent -> Parent - Child

Let's say I Create a trigger on the Parent table like the one above where all of a Parent's children are deleted when a Parent is deleted.

If I create a trigger on GrandParent to delete all parent tables when a grandparent is deleted will the trigger on teh parent table be activated and also delete all associated records from the Child table?
0
brejkCommented:
What you are trying to do is possible to achieve by creating foreign keys with CASCADE DELETE option.
0
Powerful Yet Easy-to-Use Network Monitoring

Identify excessive bandwidth utilization or unexpected application traffic with SolarWinds Bandwidth Analyzer Pack.

maradamCommented:
Hi hoyaabanks,

Yes, such cascadeing triggers will work. You only cannot run the same INSTEAD OF trigger recursively. But CASCADE DELETE is also an option if you don't have two or more references parent to GrantParent or Child to Parent. And cascade foreign keys cannot be combined with instead of triggers.
0
brejkCommented:
@hoyaabanks

Consider deleting records in all connected tables via stored procedure with transaction inside.
0
hoyaabanksAuthor Commented:
How would I setup cascade delete for these tables?  Can I do it from the table desing view from the Managment studio.  How do you specify the tables for cascading deletes?

I can't have users call a stored procedure.  Delete clauses are called from all over the place in the applicaiotn and in other stored procedures.  It would be a nightmare to locate them all and change the DELETE clause to a EXECUTE PROCEDURE.
0
maradamCommented:
Do it with TSQL

ALTER TABLE Parent ADD CONSTRAINT FK_Parent_GrantParent FOREIGN KEY (GrantParentKey) REFERENCES GrantParent(GrantParentKey) ON DELETE CASCADE

ALTER TABLE Child ADD CONSTRAINT FK_Child_Parent FOREIGN KEY (ParentKey) REFERENCES Parent(ParentKey) ON DELETE CASCADE
0
hoyaabanksAuthor Commented:
So DELETE CASCADE IS a clasuse, or it will always delete whenever the delete clause is executed?
0
maradamCommented:
DELETE CASCADE means that if parent record is deleted, all it's detail records are also deleted in the same transaction.
0

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
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 2005

From novice to tech pro — start learning today.