多行触发器

Question

我编写了一个触发器来记录对表的更改，当然直到之后我才意识到这一次只能对一条记录起作用。现在我正在尝试更新它以允许批量更新，但我不知道如何执行此操作。

CREATE TRIGGER [DT].[trg_LogChanges]
ON [DT].[NewDetails]
FOR UPDATE
AS 

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

SELECT @TableName = 'DT.NewDetails'

IF EXISTS (SELECT 1 FROM INSERTED i INNER JOIN DELETED d on i.Guid = d.Guid)

    IF(SELECT ModifiedDate FROM INSERTED) Is Null
        SET @UpdatedDate = getdate()
    ELSE
        SET @UpdatedDate = (SELECT ModifiedDate FROM INSERTED)

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


IF UPDATE (StatusID)
    BEGIN
        INSERT INTO DT.LogChanges
        (
            ChangeType, TableName, RecordGuid, FieldName
            , OldValue, NewValue, UpdatedBy, UpdatedDate
        )
        SELECT 
            'U', @TableName, d.Guid, 'StatusID'
            , d.StatusID, i.StatusID, @UpdatedBy, @UpdatedDate
        FROM INSERTED i INNER JOIN DELETED d on i.Guid = d.Guid
        WHERE 
            (d.StatusID IS NULL AND i.StatusID IS NOT NULL)
            OR (d.StatusID IS NOT NULL AND i.StatusID IS NULL)
            OR (d.StatusID <> i.StatusID)
    END

任何人都可以提供有关如何修复此问题以处理多行的任何帮助吗？我尝试通过添加 SELECT 1 FROM INSERTED 进行以下操作，但仍然收到子查询错误消息。

CREATE TRIGGER [DT].[trg_LogChanges]
   ON [DT].[NewDetails]
   FOR UPDATE
AS 

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

SELECT @TableName = 'DT.NewDetails'

IF EXISTS (SELECT 1 FROM INSERTED i INNER JOIN DELETED d on i.Guid = d.Guid)

    IF(SELECT ModifiedDate FROM INSERTED) Is Null
        SET @UpdatedDate = getdate()
    ELSE
        SET @UpdatedDate = (SELECT ModifiedDate FROM INSERTED)

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


IF UPDATE (StatusID)
    BEGIN
        IF EXISTS (SELECT 1 FROM INSERTED i
                    INNER JOIN DELETED d
                        on i.Guid = d.Guid
                    WHERE 
                        (d.StatusID IS NULL AND i.StatusID IS NOT NULL)
                        OR (d.StatusID IS NOT NULL AND i.StatusID IS NULL)
                        OR (d.StatusID <> i.StatusID))
        BEGIN
            INSERT INTO DT.LogChanges
            (
                ChangeType, TableName, RecordGuid, FieldName
                , OldValue, NewValue, UpdatedBy, UpdatedDate
            )
            SELECT 
                'U', @TableName, d.Guid, 'StatusID'
                , d.StatusID, i.StatusID, @UpdatedBy, @UpdatedDate
            FROM INSERTED i INNER JOIN DELETED d on i.Guid = d.Guid
            WHERE 
                (d.StatusID IS NULL AND i.StatusID IS NOT NULL)
                OR (d.StatusID IS NOT NULL AND i.StatusID IS NULL)
                OR (d.StatusID <> i.StatusID)
        END
    END

我已经在网上搜索过，但显然我仍然缺少执行此操作的正确方法。任何帮助将不胜感激。

编辑 我与此流程的企业主进行了交谈，他们希望忽略多行的更新。有没有办法在整个触发器周围添加 IF 以在记录大于 0 时忽略它？

谢谢

Answer 1

我相信您的条件检查可以最小化为 CASE 语句。似乎您可以最小化整个触发器，因为

INSERT INTO DT.LogChanges (ChangeType, TableName, RecordGuid, FieldName, OldValue, NewValue, UpdatedBy, UpdatedDate)
  SELECT 'U', 'DT.NewDetails', d.Guid, 'StatusID', d.StatusID, i.StatusID,
CASE WHEN i.ModifiedBy IS NULL THEN GETDATE() ELSE i.ModifiedBy AS ModifiedBy, 
CASE WHEN i.ModifiedDate IS NULL THEN '11111111-1111-1111-1111-111111111111' ELSE i.ModifiedDate AS ModifiedDate
FROM INSERTED i INNER JOIN DELETED d on i.Guid = d.Guid
WHERE      (d.StatusID IS NULL AND i.StatusID IS NOT NULL)
    OR (d.StatusID IS NOT NULL AND i.StatusID IS NULL)
    OR (d.StatusID <> i.StatusID)

我没有时间确保语法是否完美，但如果您遇到一些问题，我可以提供帮助。

为了响应您的编辑，可以使用类似的方法轻松完成

IF (SELECT COUNT(*) FROM INSERTED i INNER JOIN DELETED d on i.Guid = d.Guid) = 1
BEGIN
PRINT 'Only one update record'
END