Samantha J T Star
Samantha J T Star

Reputation: 32788

Is there a way I can turn DELETE CASCADE OFF and then ON again during a transaction?

I have four tables: exam > objectives and > objective > objective topics details. I am not using DELETE CASCADE as during normal operation I don't want it to be possible to delete exams if there are objectives etc.

I created this SQL to do the DELETE in a SQL Server 2012 stored procedure.

BEGIN
   DELETE ot
   FROM ObjectiveTopic ot
   INNER JOIN ObjectiveDetail od
    ON ot.ObjectiveDetailId = od.ObjectiveDetailId
   INNER JOIN Objective o
    ON od.ObjectiveId = o.ObjectiveId
   INNER JOIN Exam e
    ON o.ExamId = e.ExamId
   WHERE e.SubjectId = @SubjectId

   DELETE od
   FROM ObjectiveDetail od
   INNER JOIN Objective o
    ON od.ObjectiveId=o.ObjectiveId
   INNER JOIN Exam e
    On o.ExamId = e.ExamId
   Where e.SubjectId = @SubjectId;

   DELETE o
   FROM Objective o
   INNER JOIN Exam e
    ON o.ExamId = e.ExamId
   WHERE SubjectId = @SubjectId

   RETURN 0;
  END

This does work but rather than three deletes. Is there a way I could turn on DELETE CASCADE during a transaction, delete all objectives for a particular @SubjectId and then turn DELETE CASCADE off again ? If not is there some other way I can simplify this code or do I always have to do three deletes?

Upvotes: 4

Views: 239

Answers (1)

user3300750
user3300750

Reputation: 36

Cascade delete is a performance improvement. Although it does the same thing as the deletion process from the lowest child, it gives added performance gains (execution plans can be checked).

So, the best way is to make delete cascade on but for that we'll have to drop the existing foreign key create a new one with the ON DELETE NO ACTION setting enabled. Perform your actions and then go through the same cycle and make ON DELETE CASCADE off again.

Considering these hastles, the option we have at hand is to check the architecture of the underlying tables and the code against them to ensure that switching on on delete cascade wont affect any data and then permanently switching it on.

so, it wouldnt be a feasible technique to perform the actions in one transaction.

I hope this helps. I cant comment to the post due to lack of points :) :). Sorry :)

Upvotes: 1

Related Questions