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

Microsoft SQL Server Blog. Fight the fear of SQL with SQLHub.com. Founder is Ritesh Shah

Author: Riteshshah

Ritesh Shah is a database professional with having 16+ years of experience in various domain of IT field. He is a author of many technical articles on Microsoft Technology and authored a book of SQL Server 2012 Performance Tuning cookbook. He loves work with different Microsoft Products.