Como monitorar e logar as colunas que foram atualizadas ?

Boa Noite Pessoal,

Para tirar o atraso de tantos posts e pendências, estou aqui mais uma vez postando um pouco de código TSQL. Meu backlog de dúvidas está imenso e vamos ver se consigo reduzí-lo. Já vi algumas vezes aparecer uma certa dúvida referente ao log de alterações de uma tabela, mas especificamente, como saber quais colunas que sofreram uma alteração de update e quais eram os valores antes (ou até depois) da alteração. Essa é sem dúvida uma necessidade bem comum e difícil de implementar. Há recursos muito mais modernos para fazer isso como o AUDIT, CDC (Change Data Capture) e o CT (Change Tracking), mas como nem todos estão com o SQL Server 2008 ainda vejamos uma forma de fazer isso com triggers.

— Cria uma tabela de operações financeiras
CREATE TABLE
OperacaoFinanceira (
    IDOperacaoFinanceira INT NOT NULL, DataOpFinanceira DATE,
    DataProcessamento DATE, CodStatusOpFin TINYINT,
    CodTipoMovimento INT, IDUsuario VARCHAR(100),
    IDProduto INT, IDModalidade INT, Valor SMALLMONEY,
    BolEstorno TINYINT, BolAuditada TINYINT,
    CONSTRAINT PK_OperacaoFinanceira PRIMARY KEY (IDOperacaoFinanceira))

INSERT INTO OperacaoFinanceira VALUES (25,‘20100501’,‘20100503’,1,4,‘Diego_0003’,23,2,550.01,0,0)
INSERT INTO OperacaoFinanceira VALUES (26,‘20100501’,‘20100503’,1,5,‘Marco_0004’,15,1,945.13,1,0)
INSERT INTO OperacaoFinanceira VALUES (27,‘20100502’,‘20100503’,2,1,‘Tiago_0003’,28,3,126.67,1,1)
INSERT INTO OperacaoFinanceira VALUES (28,‘20100503’,‘20100503’,1,2,‘Fabio_0007’,11,4,437.55,0,1)
INSERT INTO OperacaoFinanceira VALUES (29,‘20100504’,‘20100504’,3,3,‘Diana_0008’,18,2,682.39,0,1)

— Cria uma tabela para logar as alterações
CREATE TABLE AudOF (IDAudOF INT IDENTITY(1,1),
    IDOperacaoFinanceira INT, DataAlteracao DATETIME, Coluna SYSNAME,
    ValorAntigo VARCHAR(100), ValorNovo VARCHAR(100))

Acredito que o script já seja suficientemente claro no que ele se propõe a fazer, ou seja, a cada alteração na tabela OperacaoFinanceira, loga-se o ID da operação financeira, a data da alteração, a coluna alterada e o valor antes e após a alteração. O próximo passo é a criação de uma trigger para poder gravar essas alterações na tabela AudOF. Segue uma primeira implementação.

— Cria uma trigger de atualização na tabela Operação Financeira
CREATE TRIGGER trgAU_AudOF ON OperacaoFinanceira
FOR UPDATE
AS

