Trigger ON Record ! - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Trigger ON Record !

Author

Topic

Lucky
Starting Member

10 Posts

Posted - 2008-08-17 : 20:20:23

HI Dears,

I am new and don't know how to audit complete row if there is any change in any field, I need to story all the rows if there is any modified in the rows,

Thanks

______________________________________________________________________________________

"We can be Knowledgeable with other mens Knowledge, but we can't be wise with other mens wisdom"

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-08-17 : 22:05:36

You can use trigger to catch changes, or just use third party sql audit tool.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 00:14:08

Something like this should work

CREATE TRIGGER CaptureAudtInfo
ON YourTable
AFTER INSERT,UPDATE<DELETE
AS

INSERT INTO AUDIT_TABLE (other fields, Action)
SELECT i.*,'Update' FROM INSERTED i
INNER JOIN DELETED d
ON d.PKCol=i.PKCol

INSERT INTO AUDIT_TABLE (other fields, Action)
SELECT i.*,'Insert' FROM INSERTED i
LEFT JOIN DELETED d
ON d.PKCol=i.PKCol
WHERE d.PKCol IS NULL


INSERT INTO AUDIT_TABLE (other fields, Action)
SELECT i.*,'Delete' FROM DELETED d 
INNER JOIN INSERTED i
ON d.PKCol=i.PKCol
WHERE i.PKCol IS NULL
GO

blindman
Master Smack Fu Yak Hacker

2365 Posts

Posted - 2008-08-19 : 11:50:37

This generates a script to create the archive tables and the triggers you will need:
http://sqlblindman.pastebin.com/fbb14def

Boycott Beijing Olympics 2008

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources