Início / dba / Perguntas / 326202
Accepted
Lando_
Asked: 2023-04-20 13:58:19 +0800 CST

Gatilho de auditoria para rastrear apenas quando colunas específicas são alteradas e registrar apenas os valores antigos e novos para as colunas alteradas

  • 772

Eu tenho uma tabela com 75 colunas e preciso rastrear cerca de 15 dessas colunas para fazer logoff dos valores antigos e novos sempre que os dados em qualquer uma dessas 15 colunas forem alterados (somente atualizações, sem inserções ou exclusões). A exceção é que queremos apenas rastrear as colunas que são atualizadas dessas 15. Se o valor antigo e os novos valores forem NULL ou ambos iguais, queremos apenas registrar NULLs na tabela de auditoria para essas colunas.

Várias colunas são anuláveis ​​e são uma combinação de tipos de dados inteiros, decimais e nvarchar.

Tudo isso está sendo executado em um banco de dados SQL do Azure.

Escrevi um gatilho inicial após a atualização que possui uma instrução de inserção em uma tabela de auditoria com 30 colunas, uma coluna "antiga" e "nova" para todas as 15 que desejamos rastrear. Tudo isso funciona muito bem quando os valores antigos e novos ainda não são NULL, mas obtemos dados ausentes se um dos valores antigos ou novos for NULL.

Para explicar isso, comecei a escrever instruções de caso para cada coluna e um monte de cláusulas where, mas não parece que esse seja o caminho certo a seguir. Basicamente, 15 variações diferentes de declarações como:

CREATE OR ALTER TRIGGER [dbo].[trg_Stuff_Audit]
ON [dbo].[Stuff]
AFTER UPDATE
AS
BEGIN

insert into dbo.audit (id, OldDecimalValue, NewDecimalValue)

select i.id,

case when d.DecimalValue is null and i.DecimalValue is not null then d.DecimalValue
when d.DecimalValue is not null and i.DecimalValue is null then d.DecimalValue
when d.DecimalValue <> i.DecimalValue then d.DecimalValue
else NULL end as OldDecimalValue,

case when d.DecimalValue is null and i.DecimalValue is not null then i.DecimalValue
when d.DecimalValue is not null and i.DecimalValue is null then i.DecimalValue
when d.DecimalValue <> i.DecimalValue then i.DecimalValue
else NULL end as NewDecimalValue

from inserted i inner join deleted d on i.id = d.id

where d.DecimalValue is null and i.DecimalValue is not null
OR d.DecimalValue is not null and i.DecimalValue is null
OR d.DecimalValue <> i.DecimalValue

END;

Sinto que deve haver uma maneira melhor de resolver isso, estou indo pelo caminho certo ou preciso mudar de rumo?

trigger

3 respostas

  1. Talvez você possa fazer dbo.stuff uma tabela temporal/versionada. Cada linha terá um horário de início e término com as linhas antigas sendo colocadas na tabela de histórico.

    Tabelas temporais

    • 2

  2. Eu recomendo seguir o caminho de usar tabelas temporais como a outra resposta.

    Mas se você ainda quiser manter os gatilhos, pode simplificar um pouco, desarticulando os valores e comparando de uma só vez.

    Em outras palavras: não armazene a linha inteira novamente, apenas armazene um par de colunas OldValuee NewValue, cada linha representando uma única alteração em uma coluna por linha.

    CREATE OR ALTER TRIGGER [dbo].[trg_Stuff_Audit]
ON [dbo].[Stuff]
AFTER INSERT, UPDATE, DELETE
AS

SET NOCOUNT ON;

IF NOT EXISTS (SELECT 1 FROM inserted) AND NOT EXISTS (SELECT 1 FROM deleted)
    RETURN;

INSERT dbo.audit (id, ColumnName, OldDecimalValue, NewDecimalValue)
SELECT
  ISNULL(i.id, d.id),
  v.ColumnName
  v.OldValue,
  v.NewValue
