[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

Cascade Deletion - Store Procedure

Posted on 1998-09-16
3
Medium Priority
?
222 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
3 Comments
 
LVL 7

Accepted Solution

by:
Victor Spiridonov earned 150 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:Victor 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

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
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…

656 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