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:
Post a Comment