Cascading Deletes in SQL Server 7.0

Results 1 to 3 of 3

Thread: Cascading Deletes in SQL Server 7.0

  1. #1
    Steve Blum Guest

    Default Cascading Deletes in SQL Server 7.0

    I read the question and answer written by Owen Cutajar regarding using a delete trigger to perform a cascading delete. I tried out his approach and received an error because of the foreign key constraint between the parent and child tables. From reading the SQL Server BOL, it looks like using a trigger to do a cascade delete will only work if you DO NOT use a foreign key constraint! Can anyone confirm or deny this? I find it hard to believe a database that is being targetted at large scale business applications would have a requirement that I either can do cascade deletes or I can have foreign key constraints, but not both!<BR><BR>In Oracle, of course, there are multiple options for dealing with this. There is an explicit Cascade Delete option. My last experience with Oracle was in 7.3, and at that time I think Cascade Delete was on or off for a whole database, but I think they may have moved this to the table level in version 8.x. Also, in Oracle, you can specify whether a delete trigger is to run before or after a delete, while in SQL Server it can only run after a delete. Writting a trigger to run before the delete gives the ability to delete the child records before the parent record is deleted.<BR><BR>Any observations or suggestions on how to proceed in SQL Server would be welcome.

  2. #2
    Ben Timby Guest

    Default RE: Cascading Deletes in SQL Server 7.0

    Of course you can use both table constraints and update triggers, simply drop the constraints at the beginning of your procedure, delete the related records, and reinstate the constraints!

  3. #3
    Join Date
    Dec 1969

    Default Unfortunately, that doesn't work

    When I use a Delete trigger on a table with foreign key constraints, the Delete statement fails before the trigger ever runs. SQL-Server only allows a Delete trigger to run after a delete has completed, unlike Oracle, which lets you specify Before Delete or After Delete.<BR><BR>I&#039ve been doing more reading and talking to a DBA, and both confirm that in SQL-Server, you can use triggers for Cascade Deletes OR you can have constraints, but not both.<BR><BR>One other alternative pointed out by the DBA is to write a delete stored procedure and in there do the delete on the child rows and then the delete on the parent row.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts