主页 / dba / 问题 / 46672
Accepted
Hani El Mouallem
Asked: 2013-07-20 03:56:18 +0800 CST

处理多行更新的更新后触发器

  • 772

目前正在开发一个数据库审计项目，该项目基于在特定表上更新时触发的触发器。触发器将更改写入表；写入的信息有：表名、更新列、时间戳、用户、旧值和新值。

触发器在单次更新时工作正常，但在多行更新时，它不起作用。

我的代码是这样的：

IF (UPDATE(Priority))  
BEGIN
    SET @UpdatedColumn = 'Priority'
    INSERT INTO dbo.AuditTable
        ( [TableName] ,
          [Source] ,
          [RecordId] ,
          [User] ,
          [TimeStamp] ,
          [UpdatedColumn] ,
          [OldValue] ,
          [NewValue]
        )
    SELECT 
        N'BookingItem' , -- TableName - nvarchar(max)
        (SELECT CODE FROM TBL_LEG_SOURCE 
                     INNER JOIN INSERTED INS ON LEG_SOURCE_ID = INS.SourceId) ,
        INS.Id , -- RecordId - bigint
        (SELECT USERNAME FROM INSERTED 
                     INNER JOIN TBL_USER 
                     ON ModifiedById = USER_ID) , -- User - nvarchar(max)
        GETDATE() , -- TimeStamp - datetime
        @UpdatedColumn , -- UpdatedColumn - nvarchar(max)
        DEL.Priority , -- OldValue - nvarchar(max)
        INS.Priority  -- NewValue - nvarchar(max)
    FROM 
        INSERTED INS INNER JOIN DELETED DEL ON INS.Id = DEL.Id
    WHERE
        (
            (INS.Priority <> DEL.Priority)
            OR (INS.Priority IS NULL AND DEL.Priority IS NOT NULL)
            OR (INS.Priority IS NOT NULL AND DEL.Priority IS NULL)
        )
END

错误信息：

消息 512，级别 16，状态 1，过程 MyTrigger，第 818 行
子查询返回超过 1 个值。当子查询跟随 =、!=、<、<=、>、>= 或子查询用作表达式时，这是不允许的。

有关如何修复触发器以处理多行操作的任何建议？

sql-server sql-server-2012

2 个回答

  1. 以下是如何使用正确的连接来修复错误（如果这还不够“快”，请查看您的索引）：

    INSERT dbo.AuditTable
(
  [TableName],
  [Source],
  [RecordId],
  [User],
  [TimeStamp],
  [UpdatedColumn],
  [OldValue],
  [NewValue]
)
SELECT 
  N'BookingItem', -- TableName - nvarchar(max)
  ls.CODE,
  INS.Id, -- RecordId - bigint
  u.USERNAME,
  GETDATE(), -- TimeStamp - datetime
  @UpdatedColumn, -- UpdatedColumn - nvarchar(max)
  DEL.Priority, -- OldValue - nvarchar(max)
  INS.Priority  -- NewValue - nvarchar(max)
FROM 
  INSERTED AS INS 
INNER JOIN 
  DELETED AS DEL ON INS.Id = DEL.Id
INNER JOIN 
  dbo.TBL_LEG_SOURCE AS ls ON ls.LEG_SOURCE_ID = INS.SourceId
INNER JOIN
  dbo.TBL_USER AS u ON INS.ModifiedById = u.USER_ID
WHERE
(
  (INS.Priority <> DEL.Priority)
  OR (INS.Priority IS NULL AND DEL.Priority IS NOT NULL)
  OR (INS.Priority IS NOT NULL AND DEL.Priority IS NULL)
);

    但是我认为运行 50 多个不同的插入来捕获每一个列的变化是非常愚蠢的。为什么不直接创建一个包含时间和表名列的表（您不需要存储用户名，因为您以后可以随时查看），然后每当有更新时，存储该行的旧版本和新版本？您甚至可以使用 aSEQUENCE来确保您可以识别一起修改的行集（因为时间戳可能不够独特，无法做到这一点）。

    CREATE SEQUENCE dbo.AuditSequence
  AS INT START WITH 1 INCREMENT BY 1;

CREATE TABLE dbo.AuditData
(
  AuditSequenceID INT,
  TableName SYSNAME,
  [TimeStamp] DATETIME,
  RowState CHAR(1), -- e.g. 'B' = before, 'A' = after
  ... all your 50 columns, including ModifidById ...
);

    现在在您的触发器中：

    CREATE TRIGGER dbo.MyTrigger
  ON dbo.BookingItem
  FOR UPDATE
AS
BEGIN
  SET NOCOUNT ON;

  DECLARE @as INT = NEXT VALUE FOR dbo.AuditSequence,
          @now DATETIME = CURRENT_TIMESTAMP;

  INSERT dbo.AuditData(AuditSequenceID, TableName, [TimeStamp], RowState,
    ... the rest of your 50 columns)
  SELECT @as, N'BookingItem', @now, 'B', * FROM deleted;

  INSERT dbo.AuditData(AuditSequenceID, TableName, [TimeStamp], RowState,
    ... the rest of your 50 columns)
  SELECT @as, N'BookingItem', @now, 'A', * FROM inserted;
END
GO

    现在，针对这种效率低下的更简单的审计结构编写复杂的查询，并尝试准确跟踪哪些列已更改以及所有这些。在审查审计数据时付出这个代价要比在每次更新操作上付出那个代价要好得多。

    • 9
  2. Best Answer

    您显示的查询中的两个子查询都连接到 INSERTED，而无需执行聚合或 top(1)。所以两者都可能返回不止一行。无需再次加入 INSERTED 表，只需直接引用该列即可。这样，第二个查询将如下所示：

    (SELECT U.USERNAME FROM TBL_USER U WHERE INS.ModifiedById = U.USER_ID)

    另一个的变化是类似的。

    • 4

相关问题