• Madeira Team

How to Audit Changes for Every Table in SQL Server

The best thing about being an SQL Server consultant is that you get called in on the most interesting cases. By “interesting” I mean difficult and by “difficult” I mean the ones that will give you a headache for the rest of the day.

So I got a call, from a client with an SAP system that generates Oracle code in a process that  creates a trigger which audits each field of a given table. The client needed the same process working on SQL Server.

For example, there is the ‘Invitations’ table:

1

If I update the ‘ResponseDateTime’ column where Id = 878, the auditing table should look like this:

2

You think it’s not that hard, you just need to run a cursor on the column name of the table and get the new and old values from the inserted and deleted tables.

But what if the client needs a dynamic solution that will be valid for all tables in the database? That will make the cursor solution impossible to implement, because you’ll need to write dynamic code (one with TableName as variable) inside dynamic code (one that the cursor is running).

To make it clearer, let’s break down process.

  1. First, we need to figure out if we need to create a new trigger or alter an existing one – that’s easy.

  1. Second, we need to find a way to understand which values are the new values and which are the old ones. Usually, if you’re familiar with the table you’re auditing, you know the Primary Key of that table, and you’ll use the Primary Key for the join operator between the inserted and deleted tables.

The value taken from the inserted table is the new value of the field, and the value taken from the deleted table is the old value.

So in an insert command you’ll have just the new value, in an update command you’ll have the new value and the old value, and in a delete command you’ll have only the old value. That means that we need to understand the Primary Key of the table we are auditing.

It is also means that the solution that I’m offering will work only if the table has a Primary Key.  If there’s no Primary Key on the table, the code will fail.

  1. Third, we need to unpivot the columns of the table. After all, each column in the variable table should be a row in the auditing table. (If you didn’t get it, look again in the attached screenshots above 🙂 )

For the client, the ultimate solution was to write a stored procedure which gets three variables: @SchemaName, @TableName, @TriggerName. The stored procedure will create dynamic code, and this code will create or alter the trigger code.

So let’s go through what we need step by step:

1. The first thing we needed was to understand what the trigger action, as I mentioned that’s easy:

IF EXISTS (SELECT t.name, s.name FROM sys.tables AS t INNER JOIN sys.schemas AS s ON t.schema_id = s.schema_id INNER JOIN sys.triggers AS tg ON tg.parent_id = t.object_id WHERE object_name(tg.parent_id) = @TableName AND tg.name = @TriggerName ) SET @TriggerAction = 'ALTER' ELSE SET @TriggerAction = 'CREATE'

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

IF EXISTS

(SELECT

t.name, s.name

FROM

sys.tables AS t

INNER JOIN

sys.schemas AS s

ON t.schema_id = s.schema_id

INNER JOIN

sys.triggers AS tg

ON tg.parent_id = t.object_id

WHERE

object_name(tg.parent_id) = @TableName AND tg.name = @TriggerName

)

SET @TriggerAction = 'ALTER'

ELSE

SET @TriggerAction = 'CREATE'

We can check if the @TriggerName already exists on the @SchemaName. If it is exists, then the @TriggerAction will get the value “alter.”  If not, the @TriggerAction will get the value “create.

2. Now, the table’s Primary Key issue… This is the tricky part.

From sys.tables we can understand the Primary Key of each table.

Run this for example:

DECLARE @SchemaName as sysname ='dbo', @TableName as sysname ='Invitations' SELECT Columns.name FROM sys.indexes AS Indexes INNER JOIN sys.index_columns AS IndexColumns ON Indexes.object_id = IndexColumns.object_id AND Indexes.index_id = IndexColumns.index_id INNER JOIN sys.columns AS Columns ON Indexes.object_id = Columns.object_id AND IndexColumns.column_id = Columns.column_id WHERE Indexes.object_id = OBJECT_ID (@SchemaName + N'.' + @TableName) AND Indexes.is_primary_key = 1;

1

2

3

4

5

6

7

8

9

10

11

12

13

14

15

16

17

18

19

20

21

22

23

DECLARE

@SchemaName as sysname ='dbo', @TableName as sysname ='Invitations'

SELECT

Columns.name

FROM

sys.indexes AS Indexes