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.
精彩评论