Cascade Deletion - Store Procedure

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.
Who is Participating?
Victor SpiridonovConnect With a Mentor Commented:
Use delete trigger on parent table:
Create tr_del trigger on tab_parent
delete from tab_child
from deleted

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.

oscaAuthor Commented:
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.
Victor SpiridonovCommented:
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
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.

All Courses

From novice to tech pro — start learning today.