— Verifica se a atualização foi na coluna DataOpFinanceira
IF UPDATE(DataOpFinanceira)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘DataOpFinanceira’,
        CAST(Del.DataOpFinanceira As VARCHAR(100)), CAST(Ins.DataOpFinanceira As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna DataProcessamento
IF UPDATE(DataProcessamento)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘DataProcessamento’,
        CAST(Del.DataProcessamento As VARCHAR(100)), CAST(Ins.DataProcessamento As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna CodStatusOpFin
IF UPDATE(CodStatusOpFin)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘CodStatusOpFin’,
        CAST(Del.CodStatusOpFin As VARCHAR(100)), CAST(Ins.CodStatusOpFin As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna CodTipoMovimento
IF UPDATE(CodTipoMovimento)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘CodTipoMovimento’,
        CAST(Del.CodTipoMovimento As VARCHAR(100)), CAST(Ins.CodTipoMovimento As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna IDUsuario
IF UPDATE(IDUsuario)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘IDUsuario’,
        CAST(Del.IDUsuario As VARCHAR(100)), CAST(Ins.IDUsuario As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna IDProduto
IF UPDATE(IDProduto)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘IDProduto’,
        CAST(Del.IDProduto As VARCHAR(100)), CAST(Ins.IDProduto As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna IDModalidade
IF UPDATE(IDModalidade)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘IDModalidade’,
        CAST(Del.IDModalidade As VARCHAR(100)), CAST(Ins.IDModalidade As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna Valor
IF UPDATE(Valor)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘Valor’,
        CAST(Del.Valor As VARCHAR(100)), CAST(Ins.Valor As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna BolEstorno
IF UPDATE(BolEstorno)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘BolEstorno’,
        CAST(Del.BolEstorno As VARCHAR(100)), CAST(Ins.BolEstorno As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

— Verifica se a atualização foi na coluna Valor
IF UPDATE(BolAuditada)
    INSERT INTO AudOF SELECT INS.IDOperacaoFinanceira, GETDATE(), ‘BolAuditada’,
        CAST(Del.BolAuditada As VARCHAR(100)), CAST(Ins.BolAuditada As VARCHAR(100))
    FROM Inserted As Ins
    INNER JOIN Deleted As Del ON Ins.IDOperacaoFinanceira = Del.IDOperacaoFinanceira

A trigger utiliza a função UPDATE contra cada coluna, e no caso de alteração, os dados são logados na tabela AudOF. Como toda alteração irá produzir necessariamente uma entrada na INSERTED e na DELETED, o JOIN entre essas tabelas pode ser realizada com segurança. Esse JOIN utiliza a coluna IDOperacaoFinanceira que por ser chave primária artificial supostamente não deve sofrer alterações. O CAST é necessário, pois, as colunas ValorAntigo e ValorNovo na tabela AudOF são do tipo VARCHAR(100) que é o tamanho da coluna de maior comprimento na tabela OperacaoFinanceira (IDUsuario). O script a seguir faz um teste na trigger.

— Atualiza a coluna Valor cuja operação financeira tenha o ID igual a 25
UPDATE OperacaoFinanceira SET Valor = 750.01 WHERE IDOperacaoFinanceira = 25

— Atualiza a coluna IDModalidade cuja operação financeira tenha o ID igual a 26
UPDATE OperacaoFinanceira SET IDModalidade = 4 WHERE IDOperacaoFinanceira = 26

O resultado das alterações é produzido na tabela AudOF conforme mostrado abaixo:

IDAudOF IDOperacaoFinanceira DataAlteracao Coluna ValorAntigo ValorNovo
1 25 2010-05-06 18:54:33.497 Valor 550.01 750.01
2 26 2010-05-06 18:54:33.497 IDModalidade 1 4

O script anterior fez uma alteração em uma única linha de apenas uma única coluna. Como será que a trigger lida com múltiplas alterações em uma linha, ou uma alteração em uma coluna para múltiplas linhas ou ainda múltiplas alterações em várias colunas de várias linhas ?

— Atualiza as colunas CodStatusOpFin e IDUsuario cuja operação financeira tenha o ID igual a 27
UPDATE OperacaoFinanceira SET CodStatusOpFin = 3, IDUsuario = ‘Renam_0003’ WHERE IDOperacaoFinanceira = 27

— Atualiza a coluna Valor onde as operações financeiras tenham data de operação igual ou superior a 03/05/2010
UPDATE OperacaoFinanceira SET Valor = Valor * 1.2 WHERE DataOpFinanceira >= ‘20100503’

O resultado das alterações é produzido na tabela AudOF conforme mostrado abaixo:

IDAudOF IDOperacaoFinanceira DataAlteracao Coluna ValorAntigo ValorNovo
3 27 2010-05-06 18:55:07.930 CodStatusOpFin 2 3
4 27 2010-05-06 18:55:07.930 IDUsuario Tiago_0003 Renam_0003
5 29 2010-05-06 18:55:10.170 Valor 682.39 818.87
6 28 2010-05-06 18:55:10.170 Valor 437.55 525.06

A trigger de fato loga alterações independente do número de linhas e de colunas. Não há na estrutura atual um agrupador para saber se as alterações foram ou não disparadas por um ou por vários comandos. Isso poderia ser resolvido com alguma lógica baseada na data ou ainda o uso de uma coluna auxiliar para capturar o SPID, mas o fato é que da forma que está a trigger tem um notório custo de desenvolvimento e implementação, pois, cada coluna tem um IF a parte que precisa ser mantido.

A função COLUMNS_UPDATED( )

O SQL Server dispõe da função COLUMNS_UPDATED para mapear a relação de colunas alteradas. Embora seja possível saber quais colunas foram alteradas, essa função não é muito trivial e sua utilização pode ser um pouco complicada, pois, o seu funcionamento varia quando uma tabela tem oito colunas, mais de oito colunas e mais de 32 colunas. Vejamos o seu comportamento com uma tabela com oito colunas.

— Cria uma tabela com oito colunas
CREATE TABLE T (C1 INT, C2 INT, C3 INT, C4 INT, C5 INT, C6 INT, C7 INT, C8 INT)

— Insere um registro
INSERT INTO T VALUES (1,2,3,4,5,6,7,8)

— Cria uma trigger
CREATE TRIGGER TR ON T
FOR UPDATE
AS
PRINT
COLUMNS_UPDATED()

— Faz uma alteração
UPDATE T SET C1 = 0

O retorno da função COLUMNS_UPDATE mostra um valor em hexadecimal que representa a relação de colunas alteradas. O retorno após o update foi o hexadecimal 0x01 que significa que o número 1 (basta fazer um CAST para converter esse valor para inteiro) mostrando que a primeira coluna foi alterada. Vejamos um segundo exemplo:

— Faz três alterações
UPDATE T SET C2 = 0

UPDATE T SET C3 = 0
UPDATE T SET C4 = 0

— Converte o retorno
SELECT CAST(0x02 As INT) As C2, CAST(0x04 As INT) As C3, CAST(0x08 As INT) As C4

Dessa vez os retornos foram respectivamente 2, 4, 8. A função COLUMNS_UPDATE não retorna a posição da coluna alterada, mas sim um hexadecimal que quando convertido mostra uma combinação de valores em potência de 2. Se formos capturar os retornos das atualizações temos a seguinte lógica.

Retorno Inteiro Potência Identificação Coluna
0x01 1 20 0 + 1 1
0x02 2 21 1 + 1 2
0x04 4 22 1 + 2 3
0x08 8 23 1 + 3 4

A lógica é a seguinte converte-se o valor em hexadecimal para um número inteiro. Obtêm-se a potência de base 2 que retorne esse inteiro. Extrai-se o expoente e incrementa-se uma unidade para obter o número da coluna alterada. Ex: O hexadecimal 0x08 quando convertido para inteiro retorna 8. O número 8 representa 2 elevado a 3. O expoente 3 somando mais uma unidade mostra que a quarta coluna foi alterada. Vejamos agora o que acontece quando múltiplas colunas são alteradas.

— Faz uma alteração de duas colunas
UPDATE T SET C1 = 0, C2 = 0
UPDATE T SET C3 = 0, C5 = 0
UPDATE T SET C2 = 0, C8 = 0

— Converte os retornos
SELECT CAST(0x03 As INT) As U1, CAST(0x14 As INT) As U2, CAST(0x82 As INT) As U3

Quando apenas uma coluna é atualizada é mais fácil identificá-las, mas quando duas ou mais colunas são atualizadas, as coisas ficam mais complicadas. Vejamos a lógica nesse caso.

Retorno Inteiro Potência Identificação Colunas
0x03 3 20 + 21 0 e 1 1 e 2
0x14 20 22 + 24 2 e 4 3 e 5
0x82 130 21 + 27 1 e 7 2 e 8

De fato a lógica é um pouco complexa, mas vejamos agora uma forma de retornar exatamente o número das colunas afetadas na trigger.

— Altera a trigger
ALTER TRIGGER TR ON T
FOR UPDATE
AS
DECLARE
@Num INT = COLUMNS_UPDATED(), @Inc SMALLINT = 1, @Cols VARCHAR(20) =

WHILE (@Num > 0)
BEGIN
    IF
(@Num < POWER(2,@Inc))
    BEGIN
        SET
@Cols = @Cols + CAST(@Inc As VARCHAR(4)) + ‘;’
        SELECT @Num = @Num – POWER(2,@Inc – 1), @Inc = 0
    END
SET @Inc = @Inc + 1
END

PRINT @Cols

— Faz uma alteração de várias colunas
UPDATE T SET C1 = 0
UPDATE T SET C1 = 0, C2 = 0, C3 = 0
UPDATE T SET C4 = 0, C6 = 0, C7 = 0, C8 = 0

O retornos "1;" , "3;2;1;" e "8;7;6;4;" mostram a relação de colunas alteradas de acordo com as instruções de UPDATE realizadas. Vejamos agora como a trigger se comporta se uma nova coluna for adicionada deixando a tabela com nove colunas.

— Adiciona uma nova coluna
ALTER TABLE T ADD C9 INT

— Faz os mesmo UPDATEs que os anteriores
UPDATE T SET C1 = 0
UPDATE T SET C1 = 0, C2 = 0, C3 = 0
UPDATE T SET C4 = 0, C6 = 0, C7 = 0, C8 = 0

Dessa vez os retornos foram bem diferentes do esperado.

Colunas Alteradas Retorno Esperado Resultado
C1 1; 9;
C1, C2, C3 3;2;1; 11;10;9;
C4, C6, C7, C8 8;7;6;4 16;15;14;12;

De fato, quando existem mais de oito colunas, a função COLUMNS_UPDATED se comporta um pouco diferente conforme detalhado no KB 232195 da Microsoft. Não vou entrar no mérito das modificações, mas uma modificação na trigger pode contemplar esse detalhe (adaptado de Trigger to detect columns updated – Columns_updated() more than 32 fields):

— Altera a trigger
ALTER TRIGGER TR ON T
FOR UPDATE
AS
DECLARE
@Col INT, @Cols VARCHAR(20), @qCols INT
DECLARE @bitVerificador INT, @Pot INT

SET @Col = 0
SET @Cols =

— Conta quantas colunas existem na tabela contemplada pela Trigger
SET @qCols = (SELECT COUNT(*) FROM sys.columns WHERE object_id =
    (SELECT Parent_ID FROM sys.triggers WHERE object_id = @@procid))

WHILE (@Col < @qCols)
BEGIN
    SET @Col = @Col + 1
    SET @Pot = (@Col – 1) % 8 + 1
    SET @Pot = POWER(2,@Pot – 1)
    SET @bitVerificador = ((@Col – 1) / 8) + 1
    IF (SUBSTRING(COLUMNS_UPDATED(),@bitVerificador, 1) & @Pot > 0)
        SET @Cols = @Cols + CAST(@Col As VARCHAR(2)) + ‘;’
END

PRINT @Cols

— Efetua novos updates
UPDATE T SET C1 = 0
UPDATE T SET C4 = 0, C6 = 0, C7 = 0, C8 = 0
UPDATE T SET C4 = 0, C6 = 0, C7 = 0, C8 = 0, C9 = 0

— Adiciona novas colunas
ALTER TABLE T ADD C10 INT, C11 INT

— Efetua novos updates
UPDATE T SET C5 = 0
UPDATE T SET C6 = 0, C3 = 0, C9 = 0, C10 = 0
UPDATE T SET C2 = 0, C7 = 0, C8 = 0, C11 = 0, C10 = 0

— Elimina as colunas adicionais
ALTER TABLE T DROP COLUMN C9
ALTER TABLE T DROP COLUMN C10
ALTER TABLE T DROP COLUMN C11

— Efetua novos updates
UPDATE T SET C1 = 0
UPDATE T SET C4 = 0, C6 = 0, C7 = 0, C8 = 0

Após as alterações, a trigger fica adaptada para retornar a relação de colunas afetadas independente da quantidade de colunas que a tabela possuir. O interessante é que inclusões e exclusões de colunas não alteram o funcionamento da trigger dispensando inclusive a necessidade de recompilá-la. Os exemplos citados serviram para explicar o funcionamento da função COLUMNS_UPDATED. Uma vez que a numeração das colunas alteradas seja conhecida, fica fácil obter seus nomes com um JOIN contra a sys.columns. O código a seguir faz essa alteração:

— Altera a trigger
ALTER TRIGGER TR ON T
FOR UPDATE
AS
DECLARE
@Col INT, @Cols VARCHAR(1000), @qCols INT, @NomeCol VARCHAR(50)
DECLARE @bitVerificador INT, @Pot INT

SET @Col = 0
SET @Cols =

— Conta quantas colunas existem na tabela contemplada pela Trigger
SET @qCols = (SELECT COUNT(*) FROM sys.columns WHERE object_id =
    (SELECT Parent_ID FROM sys.triggers WHERE object_id = @@procid))

WHILE (@Col < @qCols)
BEGIN
    SET @Col = @Col + 1
    SET @Pot = (@Col – 1) % 8 + 1
    SET @Pot = POWER(2,@Pot – 1)
    SET @bitVerificador = ((@Col – 1) / 8) + 1
    IF (SUBSTRING(COLUMNS_UPDATED(),@bitVerificador, 1) & @Pot > 0)
    BEGIN
        SET @NomeCol = (
            SELECT Name FROM sys.columns WHERE object_id =
                (SELECT Parent_ID FROM sys.triggers
                WHERE object_id = @@procid) AND column_id = @Col)
        SET @Cols = @Cols + @NomeCol + ‘;’
    END
END

PRINT @Cols

— Efetua novos updates
UPDATE T SET C1 = 0
UPDATE T SET C4 = 0, C6 = 0, C7 = 0, C8 = 0

O retorno da trigger mostra o nome das colunas alteradas:

C1;
(1 row(s) affected)

C4;C6;C7;C8;
(1 row(s) affected)

Agora vejamos como utilizar o raciocínio da COLUMNS_UPDATED no exemplo de negócio no início do artigo.

— Altera a trigger de atualização na tabela Operação Financeira
ALTER TRIGGER trgAU_AudOF ON OperacaoFinanceira
FOR UPDATE
AS

DECLARE @Col INT, @qCols INT, @NomeCol VARCHAR(50)
DECLARE @bitVerificador INT, @Pot INT

SET @Col = 0

— Conta quantas colunas existem na tabela contemplada pela Trigger
SET @qCols = (SELECT COUNT(*) FROM sys.columns WHERE object_id =
    (SELECT Parent_ID FROM sys.triggers WHERE object_id = @@procid))

WHILE (@Col < @qCols)
BEGIN
    SET @Col = @Col + 1
    SET @Pot = (@Col – 1) % 8 + 1
    SET @Pot = POWER(2,@Pot – 1)
    SET @bitVerificador = ((@Col – 1) / 8) + 1
    IF (SUBSTRING(COLUMNS_UPDATED(),@bitVerificador, 1) & @Pot > 0)
    BEGIN
        SET @NomeCol = (
            SELECT Name FROM sys.columns WHERE object_id =
                (SELECT Parent_ID FROM sys.triggers
                WHERE object_id = @@procid) AND column_id = @Col)

        INSERT INTO AudOF (IDOperacaoFinanceira, DataAlteracao, Coluna)
        SELECT INS.IDOperacaoFinanceira, GETDATE(), @NomeCol FROM Inserted As Ins
    END
END

— Atualiza a coluna Valor cuja operação financeira tenha o ID igual a 25
UPDATE OperacaoFinanceira SET Valor = 650.01 WHERE IDOperacaoFinanceira = 25

Dessa vez a trigger foi capaz de capturar a coluna alterada, sem a necessidade de se especificar o nome dela através da função UPDATE apresentada na implementação anterior:

IDAudOF IDOperacaoFinanceira DataAlteracao Coluna ValorAntigo ValorNovo
7 25 2010-05-06 14:49:44.913 Valor NULL NULL

O único problema é que a trigger capturou apenas o nome da coluna, mas não há referências aos valores anterior e posterior ao update. É sabido que o nome da coluna é conhecido, mas não há como recuperá-lo de forma direta. As construções abaixo parecem lógicas, mas não funcionam:

SELECT @Col FROM INSERTED
EXEC (‘SELECT ‘ + @Col + ‘ FROM INSERTED’)

O problema com a primeira construção é que @Col é uma variável e o SELECT retorna o valor da variável e não o valor da coluna cujo nome representa o valor da variável (uma instrução SELECT ‘A’ FROM INSERTED retorna a string ‘A’ assim como uma instrução SELECT @Col FROM INSERTED retorna o valor de @Col que no caso é o nome da coluna e não o seu valor). A segunda construção monta uma SQL dinâmica para executar o comando. A montagem em si está correta, mas o uso do EXEC abre uma sessão a parte e como as tabelas INSERTED e DELETED têm escopo por sessão e por isso não funcionam. Existem algumas formas de resolver isso com tabelas temporárias globais e XML. Particularmente já acho triggers pesadas e não acho interessante colocar uma carga extra envolvendo tabelas temporárias (sobretudo as globais). A versão final da trigger mostra como utilizar o XML para capturar o valor da coluna alterada.

— Altera a trigger de atualização na tabela Operação Financeira
ALTER TRIGGER trgAU_AudOF ON OperacaoFinanceira
FOR UPDATE
AS
DECLARE @Col INT, @qCols INT, @NomeCol VARCHAR(50)
DECLARE @bitVerificador INT, @Pot INT

SET @Col = 0

— Conta quantas colunas existem na tabela contemplada pela Trigger
SET @qCols = (SELECT COUNT(*) FROM sys.columns WHERE object_id =
    (SELECT Parent_ID FROM sys.triggers WHERE object_id = @@procid))

— Coloca a tabela Deleted em uma variável XML
DECLARE @Deleted XML, @DeletedTMP XML
SET @Deleted = (SELECT * FROM Deleted FOR XML RAW, ROOT(‘Deleted’))

— Coloca a tabela Inserted em uma variável XML
DECLARE @Inserted XML, @InsertedTMP XML
SET @Inserted = (SELECT * FROM Inserted FOR XML RAW, ROOT(‘Inserted’))

WHILE (@Col < @qCols)
BEGIN
    SET @Col = @Col + 1
    SET @Pot = (@Col – 1) % 8 + 1
    SET @Pot = POWER(2,@Pot – 1)
    SET @bitVerificador = ((@Col – 1) / 8) + 1
    IF (SUBSTRING(COLUMNS_UPDATED(),@bitVerificador, 1) & @Pot > 0)
    BEGIN
        SET @NomeCol = (
            SELECT Name FROM sys.columns WHERE object_id =
                (SELECT Parent_ID FROM sys.triggers
                WHERE object_id = @@procid) AND column_id = @Col)

        — Substitui a TAG no XML da DELETED e faz a extração dos dados
        SET @DeletedTMP = REPLACE(CAST(@Deleted As VARCHAR(MAX)),@NomeCol + ‘="’,‘Col="’)

        — Substitui a TAG no XML da INSERTED e faz a extração dos dados
        SET @InsertedTMP = REPLACE(CAST(@Inserted As VARCHAR(MAX)),@NomeCol + ‘="’,‘Col="’)

        INSERT INTO AudOF (IDOperacaoFinanceira, DataAlteracao, Coluna, ValorAntigo, ValorNovo)
       
SELECT INS.IDOperacaoFinanceira, GETDATE(), @NomeCol,
            (SELECT E.e.value(
                (/Deleted/row[@IDOperacaoFinanceira = sql:column("INS.IDOperacaoFinanceira")]/@Col)[1]’
,‘varchar(100)’)
            FROM @DeletedTMP.nodes(‘.’) E(e)) As ValorAntigo,
            (SELECT E.e.value(
                (/Inserted/row[@IDOperacaoFinanceira = sql:column("INS.IDOperacaoFinanceira")]/@Col)[1]’
,‘varchar(100)’)
            FROM @InsertedTMP.nodes(‘.’) E(e)) As ValorNovo
        FROM Inserted As Ins

    END
END

— Atualiza as colunas CodStatusOpFin e IDUsuario cuja operação financeira tenha o ID igual a 27
UPDATE OperacaoFinanceira SET CodStatusOpFin = 4, IDUsuario = ‘Teles_0003’ WHERE IDOperacaoFinanceira = 27

— Atualiza a coluna IDProduto onde as operações financeiras tenham data de operação igual ou superior a 03/05/2010
UPDATE OperacaoFinanceira SET IDProduto = 21 WHERE DataOpFinanceira >= ‘20100503’

Após as modificações, a trigger foi capaz de capturar todas as colunas alteradas com seus valores anteriores e posteriores ao update conforme a tabela abaixo:

IDAudOF IDOperacaoFinanceira DataAlteracao Coluna ValorAntigo ValorNovo
8 27 2010-05-06 19:00:57.730 CodStatusOpFin 3 4
9 27 2010-05-06 19:00:57.730 IDUsuario Renam_0003 Teles_0003
10 29 2010-05-06 19:01:11.297 IDProduto 18 21
11 28 2010-05-06 19:01:11.297 IDProduto 11 21

Até então, a trigger da forma como está é bem dinâmica não necessitando informar as colunas existentes uma a uma. O último teste adicionará uma coluna à tabela e verificará como a trigger se comporta.

— Adiciona mais uma coluna na tabela OperacaoFinanceira
ALTER TABLE OperacaoFinanceira ADD IDInstituicao INT

— Atualiza todos os registros
UPDATE OperacaoFinanceira SET IDInstituicao = 1

— Verifica a tabela de Auditoria
SELECT IDAudOF, IDOperacaoFinanceira, DataAlteracao, Coluna, ValorAntigo, ValorNovo
FROM AudOF WHERE Coluna = ‘IDInstituicao’ 

IDAudOF IDOperacaoFinanceira DataAlteracao Coluna ValorAntigo ValorNovo
12 29 2010-05-06 19:02:55.857 IDInstituicao NULL 1
13 28 2010-05-06 19:02:55.857 IDInstituicao NULL 1
14 27 2010-05-06 19:02:55.857 IDInstituicao NULL 1
15 26 2010-05-06 19:02:55.857 IDInstituicao NULL 1
16 25 2010-05-06 19:02:55.857 IDInstituicao NULL 1

Após todo esse código TSQL, a trigger está pronta. Sua última proposta adapta-se totalmente a adição e exclusão de novas colunas conseguindo capturar perfeitamente a(s) coluna(s) alterada(s) bem como o(s) valor(es) antigo(s) e novo(s) sem a necessidade de informar previamente a relação de colunas da tabela evitando assim um custo de manutenção adicional nas triggers quando houver alterações na tabela de origem.

Embora o código seja bem eficaz não posso classificá-lo como eficiente. Se for feita uma análise superficial vemos que a trigger tem um processamento razoável (loops, consultas a tabelas de sistemas, conversões de tabelas para XML, etc). Desaconselho completamente a implementação dessa trigger em tabelas que sofram muitos updates principalmente se possuírem muitas colunas, pois, isso pode incorrer em problemas de desempenho. Aconselho a utilização de recursos mais efetivos como o AUDIT, CDC (Change Data Capture) e o CT (Change Tracking). São mais fáceis de entender e certamente mais performáticos.

[ ]s,

Gustavo

2 Respostas para “Como monitorar e logar as colunas que foram atualizadas ?

  1. Excelente Artigo, parabéns.Sugeri a implantação em meu ambiente, reduzindo o consumo de recursos do servidor e da Storage, sem contar a economia de tempo em casos de alteração estrutural de tabelas.Parabéns.Mura

  2. Oi Ricardo,Que bom que a minha implementação atendeu os seus requisitos. Eu já tinha procurado algo assim na NET mas nunca tinha encontrado nada parecido. Resolvi criar na mão mesmo.[ ]s,

Deixe uma resposta

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s