[Webinar] Streamline your web hosting managementRegister Today

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 3804
  • Last Modified:

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
0
hoyaabanks
Asked:
hoyaabanks
  • 3
  • 3
  • 3
1 Solution
 
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
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
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

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 3
  • 3
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now