Início / dba / Perguntas / 46672
Accepted
Hani El Mouallem
Asked: 2013-07-20 03:56:18 +0800 CST

Após os gatilhos de atualização que lidam com atualizações de várias linhas

  • 772

Atualmente trabalhando em um projeto de auditoria de banco de dados baseado em triggers que são disparadas na atualização de tabelas específicas. Os gatilhos gravam as alterações em uma tabela; informações gravadas são: nome da tabela, coluna atualizada, timestamp, usuário, valor antigo e novo valor.

Os gatilhos funcionam bem com atualizações únicas, mas quando se trata de atualizações de várias linhas, não estão funcionando.

Meu código está assim:

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

Mensagem de erro:

Msg 512, Nível 16, Estado 1, Procedimento MyTrigger, Linha 818 A
subconsulta retornou mais de 1 valor. Isso não é permitido quando a subconsulta segue =, !=, <, <= , >, >= ou quando a subconsulta é usada como uma expressão.

Alguma sugestão sobre como corrigir meu gatilho para lidar com operações de várias linhas?

sql-server sql-server-2012

2 respostas

  1. Aqui está como corrigir os erros, usando junções adequadas (e se isso não for "rápido o suficiente", observe sua indexação):

    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)
);

    No entanto, acho que é muito tolo executar mais de 50 inserções diferentes dessa variedade para capturar cada alteração de coluna. Por que não apenas criar uma tabela com colunas para hora e nome da tabela (você não precisa armazenar o nome de usuário porque sempre pode procurar isso mais tarde) e, sempre que houver uma atualização, armazenar a versão antiga e a nova da linha? Você pode até usar um SEQUENCEpara garantir que possa identificar o conjunto de linhas que foram modificadas juntas (já que o timestamp pode não ser exclusivo o suficiente para fazer isso).

    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 ...
);

    Agora no seu gatilho:

    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

    Agora, escreva consultas complicadas contra essa estrutura de auditoria muito mais simples que é ineficiente e tente rastrear exatamente quais colunas foram alteradas e tudo mais. É muito melhor pagar esse preço ao revisar dados de auditoria do que pagar esse preço em cada operação de atualização.

    • 9
  2. Best Answer

    Ambas as subconsultas na consulta que você mostra juntam-se a INSERTED sem fazer uma agregação ou um top(1). Portanto, ambos potencialmente retornam mais de uma linha. em vez de ingressar na tabela INSERTED novamente, apenas faça referência à coluna diretamente. Com isso a segunda consulta ficaria assim:

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

    A mudança para o outro é semelhante.

    • 4

relate perguntas