有人能推荐我如何加快这段代码的速度，主要是光标的速度吗？该代码是一个SQL Server数据库查询，用于创建INSERT、UPDATE或DELETE的触发器。它将一条记录写入一个更改日志表，标识更改的类型(I、U或D(，然后将每行受影响列的旧值和新值保存在详细信息表中。
我希望它是通用的，这样我就可以很容易地将它用于我向它抛出的任何具有唯一列的表，我可以对其进行筛选。不幸的是，将整行更改写入克隆的结构审核表不是一种选择。
非常感谢任何帮助，我不是查询优化方面的佼佼者，欢迎任何反馈或重写。。谢谢
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
承认使用更改数据跟踪的建议，并注意不要在触发器中放入太多逻辑，下面是对更改捕获逻辑的重构(以及一些彻底的重写(。
更新后的逻辑对数据进行一次传递，同时处理所有受影响的记录。考虑到这些要求，我认为它非常接近最佳，但可能还有改进的空间。XML之间的转换可能会增加相当大的开销。另一种选择是为每个表动态生成并应用自定义触发器，这些触发器分别显式引用所有数据列，以获取详细信息并将它们联合在一起。
我还改进了值比较，以便更好地处理null、区分大小写和潜在的尾部空格更改。
下面的代码不是触发器的形式，而是适合于独立测试的形式。我想你(以及任何其他可能感兴趣的人(都会想测试一下。一旦检查出来，你应该可以把它改装回你的扳机上。
请注意，这不是一个100%的广义解决方案。某些列类型可能不受支持。该逻辑当前采用integer类型的单列主键。为了处理与这些(可能还有一些目前尚未确定的(约束的偏差，将需要进行更改。
-- Simulated change log tables
DECLARE @TableChangeLogSummary TABLE (ID INT IDENTITY(1,1), KeyValue INT NOT NULL, ModifiedDate DATETIME NOT NULL, ChangeType CHAR(1) NOT NULL, TableName NVARCHAR(1000) NOT NULL )
DECLARE @TableChangeLogDetails TABLE (ID INT IDENTITY(1,1), SummaryID int NOT NULl, ColumnName NVARCHAR(1000) NOT NULL, OldValue NVARCHAR(MAX), NewValue NVARCHAR(MAX))
-- Simulated system defined inserted/deleted tables
DECLARE @inserted TABLE (ID INTEGER, Value1 NVARCHAR(100), Value2 BIT, Value3 FLOAT)
DECLARE @deleted TABLE (ID INTEGER, Value1 NVARCHAR(100), Value2 BIT, Value3 FLOAT)
-- Test data
INSERT @inserted
VALUES
(1, 'AAA', 0, 3.14159), -- Insert
(2, 'BBB', 1, null), -- Mixed updates including null to non-null and non-null to null
(3, 'CCC', 0, 0), -- Trailing space change
(4, 'DDD', null, 1.68), -- No changes
(5, '', 0, null), -- No changes with blanks and nulls
(6, null, null, null), -- No changes all nulls
(7, null, null, null) -- Insert all nulls (summary with key, but no details will be logged)
INSERT @deleted
VALUES
(2, 'bbb', null, 2.73),
(3, 'CCC ', 0, 0),
(4, 'DDD', null, 1.68),
(5, '', 0, null),
(6, null, null, null),
(8, null, null, null), -- Delete all null values (summary with key, but no details will be logged)
(9, 'ZZZ', 999, 999.9) -- Delete non-nulls
--- Now the real work begins...
-- Set table and information. Assumes table has exactly one PK column. Later logic assumes an INT.
DECLARE @TableName NVARCHAR(1000) = 'MyTable' -- To be extracted from the parent object of the trigger
DECLARE @KeyColumnName SYSNAME = 'ID' -- This can be fixed if known or derived on the fly from the primary key definition
-- Extract inserted and/or deleted data
DECLARE @InsertedXml XML = (
SELECT *
FROM @inserted
FOR XML PATH('inserted'), TYPE
)
DECLARE @DeletedXml XML = (
SELECT *
FROM @deleted
FOR XML PATH('deleted'), TYPE
)
-- Parse and reassange the captured key and data values
DECLARE @TempDetails TABLE(
KeyValue INT NOT NULL,
ChangeType CHAR(1) NOT NULL,
ColumnName VARCHAR(1000) NOT NULL,
IsKeyColumn BIT NOT NULL,
NewValue NVARCHAR(MAX),
OldValue NVARCHAR(MAX))
INSERT @TempDetails
SELECT
KeyValue = COALESCE(I.KeyValue, D.KeyValue),
ChangeType = CASE WHEN D.KeyValue IS NULL THEN 'I' WHEN I.KeyValue IS NULL THEN 'D' ELSE 'U' END,
ColumnName = COALESCE(I.ColumnName, D.ColumnName),
IsKeyColumn = K.IsKeyColumn,
NewValue = I.Value,
OldValue = D.Value
FROM (
SELECT K.KeyValue, C.ColumnName, C.Value
FROM @InsertedXml.nodes( '/inserted' ) R(Row)
CROSS APPLY (
SELECT KeyValue = C.Col.value('text()[1]', 'int')
FROM R.Row.nodes( './*' ) C(Col)
WHERE C.Col.value( 'local-name(.)', 'nvarchar(MAX)' ) = @KeyColumnName
) K
CROSS APPLY (
SELECT ColumnName = C.Col.value('local-name(.)', 'nvarchar(MAX)'), Value = C.Col.value('text()[1]', 'nvarchar(MAX)')
FROM R.Row.nodes( './*' ) C(Col)
) C
) I
FULL OUTER JOIN (
SELECT K.KeyValue, C.ColumnName, C.Value
FROM @DeletedXml.nodes( '/deleted' ) R(Row)
CROSS APPLY (
SELECT KeyValue = C.Col.value('text()[1]', 'int')
FROM R.Row.nodes( './*' ) C(Col)
WHERE C.Col.value( 'local-name(.)', 'nvarchar(MAX)' ) = @KeyColumnName
) K
CROSS APPLY (
SELECT ColumnName = C.Col.value('local-name(.)', 'nvarchar(MAX)'), Value = C.Col.value('text()[1]', 'nvarchar(MAX)')
FROM R.Row.nodes( './*' ) C(Col)
) C
) D
ON D.KeyValue = I.KeyValue
AND D.ColumnName = I.ColumnName
CROSS APPLY (
SELECT IsKeyColumn = CASE WHEN COALESCE(I.ColumnName, D.ColumnName) = @KeyColumnName THEN 1 ELSE 0 END
) K
WHERE ( -- We need to be careful about edge cases here
(I.Value IS NULL AND D.Value IS NOT NULL)
OR (I.Value IS NOT NULL AND D.Value IS NULL)
OR I.Value <> D.Value COLLATE Latin1_General_Bin -- Precise compare (case and accent sensitive)
OR DATALENGTH(I.Value) <> DATALENGTH(D.Value) -- Catch trailing space cases
OR K.IsKeyColumn = 1
)
-- Get rid of updates with no changes, but keep key-only inserts or deletes
DELETE T
FROM @TempDetails T
WHERE T.IsKeyColumn = 1
AND T.ChangeType = 'U'
AND NOT EXISTS (
SELECT *
FROM @TempDetails T2
WHERE T2.KeyValue = T.KeyValue
AND T2.IsKeyColumn = 0
)
-- Local table to capture and link SummaryID between the summary and details tables
DECLARE @CaptureSummaryID TABLE (SummaryID int, KeyValue INT NOT NULL)
-- Insert change summary and capture the assigned Summary ID via the OUTPUT clause
INSERT INTO @TableChangeLogSummary (KeyValue, ModifiedDate, ChangeType, TableName)
OUTPUT INSERTED.id, INSERTED.KeyValue INTO @CaptureSummaryID
SELECT T.KeyValue, ModifiedDate = GETDATE(), T.ChangeType, TableName = @TableName
FROM @TempDetails T
WHERE T.IsKeyColumn = 1
ORDER BY T.KeyValue -- Optional, but adds consistancy
-- Insert change details
INSERT INTO @TableChangeLogDetails (SummaryID, ColumnName, OldValue, NewValue)
SELECT S.SummaryID, T.ColumnName, T.OldValue, T.NewValue
FROM @CaptureSummaryID S
JOIN @TempDetails T ON T.KeyValue = S.KeyValue
WHERE T.IsKeyColumn = 0
ORDER BY T.ColumnName -- Optional, but adds consistancy
-- View test results
SELECT 'Change Log:', *
FROM @TableChangeLogSummary S
LEFT JOIN @TableChangeLogDetails D ON D.SummaryID = S.ID
ORDER BY S.ID, D.ID