Reply
Logical Delete Cascade algorithm
Old 03-05-2010, 08:53 AM Logical Delete Cascade algorithm
voxink's Avatar
Novice Talker

Posts: 13
Name: Adrian
Trades: 0
Hello,

I have some problems about creating a logical deletion(including cascade) script for a SQL Server database.

The logical deletion operation is simple for tables that are not connected by FK with other tables.

e.g
UPDATE MyTable SET IsDeleted = True WHERE Id = 1

instead of
DELETE FROM MyTable WHERE Id = 1


But, when it comes to tables connected by FK, it is a little difficult to implement this.

Did someone had this problem?

Thanks
voxink is offline
Reply With Quote
View Public Profile
 
 
When You Register, These Ads Go Away!
Old 03-05-2010, 01:45 PM Re: Logical Delete Cascade algorithm
tripy's Avatar
Do not try this at home!

Posts: 3,600
Name: Thierry
Location: I'm the uber Spaminator !
Trades: 0
Modify your selects to implement this.
A simple
Code:
WHERE tblParent.IsDeleted IS FALSE
will be enough.

Or, if you want to avoid that, implement an ON DELETE trigger that will check this condition and drop the rows from the child table.
__________________
Only a biker knows why a dog sticks his head out the window.
tripy is offline
Reply With Quote
View Public Profile Visit tripy's homepage!
 
Old 03-07-2010, 01:46 PM Re: Logical Delete Cascade algorithm
voxink's Avatar
Novice Talker

Posts: 13
Name: Adrian
Trades: 0
I think that the trigger is the best solution.

The select works too, but it is time consuming.

Thanks
voxink is offline
Reply With Quote
View Public Profile
 
Old 03-07-2010, 01:58 PM Re: Logical Delete Cascade algorithm
tripy's Avatar
Do not try this at home!

Posts: 3,600
Name: Thierry
Location: I'm the uber Spaminator !
Trades: 0
What I don't like about trigger, is that they work a bit like by magic.
When you have a lot of them, and you forget about them, you can spend 1 day trying to find why the dependent rows are deleted...

But they sure are handy...
__________________
Only a biker knows why a dog sticks his head out the window.
tripy is offline
Reply With Quote
View Public Profile Visit tripy's homepage!
 
Old 03-07-2010, 02:33 PM Re: Logical Delete Cascade algorithm
voxink's Avatar
Novice Talker

Posts: 13
Name: Adrian
Trades: 0
Well, it might be better to use a recursive stored procedure, something like that:

Code:
Procedure  LogicalDelete(TableName, RecordId)
Begin
        FOR (Get all connected tables)
              LogicalDelete(ChildTableName, ChildRecordId)
        END FOR;
        UPDATE TableName SET IsDeleted = True;
End
I agree, triggers are a little bit tricky when it comes to debug.

Thanks
voxink is offline
Reply With Quote
View Public Profile
 
Reply     « Reply to Logical Delete Cascade algorithm
 

Thread Tools Search this Thread
Search this Thread:

Advanced Search

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are Off
Pingbacks are Off
Refbacks are Off





   
RSS Feed  Feeds: RSS   JS   XML
RSS Feed  Feeds for this forum: RSS   JS   XML

 


Page generated in 0.10769 seconds with 13 queries