I posted a question about my Dynamic Trigger yesterday and it turns out my last issue was due to including one too many fields in my unpivot. That being said, my creation is working! (evil laugh)
A lot of you expressed your concerns that this could be bad for the overall health of my database and I am curious why that would be... Please elaborate after reviewing what I have come up with.
This is a trigger that can be placed on ANY TABLE on ANY SERVER (once you have the necessary things in place). You either need to follow the model below or tweak it to your liking. (i.e. your own log table, your own way of tracking who made a change, etc.)
All you need: The trigger, a logging table (see mine below), and a field in your tables that tracks the user that made the change (in our case all connections go through one centralized SQL profile so this is the only way we can track which user made the change (by passing it as a parameter at the time of update)).
tblChangeLog:
- ID - int - NOT NULL (Identitity (1, 1))
- Message - varchar(max) - NOT NULL
- TableName - varchar(50) - NOT NULL
- PrimaryKey - varchar(50) - NOT NULL
- Activity - varchar(50) - NOT NULL
- CreatedByUser - varchar(30) - NOT NULL - DEFAULT ('System')
- CreatedDate - datetime - NOT NULL - DEFAULT (getdate())
Trigger: (Haven't added delete/insert yet, may only do this for updates)...
CREATE TRIGGER (your name here) ON (your table here)
AFTER UPDATE
AS
BEGIN
SET NOCOUNT ON;
DECLARE @tableName sysname
DECLARE @tableId INT
DECLARE @activity VARCHAR(50)
DECLARE @sql nvarchar(MAX)
-- DETECT AN UPDATE (Records present in both inserted and deleted)
IF EXISTS(SELECT * FROM inserted) AND EXISTS(SELECT * FROM deleted)
BEGIN
SET @activity = 'UPDATE'
-- Gets TableName and TableId
SELECT @tableName = OBJECT_NAME(parent_object_id)
, @tableId = parent_object_id
FROM sys.objects
WHERE sys.objects.name = OBJECT_NAME(@@PROCID)
-- Get the user who made the change
DECLARE @LastUpdUser VARCHAR(50)
SELECT @LastUpdUser = LastUpdUser FROM inserted
-- Stores possible column names
CREATE TABLE #Columns (
name varchar(100)
)
-- Stores only updated columns
CREATE TABLE #Changes (
Id sql_variant,
FieldName sysname,
FieldValue_OLD sql_variant,
FieldValue_NEW sql_variant,
DateChanged datetime DEFAULT (GETDATE()),
LastUpdUser varchar(50),
GroupNumber int
)
-- Gathers names of all possible updated columns (excluding generic)
INSERT INTO #columns
SELECT Name
FROM sys.columns
WHERE object_id = @tableId
AND Name NOT IN ('LastUpdUser', 'LastUpdDate', 'CreatedByUser', 'CreatedDate', 'ConcurrencyId')
-- Builds 2 dynamic strings of columns to use in pivot
DECLARE @castFields nvarchar(max) -- List of columns being cast to sql_variant
DECLARE @listOfFields nvarchar(max) -- List of columns for unpivot
SELECT @castFields = COALESCE(@castFields + ', ', '') + ('CAST(' + QUOTENAME(Name) + ' AS sql_variant) [' + Name + ']') FROM #columns
SELECT @listOfFields = COALESCE(@listOfFields + ', ', '') + QUOTENAME(Name) FROM #columns WHERE Name <> 'Id'
-- Inserting deleted/inserted data into temp tables
SELECT * into #deleted FROM deleted
SELECT * into #inserted FROM inserted
SELECT @sql = ';WITH unpvt_deleted AS (
SELECT Id, FieldName, FieldValue
FROM
(SELECT ' + @castFields + '
FROM #deleted) p
UNPIVOT
(FieldValue FOR FieldName IN
(' + @listOfFields + ')
) AS deleted_unpivot
),
unpvt_inserted AS (
SELECT Id, FieldName, FieldValue
FROM
(SELECT ' + @castFields + '
FROM #inserted) p
UNPIVOT
(FieldValue FOR FieldName IN
(' + @listOfFields + ')
) AS inserted_unpivot
)
INSERT INTO #Changes (Id, FieldName, FieldValue_OLD, FieldValue_NEW, LastUpdUser, GroupNumber)
SELECT COALESCE(D.Id, I.Id) Id
, COALESCE(D.FieldName, I.FieldName) FieldName
, D.FieldValue AS FieldValue_OLD
, I.FieldValue AS FieldValue_NEW
, ''' + @LastUpdUser + '''
, DENSE_RANK() OVER(ORDER BY I.Id) AS GroupNumber
FROM unpvt_deleted D
FULL OUTER JOIN unpvt_inserted I ON D.Id = I.Id AND D.FieldName = I.FieldName
WHERE D.FieldValue <> I.FieldValue
DECLARE @i INT = 1
DECLARE @lastGroup INT
SELECT @lastGroup = MAX(GroupNumber) FROM #Changes
WHILE @i <= @lastGroup
BEGIN
DECLARE @Changes VARCHAR(MAX)
SELECT @Changes = COALESCE(@Changes + ''; '', '''')
+ UPPER(CAST(FieldName AS VARCHAR)) + '': ''
+ '''''''' + CAST(FieldValue_OLD AS VARCHAR) + '''''' to ''
+ '''''''' + CAST(FieldValue_NEW AS VARCHAR) + ''''''''
FROM #Changes
WHERE GroupNumber = @i
ORDER BY GroupNumber
INSERT INTO tblChangeLog (Message, TableName, PrimaryKey, Activity, CreatedByUser, CreatedDate)
SELECT Distinct @Changes, ''' + @tableName + ''', CONVERT(VARCHAR, Id), ''' + @activity + ''', LastUpdUser, DateChanged
FROM #Changes
WHERE GroupNumber = @i
SET @Changes = NULL
SET @i += 1
END
DROP TABLE #Changes
DROP TABLE #columns
DROP TABLE #deleted
DROP TABLE #inserted'
exec sp_executesql @sql
END
END
To make this even more universal, my DBA has come up with a nifty script and dummy table that will store this trigger as a row in the dummy table. The script uses a cursor and finds every table on a database and creates a trigger for each table on the fly. We have tested that as well and it works like a charm, not bothering posting that here at this time.