FROM inserted i
JOIN deleted d on i.id = d.id
CROSS APPLY (
    SELECT *
    FROM (VALUES
      ('decimalValue1', d.decimalValue1, i.decimalValue1),
      ('decimalValue2', d.decimalValue2, i.decimalValue2),
      ('decimalValue3', d.decimalValue3, i.decimalValue3)
    ) v(ColumnName, OldValue, NewValue)
    WHERE v.OldValue IS DISTINCT FROM v.NewValue
) v;

    Se você tiver tipos de dados diferentes, precisará converter parasql_variant

    ...
    FROM (VALUES
      ('decimalValue1', CAST(d.decimalValue1 AS sql_variant), CAST(i.decimalValue1 AS sql_variant)),
      ('string2',       d.string2,       i.string2),
      ('int3',          d.int3,          i.int3)
    ) v(ColumnName, OldValue, NewValue)
...

    Em versões anteriores ao SQL Server 2022, você precisa alterar oWHERE

        WHERE v.OldValue <> v.NewValue
       OR v.OldValue IS NULL and v.NewValue IS NOT NULL
       OR v.OldValue IS NOT NULL and v.NewValue IS NULL

    Observe que usei AFTER INSERT, UPDATE, DELETEpara rastrear totalmente todas as alterações em uma tabela. Além disso, observe que o unpivot funciona após a junção, não verifica as tabelas inseridas e excluídas várias vezes

    • 1
  3. Best Answer

    Acho que sua melhor abordagem seria apenas modificar seu gatilho existente para lidar adequadamente com NULL para valores antigos ou novos. Já que você está na nuvem, você pode usar o IS DISTINCT FROM que CharlieFace referencia em sua cláusula WHERE.

    WHERE I.DecimalValue IS DISTINCT FROM D.DecimalValue
    OR I.DecimalValue2 IS DISTINCT FROM D.DecimalValue2
    /** REPEAT **/

    Esse provavelmente será o caminho mais rápido para uma boa resposta.

    Pessoalmente, eu me afasto das tabelas temporais porque elas rastreiam TUDO e, como você, tenho requisitos muito específicos e permanecer com os requisitos específicos torna tudo mais rápido.

    O que me leva a também evitar soluções mais complicadas, como Charlieface recomenda. Quando é uma ou duas linhas por vez, não importa muito, mas quando você tem centenas de linhas por vez para o gatilho examinar, o fato de as tabelas INSERTED e DELETED serem heaps significa que a taxa de transferência será sofrer muito. Uma inserção rápida e suja em uma tabela de auditoria que provavelmente é lida raramente é muito melhor, mesmo que reconstruir os eventos de maneira limpa seja um pouco mais trabalhoso.

    Se você quiser continuar mostrando NULL quando o Antigo e o Novo forem iguais, poderá fazer isso.

    SELECT I.ID 
    , DecimalValue_NEW = ISNULL(NULLIF(I.DecimalValue, D.DecimalValue), NULLIF(D.DecimalValue, I.DecimalValue))
    , DecimalValue_OLD = ISNULL(NULLIF(D.DecimalValue, I.DecimalValue), NULLIF(I.DecimalValue, D.DecimalValue))
    , DecimalValue2_NEW = ISNULL(NULLIF(I.DecimalValue2, D.DecimalValue2), NULLIF(D.DecimalValue2, I.DecimalValue2))
    , DecimalValue2_OLD = ISNULL(NULLIF(D.DecimalValue2, I.DecimalValue2), NULLIF(I.DecimalValue2, D.DecimalValue2))
FROM INSERTED AS I
    INNER JOIN DELETED AS D ON D.ID = I.ID 
WHERE I.DecimalValue IS DISTINCT FROM D.DecimalValue
    OR I.DecimalValue2 IS DISTINCT FROM D.DecimalValue2

    Consulte https://stackoverflow.com/questions/1075142/how-to-compare-values-which-may-both-be-null-in-t-sql para saber onde obtive ISNULL(NULLIF(A,B), NULLIF(B,A)) construção...

    Minha regra para gatilhos é fazer o trabalho rápido, não tente analisar os dados se puder ajudá-los e sair ... e tornar a operação à prova de balas para que ela lance uma exceção apenas se você precisar lançar um exceção.

    • 0

relate perguntas