Solved

Cascade Deletion - Store Procedure

Posted on 1998-09-16
3
171 Views
Last Modified: 2010-03-19
We have tried implementing cascade deletion store procedures for referenced table(Primary key) and its referencing tables (Foreign key). But it was not successful. Since, the records in the referencing tables must be deleted first before those in the referenced table. Could someone provide some advices.
0
Comment
Question by:osca
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
spiridonov earned 50 total points
ID: 1090076
Use delete trigger on parent table:
Create tr_del trigger on tab_parent
as
delete from tab_child
from deleted
where deleted.id=tab_child.parent_id

When you delete a row from table tab_parent (primary key 'id'),
delete trigger will delete all rows from tab_child table with foreign key parent_id matching deleted data.


0
 

Author Comment

by:osca
ID: 1090077
We did try this method, but it didn't work. The error was due to the presence of foreign key in the referencing tables, therefore the 'deleted' 'tmp table' cannot be created in the first place, i.e. no deletion on the referenced table (with primary key) can be performed, until all the records in the referencing tables are removed.
0
 
LVL 7

Expert Comment

by:spiridonov
ID: 1090078
You can't have both. You either use REFERENCES constraint or do all referencial integrity enforcement by using triggers: you have to create INSERT, DELETE and UPDATE triggers instead of REFERENCSES. Or  you can implement it in your application. In this case all delete operations should be done only through your application
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

Suggested Solutions

When you hear the word proxy, you may become apprehensive. This article will help you to understand Proxy and when it is useful. Let's talk Proxy for SQL Server. (Not in terms of Internet access.) Typically, you'll run into this type of problem w…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

705 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now