Logical Delete with Trigger in SQL Server 2005

Delete seems known command for every computer users and especially to whom, who engaged with database tasks but have you ever heard concept like “Logical Delete”? Fox pro guys may know this concept very well.

It proved itself very helpful in many organizations and has kept tight data integrity. Logical delete is nothing but the marked data as deleted in database rather than physically delete it. Many developer use delete flag bit column to mark row as deleted. You can set the flag true from your front end application, can make one stored procedure which can take care of flag or else you can fire instead of trigger which will detect DELETE statement and rather than deleting the row, it just marked the data as deleted.

By this way, you will always have all the data in your database but keeping and archiving those data is one of the overhead and should be done with care.

This will help a lot when you wish to UNDO your delete command and wants your entire deleted row back. You can simply change the flag and you are done.

Logical Delete becomes very critical in case of Cascading DELETE as it is difficult to handle DELETE action in all the child table but you can brainstorm your mind can do it, not a impossible task.

To know more about cascading delete and update, do refer my past article at:

http://www.sqlhub.com/2009/03/cascading-delete-and-cascading-update.html

Let us see one small example of logical deleting of data in SQL Server 2005:

--create table for demo

use adventureworks

go

CREATE TABLE LogicalDelete

(

ID INT IDENTITY(1,1) NOT NULL,

NAME VARCHAR(15) NOT NULL,

Deleted BIT NOT NULL DEFAULT 0

)

--insert records in above table

INSERT INTO LogicalDelete(Name)VALUES('Ritesh')

INSERT INTO LogicalDelete(Name)VALUES('Rajan')

INSERT INTO LogicalDelete(Name)VALUES('Dharmesh')

--create Instead Of Trigger for logical delete

--this is just basic example of logical delete

--you can customize it with your needs

CREATE TRIGGER triLogicalDelete ON LogicalDelete

INSTEAD OF DELETE

AS

BEGIN

--finding the records going to delete from DELETED table by ID column

--JOIN it with LogicalDelete table and update its status to mark as deleted

UPDATE LogicalDelete SET Deleted=1

FROM LogicalDelete

INNER JOIN Deleted

ON LogicalDelete.ID=Deleted.ID

PRINT 'Logical delete is complete'

END

--run DELETE DML command

DELETE FROM LogicalDelete WHERE Name LIKE 'R%'

--check whether flag is changed

SELECT * FROM LogicalDelete

You can easily find deleted records with “Deleted” column but beware of using this as logical delete is two sided sword, if you don’t know how to use it than it will affect performance of your server.

Reference: Ritesh Shah
http://www.sqlhub.com
Note: Microsoft Books online is a default reference of all articles but examples and explanations prepared by Ritesh Shah, founder of http://www.SQLHub.com