Keep log of Update in same table with Instead Of Trigger in SQL Server 2005

I wrote few articles on audit trails and would prefer to keep log in separate table even I see many times how to keep update track records in same table so I thought to create one example and share it with my readers.

CREATE TABLE STATSofTable

(

Status INT NOT NULL,

LOGS VARCHAR(MAX)

)

–insert into stats table

INSERT INTO STATSofTable (status,LOGS)

SELECT 1,‘TEST’ UNION ALL

SELECT 2,‘TEST’ UNION ALL

SELECT 3,‘TEST’

–CREATE TRIGGER TO UPDATE LOG AUTOMATICALLY

CREATE TRIGGER AuditTrailOnSTATSofTable ON STATSofTable

INSTEAD OF UPDATE

AS

SET NOCOUNT ON

BEGIN

IF UPDATE(Status)

BEGIN

DECLARE @OldValue VARCHAR(1)

DECLARE @NewValue VARCHAR(1)

DECLARE @OldLog VARCHAR(max)

DECLARE @NewLog VARCHAR(max)

SELECT @OldValue = Status FROM Deleted

SELECT @NewValue = Status FROM INSERTED

SELECT @OldLog= LOGS from Deleted

SET @NewLog=@OldLog + ‘ STATUS CHANGE FROM ‘ + @OldValue + ‘ TO ‘+ @NewValue + ‘ ON ‘ + CONVERT(VARCHAR(50), getdate())

UPDATE STATS SET Status=@NewValue, LOGS=@NewLog WHERE Status=@OldValue

print @oldvalue

print @newvalue

print @OldLog

END

END

–UPDATE STATUS

update STATSofTable set status=4 where status =1

update STATSofTable set status=1 where status =4

select * from STATSofTable

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

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

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