开发者

Tracking changes to database rows (SQL Server) [duplicate]

开发者 https://www.devze.com 2023-02-08 08:11 出处:网络
This question already has answers here: Closed 12 years ago. Possible Duplicate: Auditing SQL Server data changes
This question already has answers here: Closed 12 years ago.

Possible Duplicate:

Auditing SQL Server data changes

My requirem开发者_开发百科ents 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.


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


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.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号