Can anyone recommend how I can speed up this code and primarily the cursor? The code is an SQL Server db query that creates a trigger on INSERT, UPDATE, or DELETE. It writes a record to a changlog table identifying the type of change (I, U, or D) and then saves the old value and new value of each affected column for each row in a details table.
I want this to be generic so I can easily reuse it for any table I throw at it that has a unique column I can filter on. Writing the whole row of changes to a cloned structure audit table is not an option unfortunately.
Any help is greatly appreciated, I am not the greatest at query optimization and welcome any feedback or rewrites.. Thanks!
ALTER TRIGGER [dbo].[tbl_Address_ChangeTracking] ON [dbo].[tbl_Address]
AFTER INSERT, DELETE, UPDATE
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON
--SET XACT_ABORT ON
-- Get the table name of the current process
DECLARE @TableName VARCHAR(25)
SET @TableName = COALESCE(
(
SELECT SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id)
FROM sys.objects
WHERE sys.objects.name = OBJECT_NAME(@@PROCID) AND
SCHEMA_NAME(sys.objects.schema_id) = OBJECT_SCHEMA_NAME(@@PROCID)
), 'Unknown')
--Declare our cursor to navigate the records in inserted and deleted
DECLARE @cursorSQL AS NVARCHAR(MAX) = ''
DECLARE @PrimaryID AS VARCHAR(MAX) = ''
DROP TABLE IF EXISTS #inserted1TableTemp
DROP TABLE IF EXISTS #inserted2TableTemp
DROP TABLE IF EXISTS #deletedTableTemp
DECLARE @ourLogCursor CURSOR
--If we have a record in inserted and deleted this is an update record and we should pull from the inserted table and assume
--this is one update or many update statements
IF EXISTS
(
SELECT 1
FROM inserted
) AND
EXISTS
(
SELECT 1
FROM deleted
)
BEGIN
SELECT *
INTO #inserted1TableTemp
FROM inserted
SET @cursorSQL = 'SET @ourLogCursor = CURSOR FOR SELECT AddressID FROM #inserted1TableTemp; OPEN @ourLogCursor;'
END
--If we have an inserted record and no deleted record this is an insert and we pull from the inserted table
IF EXISTS
(
SELECT 1
FROM inserted
) AND
NOT EXISTS
(
SELECT 1
FROM deleted
)
BEGIN
DROP TABLE IF EXISTS #inserted2TableTemp
DROP TABLE IF EXISTS #inserted1TableTemp
DROP TABLE IF EXISTS #deletedTableTemp
SELECT *
INTO #inserted2TableTemp
FROM inserted
SET @cursorSQL = 'SET @ourLogCursor = CURSOR FOR SELECT AddressID FROM #inserted2TableTemp; OPEN @ourLogCursor;'
END
--If we have a deleted record and no insert record this is a deletion and we pull from the deleted table
IF NOT EXISTS
(
SELECT 1
FROM inserted
) AND
EXISTS
(
SELECT 1
FROM deleted
)
BEGIN
DROP TABLE IF EXISTS #inserted1TableTemp
DROP TABLE IF EXISTS #inserted2TableTemp
DROP TABLE IF EXISTS #deletedTableTemp
SELECT *
INTO #deletedTableTemp
FROM deleted
SET @cursorSQL = 'SET @ourLogCursor = CURSOR FOR SELECT AddressID FROM #deletedTableTemp; OPEN @ourLogCursor;'
END
--If we have a deleted record and no insert record this is a deletion and we pull from the deleted table
IF NOT EXISTS
(
SELECT 1
FROM inserted
) AND
NOT EXISTS
(
SELECT 1
FROM deleted
)
BEGIN
RETURN;
END
--Execute our dynamic SQL that sets the correct FOR SELECT statment for the cursor. Pass @ourCursorLog as an input param, and then grab the output
--so the results are available outside the scope of the executesql call
EXEC sys.sp_executesql @cursorSQL, N'@ourLogCursor CURSOR OUTPUT', @ourLogCursor OUTPUT;
FETCH NEXT FROM @ourLogCursor INTO @PrimaryID
DECLARE @xmlOld XML
DECLARE @xmlNew XML
DECLARE @SummaryID INT
SET @TableName = COALESCE(
(
SELECT SCHEMA_NAME(schema_id) + '.' + OBJECT_NAME(parent_object_id)
FROM sys.objects
WHERE sys.objects.name = OBJECT_NAME(@@PROCID) AND
SCHEMA_NAME(sys.objects.schema_id) = OBJECT_SCHEMA_NAME(@@PROCID)
), 'Unknown')
--Navigate all our rows
WHILE @@FETCH_STATUS = 0
BEGIN
DROP TABLE IF EXISTS #tmp_AddressesChangelogTrigger
DROP TABLE IF EXISTS #tmp_AddressesChangelogTriggerXML1
DROP TABLE IF EXISTS #tmp_AddressesChangelogTriggerXML2
DROP TABLE IF EXISTS #tmp_AddressesChangelogTriggerXMLsWithDifferences
--Get the deleted and inserted records as xml for comparison against each other
SET @xmlNew =
(
SELECT *
FROM deleted AS [TABLE]
WHERE AddressID = @PrimaryID
ORDER BY AddressID FOR XML AUTO, ELEMENTS
)
SET @xmlOld =
(
SELECT *
FROM inserted AS [TABLE]
WHERE AddressID = @PrimaryID
ORDER BY AddressID FOR XML AUTO, ELEMENTS
)
CREATE TABLE #tmp_AddressesChangelogTriggerXML1
(
NodeName VARCHAR(MAX), Value VARCHAR(MAX)
)
CREATE TABLE #tmp_AddressesChangelogTriggerXML2
(
NodeName VARCHAR(MAX), Value VARCHAR(MAX)
)
--Extract the values and column names
INSERT INTO #tmp_AddressesChangelogTriggerXML2( NodeName, Value )
--Throw the XML into temp tables with the column name and value
SELECT N.value( 'local-name(.)', 'nvarchar(MAX)' ) AS NodeName, N.value( 'text()[1]', 'nvarchar(MAX)' ) AS VALUE
FROM @xmlNew.nodes( '/TABLE/*' ) AS T(N)
INSERT INTO #tmp_AddressesChangelogTriggerXML1( NodeName, Value )
SELECT N.value( 'local-name(.)', 'nvarchar(MAX)' ) AS NodeName, N.value( 'text()[1]', 'nvarchar(MAX)' ) AS VALUE
FROM @xmlOld.nodes( '/TABLE/*' ) AS T(N)
--Get the differences into a temp table
SELECT *
INTO #tmp_AddressesChangelogTriggerXMLsWithDifferences
FROM
(
SELECT COALESCE(A.NodeName, B.NodeName) AS NodeName, B.Value AS OldValue, A.Value AS NewValue
FROM #tmp_AddressesChangelogTriggerXML1 AS A
FULL OUTER JOIN #tmp_AddressesChangelogTriggerXML2 AS B ON A.NodeName = B.NodeName
WHERE A.Value <> B.Value
) AS tmp
--If anything changed thhen start our write statments
IF
(
SELECT COUNT(*)
FROM #tmp_AddressesChangelogTriggerXMLsWithDifferences
) > 0
BEGIN
BEGIN TRY
-- Now create the Summary record
--BEGIN TRANSACTION WRITECHANGELOGRECORDS
INSERT INTO TableChangeLogSummary( ID, ModifiedDate, ChangeType, TableName )
--Get either insert, or if no insert value, get the delete value
--Set the update type, I, D, U
--Compare values with a full outer join
--Filter on the ID we are on in the CURSOR
SELECT COALESCE(I.AddressID, D.AddressID), GETDATE(),
CASE
WHEN D.AddressID IS NULL THEN 'I'
WHEN I.AddressID IS NULL THEN 'D'
ELSE 'U'
END, @TableName
FROM inserted AS I
FULL OUTER JOIN deleted AS D ON I.AddressID = D.AddressID
WHERE( I.AddressID = @PrimaryID OR
I.AcesAddressID IS NULL
) AND
( D.AddressID = @PrimaryID OR
D.AcesAddressID IS NULL
)
--Get the last summary id that was inserted so we can use it in the detail record
SET @SummaryID = (SELECT IDENT_CURRENT('TableChangeLogSummary'))
--Insert our
INSERT INTO TableChangeLogDetail( SummaryID, ColumnName, OldValue, NewValue )
SELECT @SummaryID, T.NodeName, T.OldValue, T.NewValue
FROM #tmp_AddressesChangelogTriggerXMLsWithDifferences AS T
--COMMIT TRANSACTION WRITECHANGELOGRECORDS
--PRINT 'RECORD WRITTEN'
END TRY
BEGIN CATCH
DECLARE @errorXML XML
SET @errorXML = (SELECT ERROR_NUMBER() AS ErrorNumber, ERROR_STATE() AS ErrorState, ERROR_SEVERITY() AS ErrorSeverity, ERROR_PROCEDURE() AS ErrorProcedure, ERROR_LINE() AS ErrorLine, ERROR_MESSAGE() AS ErrorMessage FOR XML RAW)
DECLARE @errorXMLText NVARCHAR(MAX) = ''
SET @errorXMLText = (SELECT CAST(@errorXML AS NVARCHAR(MAX)))
RAISERROR(@errorXMLText, 16, 1) WITH NOWAIT
END CATCH
END
--Go to the next record and process
FETCH NEXT FROM @ourLogCursor INTO @PrimaryID
END
CLOSE @ourLogCursor
DEALLOCATE @ourLogCursor
END
