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:
Let us see one small example of logical deleting of data in SQL Server 2005:
–create table for demo
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
–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
INNER JOIN Deleted
PRINT ‘Logical delete is complete’
–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
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
Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah