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.

wellsjAsked:
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.

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

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
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
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

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