The first question to ask in regards of database auditing is what exactly are you trying to achieve?
The reason for asking this question is that auditing can mean a lot of different things to different people.
Auditing generally implies recording and monitoring some level of change/access to a database, but it can also
imply a need to recover from changes made to the database.
In this article we are going to look at several different approaches to adding different levels
of auditing capabilities to a SQL Server database. The methods we are going to look at can be
summarised as follows:-
- Simple recording of who/when a record was created or last updated
- Providing a log over time recording who/when records were created/updated or deleted
- Recording the field values which that were changed, together with record key, table and user information. There is also the question of whether to record both the old and new values for any fields, or record only the new values
- Recording a before and after image of each row that is updated, or recording only the before image
Comment on Before and After Images of Records or Field Values
Because any current record will contain the latest after change image values for data,
the question is do you need to record the after image as part of the audit, or just the before change values
(then by looking at either subsequent changes or the current record you can work out the before image of the data).
This raises the question of how easy does it need to be when you come to trace through the changes to see
what was changed or reconstruct the old data. By recording both the before and after images of data,
you increase the required storage and processing to create the log, but reduce the complexity of post-processing
when examining the log.
If you record only changes to fields, then any reconstruction of the original record will be quite complicated,
whereas if you record an image of the entire record then post comparison will be much simpler,
and if required recovery of the data will be simpler when you have an entire image of the record.
Simple Auditing
The simplest way to achieve a basic level of auditing is to add four fields to
each table to record when and who creates a record and who and when last modifies a record.
CREATE TABLE [dbo].[Categories](
[CategoryID] [int] IDENTITY(1,1) PRIMARY KEY NOT NULL,
[CategoryName] [varchar](15) NOT NULL,
[Description] [varchar](max) NULL,
[Picture] [varbinary](max) NULL,
[TS_Categories] [timestamp] NULL,
[A_CategoriesCreatedBy] [varchar](30) NULL,
[A_CategoriesCreatedOn] [datetime] NULL,
[A_CategoriesUpdatedBy] [varchar](30) NULL,
[A_CategoriesUpdatedOn] [datetime] NULL
)
GO
ALTER TABLE [dbo].[Categories] ADD CONSTRAINT [def_CategoriesA_CategoriesCreatedBy]
DEFAULT (substring(suser_sname(),charindex('\',suser_sname())+(1),len(suser_sname())))
FOR [A_CategoriesCreatedBy]
GO
ALTER TABLE [dbo].[Categories] ADD CONSTRAINT [def_CategoriesA_CategoriesCreatedOn]
DEFAULT (getdate()) FOR [A_CategoriesCreatedOn]
GO
ALTER TABLE [dbo].[Categories] ADD CONSTRAINT [def_CategoriesA_CategoriesUpdatedBy]
DEFAULT (substring(suser_sname(),charindex('\',suser_sname())+(1),len(suser_sname())))
FOR [A_CategoriesUpdatedBy]
GO
ALTER TABLE [dbo].[Categories] ADD CONSTRAINT [def_CategoriesA_CategoriesUpdatedOn]
DEFAULT (getdate()) FOR [A_CategoriesUpdatedOn]
GO
Then we require to add a trigger which will update the record of who last changed the record:-
CREATE TRIGGER [dbo].[TR_Categories_U]
ON [dbo].[Categories]
FOR UPDATE
AS UPDATE [Categories]
SET [A_CategoriesUpdatedOn] = GetDate(),
[A_CategoriesUpdatedBy] = SUBSTRING(SUSER_SNAME(),(CHARINDEX('\',SUSER_SNAME()) + 1),LEN(SUSER_SNAME()))
FROM [Categories] s
INNER JOIN [INSERTED] i ON s.[CategoryID] = i.[CategoryID]
GO
This level of auditing is trivial to add to your database, it has a minimum impact on performance,
and provides a quick visual check on who/when a record was changed/created. It does not provide a
great deal of information, for example you will not know anything about whether records have been deleted.
However, it is quite a useful low cost feature to add to a database.
Simple Auditing Logging Changes Over Time
The next level of auditing could be to extend the first method by recording over time who
inserted/modified or deleted records. This can again be added at a very low cost both in terms of
performance and storage.
This level of auditing can yield information which is useful to information on the amount
of change that individuals make in a system, what kind of information is most frequently
changes, and to some extent when data is deleted who deleted the data (although it won’t
tell you if some design problem in the application was responsible for inadvertently deleting data,
it does provide more information in investigating what may have happened).
To do this you would create a table for recording the log of changes; this table could record the table name,
the primary key, the user, the date/time and nature of the change (D-Delete, I–Insert, U- Update). You also need
to consider how to hold composite keys, the easiest method would be to add sufficient fields to hold the
maximum composite key size in the application. There is also the question of the data types in the key fields;
probably the simplest approach would be to hold each key as a text field. In the example below we have
arbitrarily select 3 key columns all text 128 characters,
you could have a lot of different approaches to this problem.
CREATE TABLE tblAudit(
AuditID INT IDENTITY(1,1) PRIMARY KEY,
TableName VARCHAR(128),
PKColumnValue1 VARCHAR(128),
PKColumnValue2 VARCHAR(128),
PKColumnValue3 VARCHAR(128),
ChangedBy VARCHAR(30),
ChangedOn DATETIME,
ChangeType VARCHAR(1)
)
GO
ALTER TABLE [dbo].[tblAudit] ADD CONSTRAINT [def_tblAudit_ChangedBy]
DEFAULT (substring(suser_sname(),charindex('\',suser_sname())+(1),len(suser_sname())))
FOR [ChangedBy]
GO
ALTER TABLE [dbo].[tblAudit] ADD CONSTRAINT [def_tblAudit_ChangedOn]
DEFAULT (getdate()) FOR [ChangedOn]
GO
The next step is to write the Trigger code on each table to record the changes to the log table.
CREATE TRIGGER [dbo].[TR_Categories_D]
ON [dbo].[Categories]
AFTER DELETE AS
INSERT INTO tblAudit(TableName,PKColumnValue1,ChangeType)
SELECT 'Categories', CategoryID, 'D' FROM DELETED
GO
CREATE TRIGGER [dbo].[TR_Categories_I]
ON [dbo].[Categories]
AFTER INSERT AS
INSERT INTO tblAudit(TableName,PKColumnValue1,ChangeType)
SELECT 'Categories', CategoryID, 'I' FROM INSERTED
GO
CREATE TRIGGER [dbo].[TR_Categories_U]
ON [dbo].[Categories]
FOR UPDATE AS
INSERT INTO tblAudit(TableName,PKColumnValue1,ChangeType)
SELECT 'Categories', CategoryID, 'U' FROM INSERTED
GO
The following shows the results of an update, insert and delete operation:-
Auditing of entire records to a Separate Database
With this method, the idea is that every time a row is changed a before and after image of the row is
written to a separate database.
First you create a new database called in this example called NorthwindAuditing, and then we create a
mirror table for the categories data.
CREATE TABLE [dbo].[Categories](
[Action_Categories] [char](1) NULL,
[Operation_Categories] [char](1) NULL,
[CategoryID] [int] NULL,
[CategoryName] [varchar](15) NULL,
[Description] [varchar](max) NULL,
[Picture] [varbinary](max) NULL,
[TS_Categories] [varbinary](8) NULL,
[A_CategoriesCreatedBy] [varchar](30) NULL,
[A_CategoriesCreatedOn] [datetime] NULL,
[A_CategoriesUpdatedBy] [varchar](30) NULL,
[A_CategoriesUpdatedOn] [datetime] NULL
) ON [PRIMARY]
GO
This image table has two additional fields called Action_Categories and Operation_Categories, which
record details of the change being made to the data.
In the main database we create the following triggers:-
CREATE TRIGGER [dbo].[TR_Categories_D]
ON [dbo].[Categories]
AFTER DELETE AS
INSERT INTO NothwindAudit.dbo.[Categories] SELECT 'B','D',* FROM DELETED
GO
CREATE TRIGGER [dbo].[TR_Categories_I]
ON [dbo].[Categories]
AFTER INSERT AS
INSERT INTO NothwindAudit.dbo.[Categories] SELECT 'A','I',* FROM INSERTED
GO
CREATE TRIGGER [dbo].[TR_Categories_U]
ON [dbo].[Categories]
FOR UPDATE
AS UPDATE [Categories]
SET [A_CategoriesUpdatedOn] = GetDate(),
[A_CategoriesUpdatedBy] = SUBSTRING(SUSER_SNAME(),(CHARINDEX('\',SUSER_SNAME()) + 1),LEN(SUSER_SNAME()))
FROM [Categories] s
INNER JOIN [INSERTED] i ON s.[CategoryID] = i.[CategoryID]
INSERT INTO NothwindAudit.dbo.[Categories] SELECT 'B','U',* FROM DELETED
INSERT INTO NothwindAudit.dbo.[Categories] SELECT 'A','U',* FROM INSERTED
GO
The following image shows the effect of updating a record (B indicates the Before Image of the data and
A the After Image of the data):-
One advantage of this technique is that the triggers never need to be altered, but the down-side is
that you need to make sure that the image tables match the structure of the main tables. When you change a table design in
the main database you put the changes into the audit system and the main system at the same time. This method will
give very good performance; the cost being that of one or two additional inserts. It is also very simple to
view the auditing data as the entire before and after image of each row is held in the auditing tables.
Auditing Field Changes
An alternative approach the previous methods is to only record those fields which have been edited (together with the table name and primary key)
in a single table.
The code in this section has been developed and tested for SQL Server 2008.
The Update(ColumnName) function can be used for all columns except computed columns to test whether
data in the column has been updated, so you could cycle through each column inside a trigger to establish which rows
had been updated or inserted.
The second function to investigate is the COLUMNS_UPDATED(), this will return a bitmask for the ordinal set of fields in the row.
This is quite a complicated function to understand and manipulate. Your columns are treated by the Ordinal Number (positional number),
columns 1-8 are encoded in the first byte, then columns 9-16 in the second byte.
If you want to test for any changes in the first 8 fields, we use SUBSTRING(COLUMNS_UPDATED(),1,1), for the next 8 columns we would use
SUBSTRING(COLUMNS_UPDATED(),1,2), varying the third parameter 1,2... as required.
Within each block the 8 fields are ordered bitwise from left to right, and we can use a bit mask to & out the result.
For example the first block of fields can be tested using the following:-
IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 1 = 1
IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 2 = 2
Until the last field which is IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 128 = 128
Then we could test the first field in the second block using IF SUBSTRING(COLUMNS_UPDATED(),2,1) & 1 = 1
You can experiment with this function in the management studio by writing a simple trigger and displaying the results:-
CREATE TRIGGER [dbo].[TR_Categories_AU]
ON [dbo].[Categories]
AFTER UPDATE
AS
IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 1 = 1
Print 'Column 1 Updated'
IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 2 = 2
Print 'Column 2 Updated'
IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 4 = 4
Print 'Column 3 Updated'
IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 8 = 8
Print 'Column 4 Updated'
IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 16 = 16
Print 'Column 5 Updated'
IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 32 = 32
Print 'Column 6 Updated'
IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 64 = 64
Print 'Column 7 Updated'
IF SUBSTRING(COLUMNS_UPDATED(),1,1) & 128 = 128
Print 'Column 8 Updated'
IF SUBSTRING(COLUMNS_UPDATED(),2,1) & 1 = 1
Print 'Column 9 Updated'
GO
Then if we execute the following update:-
UPDATE Categories
SET CategoryName = CategoryName
GO
In our example this gives the following output:-
Column 3 Updated
Column 6 Updated
In the above we get two changes because column 6 is a timestamp (automatically updated), and column 3 contains the data we changed.
Now in our example, we actually added in another column at the beginning of the table, and then deleted the column,
to illustrate a very important point; you cannot always accept that the bitmap ordering of the fields matches what the order looks
like in the management studio. We said our column was number 3, but it looks like it is number 2!
You need to execute the following to find how the numbering matches up between the columns and the bitwise map:-
SELECT ORDINAL_POSITION, COLUMN_NAME,
COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID') AS COLUMN_ID
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Categories'
GO
Now we can see that the Ordinal_Position of 2 and 5 matches what we see when looking at the table structure,
but the column_id which indicates the order in the bitmask is 3 and 6, which matches what was returned by the COLUMNS_UPDATED() function.
This point is made in the SQL Server documentation: -
‘In SQL Server 2008, the ORDINAL_POSITION column of the INFORMATION_SCHEMA.COLUMNS view is
not compatible with the bit pattern of columns returned by COLUMNS_UPDATED. To obtain a bit pattern
compatible with COLUMNS_UPDATED, reference the ColumnID property of the COLUMNPROPERTY system function when
you query the INFORMATION_SCHEMA.COLUMNS view...’
The following code can be used to see how a calculation based around this would work.
DECLARE @OrdinalPosition INT
DECLARE @Bitmask INT
DECLARE @Bit INT
DECLARE @Byte INT
SET @OrdinalPosition = 1
WHILE @OrdinalPosition <= 50
BEGIN
SELECT @Bit = (@OrdinalPosition-1) % 8 + 1 -- gives 1,2,3,4,5,6,7,8
SELECT @Bitmask = POWER(2,@Bit - 1) -- gives 1,2,4,8,16,32,64,128
SELECT @Byte = ((@OrdinalPosition - 1) / 8) + 1 -- gives 1 for for 1-8, 2 for 9 -16 etc.
PRINT CONVERT(VARCHAR(8),@OrdinalPosition) + Char(9) + CONVERT(VARCHAR(8),@Byte) +
Char(9) + CONVERT(VARCHAR(8),@Bit) + Char(9) + CONVERT(VARCHAR(8),@BitMask)
SET @OrdinalPosition = @OrdinalPosition + 1
END
This could then be used with an expression like the following inside a trigger (more complete code to follow):-
IF SUBSTRING(COLUMNS_UPDATED(),@Byte,1) & @Bitmask = @Bitmask -- or > 0
Print 'Column ' + CONVERT(VARCHAR(8),@OrdinalPosition) + ' Updated'
We are now ready to create our auditing table to record this information, in this example we have decided on a maximum of
3 parts to a primary key, but this is easily extended by adding more fields. We have also choosen to hold the data
in a VARCHAR(MAX) data type field:-
CREATE TABLE tblAudit(
AuditID INT IDENTITY(1,1) PRIMARY KEY,
SchemaName VARCHAR(128),
TableName VARCHAR(128),
PKColumnValue1 VARCHAR(128),
PKColumnValue2 VARCHAR(128),
PKColumnValue3 VARCHAR(128),
FieldName VARCHAR(128),
FieldValue VARCHAR(MAX),
ImageType VARCHAR(1), -- 'B' (before),'A' (after)
ActionType VARCHAR(1), -- 'U' (update), 'D' (delete), 'I' (insert)
ChangedBy VARCHAR(30),
ChangedOn DATETIME
)
GO
Then we can use the following trigger for auditing:-
CREATE TRIGGER [dbo].[TR_Categories_AU]
ON [dbo].[Categories]
FOR UPDATE
AS
DECLARE @IDPosition INT -- equivalent to the ordinal position
DECLARE @Bitmask INT -- bit mask for comparing value
DECLARE @Bit INT -- bit position in the byte
DECLARE @Byte INT -- byte number
DECLARE @MaxIDPosition INT -- maximum field number
DECLARE @FieldName VARCHAR(128) -- name of changed field
DECLARE @InsertCommand VARCHAR(1024) -- command for inserting in audit
DECLARE @ChangedBy VARCHAR(128)
DECLARE @ChangedOn VARCHAR(20)
SET @ChangedBy = substring(suser_sname(),charindex(''\'',suser_sname())+(1),len(suser_sname()))
SET @ChangedOn = CONVERT(VARCHAR(20),GetDate()) -- tag all the changes consistently
-- Table holding field names and ColumnID
DECLARE @Fields TABLE(ColumnID INT, ColumnName VARCHAR(128))
INSERT INTO @Fields(ColumnID,ColumnName)
SELECT COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + '.' + TABLE_NAME),
COLUMN_NAME, 'ColumnID') As ColumnID, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = 'Categories' -- this needs changed for the desired table
SELECT @MaxIDPosition = MAX(ColumnID) FROM @Fields
-- Because we can not execute dynamic sql against INSERTED and DELETED
-- we need a copy of these tables
SELECT * INTO #Deleted FROM deleted
SELECT * INTO #Inserted FROM inserted
SET @IDPosition = 1
WHILE @IDPosition <= @MaxIDPosition
BEGIN
SELECT @Bit = (@IDPosition-1) % 8 + 1 -- gives 1,2,3,4,5,6,7,8
SELECT @Bitmask = POWER(2,@Bit - 1) -- gives 1,2,4,8,16,32,64,128
SELECT @Byte = ((@IDPosition - 1) / 8) + 1 -- gives 1 for for 1-8, 2 for 9 -16 etc.
IF SUBSTRING(COLUMNS_UPDATED(),@Byte,1) & @Bitmask > 0
BEGIN
-- column has changed
-- Get the fieldname
SELECT @Fieldname = ColumnName FROM @Fields WHERE ColumnID = @IDPosition
-- Post the before and after column values
-- This needs changed for the desired table and primary keys
-- you can easily add multiple keys here
-- Our timestamp columns are all named TS_ and we want to exclude them
IF LEFT(@FieldName,3) <> 'TS_'
BEGIN
SET @InsertCommand = 'INSERT INTO tblAudit(SchemaName,TableName, PKColumnValue1, FieldName, FieldValue,
ImageType, ActionType, ChangedBy, ChangedOn)
SELECT ''dbo'', ''Categories'', CategoryID, ''' + @Fieldname + ''', CONVERT(VARCHAR(MAX),' + @FieldName + '),
''B'', ''U'',''' + @ChangedBy + ''',''' + @ChangedOn + ''' FROM #deleted'
EXEC (@InsertCommand)
SET @InsertCommand = 'INSERT INTO tblAudit(SchemaName,TableName, PKColumnValue1, FieldName, FieldValue,
ImageType, ActionType, ChangedBy, ChangedOn)
SELECT ''dbo'',''Categories'', CategoryID, ''' + @Fieldname + ''', CONVERT(VARCHAR(MAX),' + @FieldName + '),
''A'', ''U'',''' + @ChangedBy + ''',''' + @ChangedOn + ''' FROM #inserted'
EXEC (@InsertCommand)
END
END
SET @IDPosition = @IDPosition + 1
END
GO
This method unfortunately has to take a snapshot of the DELETED and INSERTED virtual tables,
because you are not allowed inside the trigger to execute dynamic sql which refers to the actual INSERTED and DELETED tables.
We also need to have the table name and the primary key hard-coded into the trigger (the primary key could be worked out, but there would
be a cost to doing that); later we will see how to avoid that problem by dynamically writing the trigger code.
A nice feature of this approach is you only need to do this once unless you intend to change the primary keys on the table.
The TSQL is also not too complicated so that adding multiple part keys or making other changes is not difficult.
Shown below is an example of the resulting audit log:-
Automating Field Auditing Level Trigger Code
Revised 13th November 2011, code modified to allow for spaces in both field names, and primary key field names.
With a little bit of generalisation we can write a process which will write
our trigger code, and use a similar process to record when rows are inserted and deleted, code for this is included
in the download at the end of this article for all three insert, update and delete triggers. You may consider
not using the Insert triggers, because these records will be held in the actual tables.
The example below will generate a set of triggers for the UPDATE trigger.
-- ************************************************************************
-- Examples of automatically generating the auditing triggers for UPDATE
-- ************************************************************************
DECLARE @SchemaName VARCHAR(128) -- schema name
DECLARE @TableName VARCHAR(128) -- table name
DECLARE @KeyName VARCHAR(128) -- primary key part
DECLARE @KeyCount INT -- columns in the primary key
DECLARE @cmd VARCHAR(8000) -- cmd string to execute
DECLARE @KeyListInsert VARCHAR(1024) -- list of keys to audit values to
DECLARE @KeyList VARCHAR(1024) -- list of keys to be used
DECLARE @MaxKeys INT -- Maximum number of parts to a key
DECLARE @TriggerName VARCHAR(128) -- Name for the trigger
SET @MaxKeys = 3 -- change for different maximum number of parts in a key
DECLARE Table_Cursor CURSOR
FOR
SELECT TABLE_SCHEMA,TABLE_NAME
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_TYPE = 'BASE TABLE' and TABLE_NAME <> 'tblAudit'
OPEN Table_Cursor
FETCH NEXT FROM Table_Cursor INTO @SchemaName , @TableName
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE Key_Cursor CURSOR
FOR
SELECT u.COLUMN_NAME
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE u
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS c
ON u.TABLE_NAME = c.TABLE_NAME
AND u.TABLE_SCHEMA = c.TABLE_SCHEMA
AND u.CONSTRAINT_NAME = c.CONSTRAINT_NAME
WHERE c.TABLE_SCHEMA = @SchemaName and c.TABLE_NAME = @TableName
AND c.CONSTRAINT_TYPE = 'PRIMARY KEY'
SET @KeyCount = 0
-- PKColumnValue1, PKColumnValue2, PKColumnValue3
SET @KeyListInsert = ''
SET @KeyList = ''
OPEN Key_Cursor
FETCH NEXT FROM Key_Cursor INTO @KeyName
WHILE @@FETCH_STATUS = 0
BEGIN
SET @KeyCount = @KeyCount + 1
IF @KeyCount > @MaxKeys
BEGIN
PRINT @SchemaName + ' ' + @Tablename + ' number of keys exceeded ' + CONVERT(VARCHAR(8),@KeyCount)
CLOSE Key_Cursor
DEALLOCATE Key_Cursor
GOTO SkipTheTable
END
IF @KeyCount > 1
BEGIN
SET @KeyListInsert = @KeyListInsert + ','
SET @KeyList = @KeyList + ','
END
SET @KeyListInsert = @KeyListInsert + 'PKColumnValue' + CONVERT(CHAR(1),@KeyCount)
SET @KeyList= @KeyList + '[' + @KeyName +']'
FETCH NEXT FROM Key_Cursor INTO @KeyName
END
CLOSE Key_Cursor
DEALLOCATE Key_Cursor
SET @TriggerName = '[' +@SchemaName + '].[TR_' +Replace(@TableName,' ','') + '_AU]'
IF EXISTS(SELECT * FROM sysobjects WHERE id = OBJECT_ID(N''+@TriggerName +''))
BEGIN
SET @cmd = 'DROP TRIGGER ' +@TriggerName
EXEC (@cmd)
END
-- Now create the trigger body
SET @cmd = 'CREATE TRIGGER ' + @TriggerName + '
ON [' + @SchemaName + '].[' + @TableName + ']
FOR UPDATE
AS
DECLARE @IDPosition INT -- equivalent to the ordinal position
DECLARE @Bitmask INT -- bit mask for comparing value
DECLARE @Bit INT -- bit position in the byte
DECLARE @Byte INT -- byte number
DECLARE @MaxIDPosition INT -- maximum field number
DECLARE @FieldName VARCHAR(128) -- name of changed field
DECLARE @InsertCommand VARCHAR(1024) -- command for inserting in audit
DECLARE @ChangedBy VARCHAR(128)
DECLARE @ChangedOn VARCHAR(20)
SET @ChangedBy = substring(suser_sname(),charindex(''\'',suser_sname())+(1),len(suser_sname()))
SET @ChangedOn = CONVERT(VARCHAR(20),GetDate()) -- tag all the changes consistently
-- Table holding field names and ColumnID
DECLARE @Fields TABLE(ColumnID INT, ColumnName VARCHAR(128))
INSERT INTO @Fields(ColumnID,ColumnName)
SELECT COLUMNPROPERTY(OBJECT_ID(TABLE_SCHEMA + ''.'' + TABLE_NAME),
COLUMN_NAME, ''ColumnID'') As ColumnID, COLUMN_NAME
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = '''+ @TableName +''' AND TABLE_SCHEMA = ''' + @SchemaName + '''
SELECT @MaxIDPosition = MAX(ColumnID) FROM @Fields
-- Because we can not execute dynamic sql against INSERTED and DELETED
-- we need a copy of these tables
SELECT * INTO #Deleted FROM deleted
SELECT * INTO #Inserted FROM inserted
SET @IDPosition = 1
WHILE @IDPosition <= @MaxIDPosition
BEGIN
SELECT @Bit = (@IDPosition-1) % 8 + 1 -- gives 1,2,3,4,5,6,7,8
SELECT @Bitmask = POWER(2,@Bit - 1) -- gives 1,2,4,8,16,32,64,128
SELECT @Byte = ((@IDPosition - 1) / 8) + 1 -- gives 1 for for 1-8, 2 for 9 -16 etc.
IF SUBSTRING(COLUMNS_UPDATED(),@Byte,1) & @Bitmask > 0
BEGIN
-- column has changed
-- Get the fieldname
SELECT @Fieldname = ColumnName FROM @Fields WHERE ColumnID = @IDPosition
-- Post the before and after column values
-- Our timestamp columns are all named TS_ and we want to exclude them
IF LEFT(@FieldName,3) <> ''TS_''
BEGIN
SET @InsertCommand = ''INSERT INTO tblAudit(SchemaName, TableName,' + @KeyListInsert + ',
FieldName, FieldValue, ImageType, ActionType, ChangedBy, ChangedOn)
SELECT ''''' + @SchemaName +''''', ''''' + @TableName +''''',' + @KeyList + ', '''''' +
@Fieldname + '''''', CONVERT(VARCHAR(MAX),['' + @FieldName + '']), ''''B'''', ''''U'''', '''''' +
@ChangedBy + '''''', '''''' + @ChangedOn + '''''' FROM #deleted ''
EXEC (@InsertCommand)
SET @InsertCommand = ''INSERT INTO tblAudit(SchemaName, TableName,' + @KeyListInsert + ',
FieldName, FieldValue, ImageType, ActionType, ChangedBy, ChangedOn)
SELECT ''''' + @SchemaName + ''''', ''''' + @TableName +''''',' + @KeyList + ', '''''' +
@Fieldname + '''''', CONVERT(VARCHAR(MAX),['' + @FieldName + '']), ''''A'''', ''''U'''', '''''' +
@ChangedBy+ '''''', '''''' + @ChangedOn + '''''' FROM #inserted ''
EXEC (@InsertCommand)
END
END
SET @IDPosition = @IDPosition + 1
END'
EXEC (@cmd)
-- useful if debugging comment out the exec
--print @cmd
--goto endall
SkipTheTable:
FETCH NEXT FROM Table_Cursor INTO @SchemaName , @TableName
END
endall:
CLOSE Table_Cursor
DEALLOCATE Table_Cursor
GO
Download Code
The sample TSQL
can be downloaded from here.
Other good resources for alternative strategies are as follows:-
SQL Server Built-In Auditing
If you want to learn more about the built in auditing capabilities of SQL Server, then follow this link http://msdn.microsoft.com/en-us/library/dd392015(v=sql.100).aspx
MUST+SQL allows you to upsize into multiple schemas splitting up an application into manageable chunks or
bringing together multiple databases and simplifying security. Click below to improve your
development productivity.
Top of Page