• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 439
  • Last Modified:

Cascade Delete

I have a parent table that has a one to many child relationship with many tables.  I need a cascade delete trigger with correct syntax.  Keep in mind I have never written a trigger before. If you could use this example:

tables/fileds are: master.MID, link1.L1ID, link2.L2ID


So, if I delete a master.MID the many L!ID's and L2ID's will also be deleted.

0
wellsj
Asked:
wellsj
  • 7
  • 7
1 Solution
 
simonsabinCommented:
CREATE TRIGGER MasterDelete ON MASTER For Delete
AS

  DELETE FROM Link1
    FROM deleted d ON d.MID = Link1.L1ID
  DELETE FROM Link2
    FROM deleted d ON d.MID = Link2.L2ID
0
 
wellsjAuthor Commented:
What is the "d" after the deleted word and before the MID for?
0
 
simonsabinCommented:
it is the alias for the deleted table.

When you delete a record the record is put in a deleted table. Joining to this and deleteing those that join will do the trick
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
wellsjAuthor Commented:
I tried it an I am getting an error that reads something like -- ERROR 156: INCORRECT SYNTAX NEAR THE KEYWORD 'ON'.

Here is the trigger:

CREATE TRIGGER API_DELETE ON Well
FOR DELETE
AS
DELETE FROM FORMTOPS
 FROM deleted d ON D.API_WELLNO = FORMTOPS.API_WELLNO
DELETE FROM TBLSTIMULATION
 FROM deleted d ON D.API_WELLNO = TBLSTIMULATION.API_WELLNO
0
 
simonsabinCommented:
Sorry bastardized a bit of code

Use this
if exists (select * from sysobjects where id = object_id('dbo.API_DELETE') and sysstat & 0xf = 8)
      drop trigger dbo.API_DELETE
GO

CREATE TRIGGER API_DELETE ON Well
FOR DELETE
AS
DELETE FROM FORMTOPS
 FROM deleted d
  WHERE D.API_WELLNO = FORMTOPS.API_WELLNO

DELETE FROM TBLSTIMULATION
 FROM deleted d
   WHERE D.API_WELLNO = TBLSTIMULATION.API_WELLNO

GO

0
 
wellsjAuthor Commented:
Thaaaaaaaaank you!
0
 
wellsjAuthor Commented:
OK one more follow up.  there is a forth table in the mix. and it has a FK constraint.  How do I handle that?
Table: location
PK -- location_id (which is the same data as the api_wellno)
0
 
simonsabinCommented:
Where is the foreign key? not quite clear.

You can't have cascade deletes and referential integrity, your choice.
0
 
wellsjAuthor Commented:
the foreign key is in the location table.  the error I'm getting is:
DELETE statement conflicted with COLUMN REFERENCE constraint FK_tblLocational_Well.  ETC....
0
 
simonsabinCommented:
Ok so the FK on tblLocation relates to the one of the tables above.

To have cascade delete you will have to remove the FK, and then to maintain referential integrity you will have to add a code as above to the table that location references, to delete the records from location.
Does that make it clear.
0
 
wellsjAuthor Commented:
I hate to be a pain in the ass but, the doesnt he FK constrain maintain RI and automatically cascade delete?  And the FK on Location relates to the Well table.

Still foggy but it's clearing
0
 
simonsabinCommented:
Nope it is not like access.

You can either have foreign key constraints or cascade delete triggers you can't have both. It is a feature of Sql server
0
 
wellsjAuthor Commented:
Nice feature!  Sounds backwards to me.  So if you have FK constraint set up how do you go about deleteing relating records?  Or if you have cascade deletes in place how do you insure the integrity of the data on inserts and updates?

And lastly, how do I give you more points?  You have been a big help.
0
 
simonsabinCommented:
You can ask another question and title it for simonsabin.

If you want cascade deletes you have to add more triggers to ensure that data added and modified has valid integrity
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Cloud Class® Course: Ruby Fundamentals

This course will introduce you to Ruby, as well as teach you about classes, methods, variables, data structures, loops, enumerable methods, and finishing touches.

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