Here is an example of what I was trying to describe as generating code from the metadata stored in SQL Server.
I once had a customer needing to audit all changes in their database. We developed a two phase implementation. First we created a generic audit queue table defined below. We created an audit trigger on any table to be audited, which wrote all changes into this table.
CREATE TABLE AuditQueue
(
AuditQueueId INT NOT NULL BIGINT
,SchemaId INT NOT NULL
,TableId INT NOT NULL
,ColumnId INT NOT NULL
,KeyValue INT NOT NULL
,Value VARCHAR(MAX) NULL
,AuditDTM DateTime NOT NULL
,Action CHAR(1) NOT NULL -- I=Insert, U=Update, D=Delete
)
A second table of changes maintained the delta of the changes for any column in any row in any table. We’re not going to get into that because it doesn’t demonstrate the process of writing code in SQL scripts.In short, write all data that may have been modified into the audit table in a trigger. A later process reviews all rows in the audit queue table, drops the rows from the Audit Queue having the same value as the last matching key in the Audit Table, and addes new records to the audit table where the value is different, Inserted, or deleted..
By using the audit queue table, the trigger performs quickly, and the comparison can be made in another process which may perform more slowly.
So, now we have an AuditQueue table; how do we populate it? I have found that using triggers is an excelent method. You could modify your application code. However, if you use a trigger, then all changes will be reflected, even if they don’t come through your code..
Following is a lengthy scrips that you can used to create trigger SQL code. It has three parameters for schema, table, and key column name. Set these variables and then execute the script. The script prints to the console the SQL code necessary to create an audit script, based on the schema, table, and key column name you supplied
Here’s a copy of the script. Following that will be an example of the trigger code it generates.
Sure, you coud have used ADO to connect to the database and written this in many other lanugages. You could even do it in a unit test. I find this easy to modify the script and get to the code quickly. You could even write a T4 template to do the same thing. Do whatever works for you.
Cheers,
Ben
SCRIPT TO GENERATE AUDIT SQL TRIGGER
-- Input Parameters
DECLARE @SchemaName SYSNAME = 'DBO'
DECLARE @TableName SYSNAME = 'Options'
DECLARE @KeyColumnName SYSNAME = 'OptionId'
-- Working Variables
DECLARE @CRLF CHAR(2) = CHAR(13) + CHAR(10)
DECLARE @SchemaId INT = (SELECT uid FROM Sys.sysusers WHERE name = @SchemaName)
DECLARE @SchemaChar VARCHAR(64) = CONVERT(VARCHAR(64), @SchemaId) + @CRLF
DECLARE @TableId INT = (SELECT id FROM sys.sysobjects WHERE name = @TableName AND uid = @SchemaId)
DECLARE @TableChar VARCHAR(64) = CONVERT(VARCHAR(64), @TableId) + @CRLF
DECLARE @Body VARCHAR(MAX) =
'CREATE TRIGGER triud_' + @SchemaName + '_' + @TableName + '_Audit' + @CRLF
+ 'ON ' + @SchemaName + '.' + @TableName + @CRLF
+ 'FOR INSERT, UPDATE, DELETE' + @CRLF
+ 'AS' + @CRLF
+ 'INSERT INTO AuditQueue' + @CRLF
+ '(' + @CRLF
+ ' SchemaId' + @CRLF
+ ' ,TableId' + @CRLF
+ ' ,ColumnId' + @CRLF
+ ' ,KeyValue' + @CRLF
+ ' ,Value' + @CRLF
+ ' ,AuditDTM' + @CRLF
+ ' ,Action' + @CRLF
+ ')' + @CRLF
PRINT @Body
DECLARE @InsertSql VARCHAR(MAX) = ''
SELECT @InsertSql +=
CASE WHEN @InsertSQL = '' THEN 'SELECT' ELSE 'UNION ALL SELECT' END + @CRLF +
' ' + @SchemaChar +
' ,' + @TableChar +
' ,' + CONVERT(VARCHAR(64), ORDINAL_POSITION) + @CRLF +
' ,i.' + @KeyColumnName + @CRLF +
' ,CONVERT(VARCHAR(MAX), i.' + COLUMN_NAME + ')' + @CRLF +
' ,GETDATE()' + @CRLF +
' ,''U''' + @CRLF +
'FROM Inserted i' + @CRLF +
'LEFT JOIN Deleted d ON I.' + @KeyColumnName + ' = d.' + @KeyColumnName + @CRLF +
'WHERE d.' + @KeyColumnName + ' IS NULL' + @CRLF + @CRLF
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND COLUMN_NAME != @KeyColumnName
ORDER BY ORDINAL_POSITION
SELECT @InsertSql +=
CASE WHEN @InsertSQL = '' THEN 'SELECT' ELSE 'UNION ALL SELECT' END + @CRLF +
' ' + @SchemaChar +
' ,' + @TableChar +
' ,' + CONVERT(VARCHAR(64), ORDINAL_POSITION) + @CRLF +
' ,d.' + @KeyColumnName + @CRLF +
' ,CONVERT(VARCHAR(MAX), d.' + COLUMN_NAME + ')' + @CRLF +
' ,GETDATE()' + @CRLF +
' ,''D''' + @CRLF +
'FROM Inserted i' + @CRLF +
'JOIN Deleted d ON I.' + @KeyColumnName + ' = d.' + @KeyColumnName + @CRLF + @CRLF
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND COLUMN_NAME != @KeyColumnName
ORDER BY ORDINAL_POSITION
SELECT @InsertSql +=
CASE WHEN @InsertSQL = '' THEN 'SELECT' ELSE 'UNION ALL SELECT' END + @CRLF +
' ' + @SchemaChar +
' ,' + @TableChar +
' ,' + CONVERT(VARCHAR(64), ORDINAL_POSITION) + @CRLF +
' ,i.' + @KeyColumnName + @CRLF +
' ,CONVERT(VARCHAR(MAX), i.' + COLUMN_NAME + ')' + @CRLF +
' ,GETDATE()' + @CRLF +
' ,''I''' + @CRLF +
'FROM Deleted d' + @CRLF +
'LEFT JOIN Inserted i ON I.' + @KeyColumnName + ' = d.' + @KeyColumnName + @CRLF +
'WHERE i.' + @KeyColumnName + ' IS NULL' + @CRLF + @CRLF
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_NAME = @TableName
AND COLUMN_NAME != @KeyColumnName
ORDER BY ORDINAL_POSITION
PRINT @InsertSql
SAMPLE TRIGGER FOR MY OPTIONS TABLE
CREATE TRIGGER triud_DBO_Options_Audit
ON DBO.Options
FOR INSERT, UPDATE, DELETE
AS
INSERT INTO AuditQueue
(
SchemaId
,TableId
,ColumnId
,KeyValue
,Value
,AuditDTM
,Action
)
SELECT
1
,245575913
,2
,i.OptionId
,CONVERT(VARCHAR(MAX), i.optionName)
,GETDATE()
,'I'
FROM Inserted i
LEFT JOIN Deleted d ON I.OptionId = d.OptionId
WHERE d.OptionId IS NULL
UNION ALL SELECT
1
,245575913
,2
,d.OptionId
,CONVERT(VARCHAR(MAX), d.optionName)
,GETDATE()
,'U'
FROM Inserted i
JOIN Deleted d ON I.OptionId = d.OptionId
UNION ALL SELECT
1
,245575913
,2
,i.OptionId
,CONVERT(VARCHAR(MAX), i.optionName)
,GETDATE()
,'D'
FROM Deleted d
LEFT JOIN Inserted i ON I.OptionId = d.OptionId
WHERE i.OptionId IS NULL