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:
If I update the ‘ResponseDateTime’ column where Id = 878, the auditing table should look like this:
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.
First, we need to figure out if we need to create a new trigger or alter an existing one – that’s easy.
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.
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
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;
The tricky part is to generate the name of the Primary Key column into our dynamic script. Since it is unknown in advance whether the primary key column is composed of one or more columns, we need to do a little manipulating on the dynamic command. The join condition should include all Primary Key columns; therefore, we will need to generate in an artificial way the word “and” after the column name of the Primary Key. Something like this:
SET @Command += N' select * FROM inserted FULL OUTER JOIN deleted ON '; SELECT @Command += N'inserted.' + Columns.name + N' = deleted.' + Columns.name + N' AND ' 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 (QUOTENAME (@SchemaName) + N'.' + QUOTENAME (@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
24
25
26
27
28
29
30
31
SET @Command +=
N' select *
FROM
inserted
FULL OUTER JOIN
deleted
ON
';
SELECT
@Command +=
N'inserted.' + Columns.name + N' = deleted.' + Columns.name + N'
AND '
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 (QUOTENAME (@SchemaName) + N'.' + QUOTENAME (@TableName))
AND
Indexes.is_primary_key = 1;
In this way, at the end of the @Command you will have ‘and’ with nothing following it. So now will start a gambling game: How many varchars will we need to cut off from the end of the @Command so the syntax will be valid? In my example, I’m cutting 7 varchars from the end of the @Command.
SET @Command = LEFT (@Command , LEN (@Command) - 7);
1
SET @Command = LEFT (@Command , LEN (@Command) - 7);
**For the sake of a better understanding I have attached the above code. If you run it, the @Command variable will be set with ‘null’ value, because the inserted and deleted tables are ‘alive’ only when the trigger is fired.
3. Now we are at the critical point: the unpivot issue. As I stated before, my original thought was to use a cursor for this. However, this means we will have dynamic code within dynamic code. So I needed to find a way to do it inside the dynamic code that the Stored Procedure runs for the creation of the trigger; in other words, do the unpivot in the insert statement of the auditing table.
Wandering around the internet, I came across the post ‘Unpivoting Data’ by Itzik Ben-Gan, which provided me my solution: you can use the ‘cross apply’ operator to get the unpivot result you’d like. Here is small example:
SELECT Field = FieldValues.FieldName, Value = isnull (CAST (FieldValues.Value AS VARCHAR(100)),'') FROM ( SELECT Id, RequestingSessionId, ReceivingMemberId, CreationDateTime, StatusId, ResponseDateTime FROM [dbo].[Invitations] WHERE Id = 169 ) AS RawData CROSS APPLY ( VALUES (N'Id', cast (Id as nvarchar(max))), (N'RequestingSessionId', cast (RequestingSessionId as nvarchar(max))), (N'ReceivingMemberId', cast (ReceivingMemberId as nvarchar(max))), (N'CreationDateTime', cast (CreationDateTime as nvarchar(max))), (N'StatusId', cast (StatusId as nvarchar(max))), (N'ResponseDateTime', cast (ResponseDateTime as nvarchar(max))) ) AS FieldValues (FieldName, Value)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
SELECT
Field = FieldValues.FieldName,
Value = isnull (CAST (FieldValues.Value AS VARCHAR(100)),'')
FROM
(
SELECT
Id, RequestingSessionId, ReceivingMemberId, CreationDateTime, StatusId, ResponseDateTime
FROM
[dbo].[Invitations]
WHERE
Id = 169
)
AS RawData
CROSS APPLY
(
VALUES
(N'Id', cast (Id as nvarchar(max))),
(N'RequestingSessionId', cast (RequestingSessionId as nvarchar(max))),
(N'ReceivingMemberId', cast (ReceivingMemberId as nvarchar(max))),
(N'CreationDateTime', cast (CreationDateTime as nvarchar(max))),
(N'StatusId', cast (StatusId as nvarchar(max))),
(N'ResponseDateTime', cast (ResponseDateTime as nvarchar(max)))
)
AS
FieldValues (FieldName, Value)
‘RawData’ is the data you want to unpivot.
‘FieldValues’ is kind of the unpivot you want to have, using a table-value constructor where you can specify column name as field name and the column values for each field. What is really cool is that the ‘FieldValues’ refers to values in the ‘RawData’, so this is how it builds the data in the right form. For more info regarding the unpivot, I recommend reading Itzik Ben-Gan’s post..
So, after a long day and a small headache, all we have left is to take the basic steps listed and generate it in the Stored Procedure code – and we are done!
In the code below you can find:
Create dbo.Invitations table script- Create table dbo.Invitations
Create dbo.Auditing table script- Create table dbo.Auditing
The Stored Procedure code- The trigger code from the procedure
The Create trigger the Stored Procedure generates- Create trigger procedure