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

Advertisements

UPDATE() function with Trigger in SQL Server 2005:

UPDATE() function in Trigger will is one of the very useful function related to trigger. UPDATE() function will help you to know that which column is going to update. Let us see how will it work?

–create table1 for demo

CREATE TABLE CustInfo

(

CustId INT Identity(1,1),

Name VARCHAR(10),

Country Varchar(10),

)

GO

–insert records in above table

INSERT INTO CustInfo

SELECT ‘RITSEH’,’India’ UNION ALL

SELECT ‘RAJAN’ ,’Hindustan’

GO

–CREATE trigger to check which field

–we are updating

CREATE TRIGGER UpdateCheck ON CustInfo

AFTER UPDATE

AS

IF UPDATE(Name)

BEGIN

PRINT ‘YOU HAVE UPDATED NAME FIELD’

END

ELSE

BEGIN

PRINT ‘YOU HAVE NOT UPDATED NAME FIELD’

END

–check this out

UPDATE CustInfo SET Name =‘R.Shah’ WHERE Name=‘RAJAN’

As we are updating Name field, we will see first message as condition will fall in IF part.

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

Instead Of Trigger in SQL Server 2005

As name said “Instead Of” when you submit any transaction, “Instead of” trigger get fire first instead of your submitted DML processes further. You can define only one “Instead Of” Trigger for one table or one view.

–create table for demo

CREATE TABLE empData

(

Name varchar(10),

Age INT

)

–create INSTEAD OF trigger on empData table,

–which will show message if Age > 100

–and roll back transaction

alter TRIGGER empAgeCheck ON empData

INSTEAD OF INSERT

AS

SET NOCOUNT ON

DECLARE @Age INT

SELECT @Age=Age FROM INSERTED

IF (@Age>100)

BEGIN

print ‘you can not do this’

ROLLBACK

END

GO

–try inserting records and see message

INSERT INTO empData VALUES(‘TestName’,101)

–finally check your data in empData table.

select * from empData

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

After Trigger in Microsoft SQL Server 2005 (CREATE and ALTER)

After giving quiet a good details about Triggers in my previous article, I would like to move further and want to give introduction about After Trigger in SQL Server.

AFTER Trigger was the only trigger before Microsoft SQL Server 2000 and it is useful as well. Table can contain more than one AFTER trigger. You can use AFTER trigger for recording data audit trails, complex business rule and for complex data validation.

AFTER trigger fire after all transaction gets complete respected to DML command but before COMMIT.

–create table for demo

CREATE TABLE empData

(

Name varchar(10),

Age INT

)

–create AFTER trigger on empData table,

–which will show message if Age > 100

–but won’t stop inserting records.

CREATE TRIGGER empAgeCheck ON empData

AFTER INSERT

AS

SET NOCOUNT ON

DECLARE @Age INT

SELECT @Age=Age FROM INSERTED

IF (@Age>100)

BEGIN

print ‘you can not do this’

END

GO

–try inserting records and see message

INSERT INTO empData VALUES(‘TestName’,101)

–if you want to abort the batch

–if it break the rule than ALTER your TRIGGER

–and add rollback in it

ALTER TRIGGER empAgeCheck ON empData

AFTER INSERT

AS

SET NOCOUNT ON

DECLARE @Age INT

SELECT @Age=Age FROM INSERTED

IF (@Age>100)

BEGIN

print ‘you can not do this’

ROLLBACK

END

GO

–try inserting records and see message

INSERT INTO empData VALUES(‘TestName’,101)

–finally check your data in empData table.

select * from empData

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