How would one dynamically create an audit table, preserving data types from the source table, and create an Insert,Update,Delete trigger for the audit table?
One way would be to use the following code. This should dynamically duplicate a table, preserving the data types, and create an INSERT,UPDATE,DELETE trigger.
This trigger will insert 2 records on an UPDATE, an ‘Update – inserted’ record, and an ‘Update – deleted’ record. (This was for a business rule.)
You would need to change ‘AUDIT_1’ to change your table prefix to whatever you want. Thanks to all for the help on this question.
DECLARE @TableName SYSNAME
DECLARE @SchemaName SYSNAME
DECLARE @SQL NVARCHAR(MAX) = 'CREATE TABLE ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('TABLE_PREFIX' + SUBSTRING(@TableName, 3, LEN(@TableName)-2)) + ' (
[AuditId] INT IDENTITY(1,1) NOT NULL,
[AuditAction] VARCHAR(50) NOT NULL,
[AuditDateTime] DATETIME NOT NULL,
' +
STUFF((
SELECT ',' + '[' + c.name + '] ' +
CASE
WHEN c.system_type_id IN (167, 175, 231, 239) AND c.max_length = -1 THEN 'VARCHAR(MAX)'
WHEN c.system_type_id IN (167, 175, 231, 239) THEN 'VARCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
WHEN c.system_type_id IN (165, 173, 231, 239) AND c.max_length = -1 THEN 'NVARCHAR(MAX)'
WHEN c.system_type_id IN (165, 173, 231, 239) THEN 'NVARCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length / 2 END AS VARCHAR(5)) + ')'
WHEN c.system_type_id IN (40) THEN 'CHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
WHEN c.system_type_id IN (41) THEN 'NCHAR(' + CAST(CASE WHEN c.max_length < 1 THEN 1 ELSE c.max_length END AS VARCHAR(5)) + ')'
WHEN c.system_type_id IN (48, 52, 56) THEN 'INT'
WHEN c.system_type_id IN (127) THEN 'BIGINT'
WHEN c.system_type_id IN (59, 60, 62) THEN 'SMALLINT'
WHEN c.system_type_id IN (106) THEN CONCAT('DECIMAL(', [precision], ',', scale, ')')
WHEN c.system_type_id IN (108) THEN CONCAT('NUMERIC(', [precision], ',', scale, ')')
WHEN c.system_type_id = 104 THEN 'BIT' -- Changed from 'TINYINT' to 'BIT'
ELSE TYPE_NAME(c.system_type_id)
END +
CASE WHEN c.is_nullable = 1 THEN ' NULL' ELSE ' NOT NULL' END
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
ORDER BY c.column_id
FOR XML PATH(''), TYPE
).value('.[1]','nvarchar(max)'), 1, 1, '')
+ ')'
EXEC(@SQL)
/* Create Trigger */
SET @SQL = 'CREATE TRIGGER [tg_' + @TableName + '_AUD1' +'] ON ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME(@TableName) +'
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
SET NOCOUNT ON;
IF EXISTS (SELECT * FROM INSERTED)
BEGIN
IF EXISTS (SELECT * FROM DELETED)
BEGIN --UPDATE
INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('TABLE_PREFIX' + SUBSTRING(@TableName, 3, LEN(@TableName)-2))+ ' (
AuditAction,
AuditDateTime,
' + STUFF((
SELECT ',' + QUOTENAME(c.name)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
FOR XML PATH(''), TYPE
).value('.[1]','nvarchar(max)'), 1, 1, '') + '
)
SELECT
''UPDATE - Inserted'',
SYSDATETIME(),
' + STUFF((
SELECT ',' + CASE WHEN c.name IN ('RowGuid', 'ModifiedDate') THEN 'DEFAULT' ELSE 'i.' + QUOTENAME(c.name) END
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
FOR XML PATH(''), TYPE
).value('.[1]','nvarchar(max)'), 1, 1, '') + '
FROM inserted i;
INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('TABLE_PREFIX' + SUBSTRING(@TableName, 3, LEN(@TableName)-2))+ ' (
AuditAction,
AuditDateTime,
' + STUFF((
SELECT ',' + QUOTENAME(c.name)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
FOR XML PATH(''), TYPE
).value('.[1]','nvarchar(max)'), 1, 1, '') + '
)
SELECT
''UPDATE - Deleted'',
SYSDATETIME(),
' + STUFF((
SELECT ',' + CASE WHEN c.name IN ('RowGuid', 'ModifiedDate') THEN 'DEFAULT' ELSE 'd.' + QUOTENAME(c.name) END
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
FOR XML PATH(''), TYPE
).value('.[1]','nvarchar(max)'), 1, 1, '') + '
FROM deleted d;
END;
ELSE
BEGIN --INSERT
INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('TABLE_PREFIX' + SUBSTRING(@TableName, 3, LEN(@TableName)-2)) + ' (
AuditAction,
AuditDateTime,
' + STUFF((
SELECT ',' + QUOTENAME(c.name)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
FOR XML PATH(''), TYPE
).value('.[1]','nvarchar(max)'), 1, 1, '') + '
)
SELECT
''INSERT'',
SYSDATETIME(),
' + STUFF((
SELECT ',' + CASE WHEN c.name IN ('RowGuid', 'ModifiedDate') THEN 'DEFAULT' ELSE 'i.' + QUOTENAME(c.name) END
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
FOR XML PATH(''), TYPE
).value('.[1]','nvarchar(max)'), 1, 1, '') + '
FROM inserted i;
END;
END;
ELSE
BEGIN --DELETE
INSERT INTO ' + QUOTENAME(@SchemaName) + '.' + QUOTENAME('TABLE_PREFIX' + SUBSTRING(@TableName, 3, LEN(@TableName)-2))+ ' (
AuditAction,
AuditDateTime,
' + STUFF((
SELECT ',' + QUOTENAME(c.name)
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
FOR XML PATH(''), TYPE
).value('.[1]','nvarchar(max)'), 1, 1, '') + '
)
SELECT
''DELETE'',
SYSDATETIME(),
' + STUFF((
SELECT ',' + CASE WHEN c.name IN ('RowGuid', 'ModifiedDate') THEN 'DEFAULT' ELSE 'd.' + QUOTENAME(c.name) END
FROM sys.columns c
WHERE c.object_id = OBJECT_ID(@SchemaName + '.' + @TableName)
FOR XML PATH(''), TYPE
).value('.[1]','nvarchar(max)'), 1, 1, '') + '
FROM deleted d;
END;
END'
EXEC(@SQL)
Also, if you need to quickly drop the triggers an an audit table, you can use the following code to get you there quickly. This code looks for ‘AUDIT_1’ in the name.
/* CODE TO QUICKLY DROP ALL TRIGGERS IN SCHEMA */
DROP TABLE IF EXISTS #t1;
SELECT sch.name AS [Schema]
, t.name AS [Table]
, tr.name AS [Trigger]
, tr.is_disabled
, CASE tr.type WHEN 'TR' THEN 'SQL Trigger'
WHEN 'TA' THEN 'CLR Trigger' END AS [Trigger Type]
, tr.create_date
, tr.modify_date
, CASE WHEN tr.is_instead_of_trigger = 1 THEN 'INSTEAD OF' ELSE 'AFTER' END AS [Trigger Timing]
, CASE WHEN OBJECTPROPERTY(tr.object_id, 'ExecIsInsertTrigger') = 1 THEN 'INSERT,' ELSE '' END
+ CASE WHEN OBJECTPROPERTY(tr.object_id, 'ExecIsUpdateTrigger') = 1 THEN 'UPDATE,' ELSE '' END
+ CASE WHEN OBJECTPROPERTY(tr.object_id, 'ExecIsDeleteTrigger') = 1 THEN 'DELETE' ELSE '' END AS [Trigger Events]
INTO #t1
FROM sys.triggers AS tr
INNER JOIN sys.tables AS t ON tr.parent_id = t.object_id
INNER JOIN sys.schemas AS sch ON t.schema_id = sch.schema_id
WHERE sch.name LIKE 'field%' AND tr.name LIKE '%AUDIT_1';
SELECT 'DROP TRIGGER IF EXISTS ' + t1.[Trigger]
, *
FROM #t1 AS t1;
/* CODE TO QUICKLY DROP ALL AUDIT TABLES IN SCHEMA */
SELECT SCHEMA_NAME(t.schema_id) AS schema_name
, t.name AS table_name
INTO #t1
FROM sys.tables AS t
WHERE t.name LIKE 't_Audit%' AND SCHEMA_NAME(t.schema_id) LIKE 'Field%'
ORDER BY table_name
, schema_name;
SELECT *
, 'Drop table ' + t1.schema_name + '.' + t1.table_name
FROM #t1 AS t1
WHERE t1.table_name NOT LIKE '%Name%' OR t1.table_name NOT LIKE '%rows%';