无光标触发批量更新

Question

我在 SQL Server 2005 中有一个触发器，用于跟踪特定字段的审核更改。我们在单独的表中跟踪变化。此触发器按单个记录条目的预期工作，这就是它的预期，但现在他们希望通过批量更新跟踪对列所做的更改。

当前触发器：

CREATE TRIGGER [dbo].[trg_LogChanges]
   ON [dbo].[Test]
   FOR UPDATE
AS 

DECLARE @TableName VARCHAR(100) ,
    @UpdatedDate smalldatetime ,
    @UpdatedBy uniqueidentifier

SELECT @TableName = 'dbo.Test'

IF(SELECT COUNT(*) FROM INSERTED) = 1
    BEGIN
       IF(SELECT LastModifiedDate FROM INSERTED) Is Null
        SET @UpdatedDate = getdate()
       ELSE
        SET @UpdatedDate = (SELECT LastModifiedDate FROM INSERTED)

       IF(SELECT LastModifiedBy FROM INSERTED) Is Null
        SET @UpdatedBy = '11111111-1111-1111-1111-111111111111'
       ELSE
        SET @UpdatedBy = (SELECT LastModifiedBy FROM INSERTED)

            IF UPDATE (ActDate)
        BEGIN
        INSERT INTO dbo.LogChanges
        (
            ChangeType
            , TableName
            , RecordGuid
            , FieldName
            , OldValue
            , NewValue
            , UpdatedBy
            , UpdatedDate
        )
        SELECT 
            'U'
            , @TableName
            , d.Guid
            , 'ActDate'
            , d.ActDate
            , i.ActDate
            , @UpdatedBy
            , @UpdatedDate
        FROM INSERTED i
        INNER JOIN DELETED d
            on i.Guid = d.Guid
        WHERE 
            (d.ActDate IS NULL AND i.ActDate IS NOT NULL)
            OR (d.ActDate IS NOT NULL AND i.ActDate IS NULL)
            OR (d.ActDate <> i.ActDate)
       END
        -- this keeps going for each field that we need to get the Audit Trail on
    END
ELSE
    BEGIN
        -- now I need to track for multiple records
        -- I tried changing the WHERE clause above to see if it would work for bulk updates
        INSERT INTO...
        SELECT...
        WHERE 
    (
        (d.ActDate IS NULL AND i.ActDate IS NOT NULL)
        OR (d.ActDate IS NOT NULL AND i.ActDate IS NULL)
        OR (d.ActDate <> i.ActDate)
    )
    AND d.ActDate IN (SELECT d.ActDate FROM DELETED d)
    END

此代码不适用于多个记录，它会引发错误：

Subquery returned more than 1 value. This is not permitted when the subquery 
follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

如何更改当前触发器以进行批量更新。我必须使用光标来执行此操作吗？如果是这样，那么有人可以提供一些示例代码吗？

Answer 1

当有多个记录时，如何设置@UpdatedDate和@UpdatedBy？我怀疑你正在做类似的事情，这就是你为一张记录所做的事情。

IF(SELECT LastModifiedBy FROM INSERTED) Is Null
  SET @UpdatedBy = '11111111-1111-1111-1111-111111111111'
ELSE
 SET @UpdatedBy = (SELECT LastModifiedBy FROM INSERTED)

如果 INSERTED 中有多于一行，则 ELSE 下的 SET 将导致错误。

试试这个

INSERT INTO dbo.LogChanges
(
ChangeType
, TableName
, RecordGuid
, FieldName
, OldValue
, NewValue
, UpdatedBy
, UpdatedDate
)
SELECT 
'U'
, @TableName
, d.Guid
, 'ActDate'
, d.ActDate
, i.ActDate
, ISNULL(LastModifiedBy, '11111111-1111-1111-1111-111111111111')
, ISNULL(LastModifiedDate, getdate())
FROM INSERTED i
INNER JOIN DELETED d
    on i.Guid = d.Guid
WHERE (d.ActDate IS NULL AND i.ActDate IS NOT NULL)
    OR (d.ActDate IS NOT NULL AND i.ActDate IS NULL)
    OR (d.ActDate <> i.ActDate)

这应该适用于任意数量的记录，包括一条记录。请注意，我没有检查UPDATE(ActDate)。如果 ActDate 没有更改，则不会返回任何记录。