Tracking changes to database rows (SQL Server)

Question

Possible Duplicate:
Auditing SQL Server data changes

My requirements demand for each row inserted and updated in the database to track who made the change (creator/modifier), when the record was created and when it was modified. I have guids for row IDs in all tables, so I thought I would come up with a table rowdata

rowdata: created (datetime), modified (datetime), createdby (string or user id), modifiedby and maybe summary column (string, summary of changes)

and then put some insert/update triggers in place. Do you think is fine or is there another wa (maybe out-of-the-box one)?

My dev environment is .NET 4, so if you think of other options that might come into question, please tell.

Use SQL Server 2008 Auditing features - see duplicate question&answer — marc_s, Feb 02 '11 at 17:38

score 4 · Answer 1 · answered Feb 02 '11 at 17:38

4

Triggers are indeed the number one option for auditing tables in this way.

answered Feb 02 '11 at 17:38

Oded

489,969
99
883
1,009

score 3 · Accepted Answer · answered Feb 02 '11 at 18:00

I have similar requirements but went about it by adding these four columns to all the tables that needed the auditing tracked:

[Create_User] [nvarchar](100) NULL,
[Create_Date] [datetimeoffset](7) NULL,
[Modify_User] [nvarchar](100) NULL,
[Modify_Date] [datetimeoffset](7) NULL,

The INSERT trigger looks like:

CREATE TRIGGER [SomeSchema].[Some_Table_Insert_Create] ON [SomeSchema].[Some_Table] FOR INSERT AS 
   SET NOCOUNT ON

   IF EXISTS(SELECT * FROM INSERTED WHERE Create_User IS NOT NULL)
      BEGIN   
         UPDATE [SomeSchema].[Some_Table] SET 
            Create_Date = SYSDATETIMEOFFSET()
         FROM 
            [SomeSchema].[Some_Table]
         INNER JOIN 
            INSERTED 
         ON 
            [SomeSchema].[Some_Table].Some_Table_Id = INSERTED.Some_Table_Id
      END
   ELSE
      BEGIN
         UPDATE [SomeSchema].[Some_Table] SET 
            Create_User = SUSER_SNAME(),
            Create_Date = SYSDATETIMEOFFSET()
         FROM 
            [SomeSchema].[Some_Table]
         INNER JOIN 
            INSERTED 
         ON 
            [SomeSchema].[Some_Table].Some_Table_Id = INSERTED.Some_Table_Id
      END

And the UPDATE trigger looks like:

CREATE TRIGGER [SomeSchema].[Some_Table_Update_Modify] ON [SomeSchema].[Some_Table] FOR UPDATE AS 

   SET NOCOUNT ON

   IF NOT UPDATE (Create_User) AND NOT UPDATE (Create_Date)
      BEGIN
         IF EXISTS(SELECT * FROM INSERTED WHERE Modify_User IS NOT NULL)
             BEGIN  
                 UPDATE [SomeSchema].[Some_Table] SET 
                     Modify_Date = SYSDATETIMEOFFSET()
                 FROM 
                     [SomeSchema].[Some_Table]
                 INNER JOIN 
                     INSERTED 
                 ON 
                     [SomeSchema].[Some_Table].Some_Table_Id = INSERTED.Some_Table_Id
             END
         ELSE
             BEGIN
                 UPDATE [SomeSchema].[Some_Table] SET 
                     Modify_User = SUSER_SNAME(),
                     Modify_Date = SYSDATETIMEOFFSET() 
                 FROM 
                     [SomeSchema].[Some_Table]
                 INNER JOIN 
                     INSERTED 
                 ON 
                     [SomeSchema].[Some_Table].Some_Table_Id = INSERTED.Some_Table_Id
             END
      END

The SUSER_SNAME() function is useful for use because we are using impersonation in our app and windows authentication to connect to the DB. This may not work in your case.

Tracking changes to database rows (SQL Server)

2 Answers2