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

Friday, March 27, 2009 |

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

0 comments: