This is an optional feature, where for each individual database and then each individual table settings are provided to control whether auditing fields are added
to the tables to track user edits, this is an optional feature.
This level of auditing records the user who last edited a record, and when the record was edited.
These fields can then be displayed in your application to assist with auditing.
Example Showing New Fields
When this option is enabled, four new fields get added to each table, these fields are all prefixed with A_TableNameFieldname.
An example is show below for the categories table in northwind.
CREATE TABLE [dbo].[Categories] (
[CategoryID] INT NOT NULL IDENTITY(1,1) ,
[CategoryName] VARCHAR(15) NOT NULL ,
[Description] VARCHAR(MAX) NULL,
[Picture] IMAGE NULL,
[TSCategories] TIMESTAMP NULL,
[A_CategoriesCreatedBy] VARCHAR(30) NULL CONSTRAINT [defCategoriesA_CategoriesCreatedBy]
DEFAULT SUSER_SNAME(),
[A_CategoriesCreatedOn] DATETIME NULL CONSTRAINT [defCategoriesA_CategoriesCreatedOn]
DEFAULT GetDate(),
[A_CategoriesLastUpdatedBy] VARCHAR(30) NULL CONSTRAINT [defCategoriesA_CategoriesLastUpdatedBy]
DEFAULT SUSER_SNAME(),
[A_CategoriesLastUpdatedOn] DATETIME NULL CONSTRAINT [defCategoriesA_CategoriesLastUpdatedOn]
DEFAULT GetDate() ,
CONSTRAINT [pkCategories] PRIMARY KEY CLUSTERED ( [CategoryID])
)
|
Example Showing Trigger Code
Post migration triggers are created to stamp the records when users make changes to the data.
CREATE TRIGGER TRAuditCategories ON [Categories]
FOR UPDATE AS
UPDATE [Categories]
SET [A_CategoriesLastUpdatedOn] = GetDate(), [A_CategoriesLastUpdatedBy] = SUSER_SNAME()
FROM [Categories] s
INNER JOIN [INSERTED] i
ON s.[CategoryID] = i.[CategoryID]
AND s.[TS_Categories] = i.[TS_Categories]
|