none
Problema numa trigger update RRS feed

  • Pergunta

  • Olá pessoal,  fiz uma trigger para ser disparada no momento de uma alteração, com o propósito de que, somente os campos alterados sejam inseridos numa espécie de tabela de auditoria.

    Só que quando ocorre alteração de apenas um campo por exemplo, a tabela de auditoria é alimentada por todos os campos da tabela alterada, e não do único campo que alterei.

    O corpo da trigger está seguindo esse modelo:

    ALTER TRIGGER tr_Tabela_Alteracao
    ON Tabela
    AFTER UPDATE

    IF UPDATE(campo1)
    BEGIN
        insert TabelaAuditoria...
    END

    IF UPDATE(Campo2)
    BEGIN
    insert TabelaAuditoria...
    END

    IF UPDATE(Campo3)
    BEGIN
    insert TabelaAuditoria...
    END

    :: Como eu poderia resolver esse problema?

    sexta-feira, 5 de outubro de 2012 20:05

Respostas

  • Júnior,

    em primeiro lugar,s e seu objetivo é auditar os dados que foram alterados, eu recomendaria usar os recursos nativos do SQL Server, como por exemplo o CDC (Change Data Capture), que é exatamente pra isso. Porém, este mecanismo só está disponível nas edições Enterprise e Developer do SQL.

    Outra opção nativa do SQL que, dependendo do seu objetivo, pode ser mais interessante, é o mecanismo Change Tracking. COm ele você também consegue descobrir quais colunas da sua tabela foram alteradas. Contudo, este mecanismo não permite conhecer os dados existentes antes de uma alteração. Isso quem faz é o CDC. Mas por outro lado, o Change Tracking está disponível em qualquer edição do SQL 2008. Inclusive na Express (gratuita).

    Bem, agora que já lhe informei que você tem mecanismos nativos que talvez possam ser mais interessantes (até mesmo por questões de performance), caso seja realmente necessário fazer esta auditoria através de Triggers, precisamos saber como está a sua instrução Insert (você só postou o início).

    Ou seja, se o Campo1 tiver sido atualizado, o que você quer fazer? Quer inserir somente o conteúdo deste campo na tabela de auditoria? você quer incluir na auditoria somente o valor existente antes da alteração ou também o novo valor?

    Coloque o Insert completo. Isso nos ajudará a entendermos a melhor forma de se chegar ao resultado esperado por você.


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    • Marcado como Resposta Harley Araujo segunda-feira, 8 de outubro de 2012 11:56
    sábado, 6 de outubro de 2012 12:05
    Moderador

Todas as Respostas

  • Júnior,

    em primeiro lugar,s e seu objetivo é auditar os dados que foram alterados, eu recomendaria usar os recursos nativos do SQL Server, como por exemplo o CDC (Change Data Capture), que é exatamente pra isso. Porém, este mecanismo só está disponível nas edições Enterprise e Developer do SQL.

    Outra opção nativa do SQL que, dependendo do seu objetivo, pode ser mais interessante, é o mecanismo Change Tracking. COm ele você também consegue descobrir quais colunas da sua tabela foram alteradas. Contudo, este mecanismo não permite conhecer os dados existentes antes de uma alteração. Isso quem faz é o CDC. Mas por outro lado, o Change Tracking está disponível em qualquer edição do SQL 2008. Inclusive na Express (gratuita).

    Bem, agora que já lhe informei que você tem mecanismos nativos que talvez possam ser mais interessantes (até mesmo por questões de performance), caso seja realmente necessário fazer esta auditoria através de Triggers, precisamos saber como está a sua instrução Insert (você só postou o início).

    Ou seja, se o Campo1 tiver sido atualizado, o que você quer fazer? Quer inserir somente o conteúdo deste campo na tabela de auditoria? você quer incluir na auditoria somente o valor existente antes da alteração ou também o novo valor?

    Coloque o Insert completo. Isso nos ajudará a entendermos a melhor forma de se chegar ao resultado esperado por você.


    Roberson Ferreira - Database Developer
    Acesse: www.robersonferreira.com.br
    Email: contato@robersonferreira.com.br

    Se esta sugestão for útil, por favor, classifique-a como útil.
    Se ela lhe ajudar a resolver o problema, por favor, marque-a como Resposta.

    • Marcado como Resposta Harley Araujo segunda-feira, 8 de outubro de 2012 11:56
    sábado, 6 de outubro de 2012 12:05
    Moderador
  • Bom dia Roberson Ferreira,

    Realmente as ferramentas citadas seria mais conveniente, porém eu não tenho ainda conhecimento dessas tecnologias, e terei que usar triggers.

    A estrutura da trigger completa, para melhor compreensão seria assim:

    CREATE TRIGGER tr_Clientes_Alteracao
    ON Clientes
    AFTER UPDATE
    AS
    DECLARE @ID INTEGER;
    DECLARE @IDMAXIMO INTEGER;
    set @IDMAXIMO = 0;
    set @ID = 0;

    -- Verifica se a atualização foi em um determinado campo
    IF UPDATE(Cli_NOME)
    BEGIN
        --inserir na tabela de auditoria-cabeçalho (LogCadastro)
        IF @IDMAXIMO = 0
        BEGIN
            insert LogCadastro
            select (select CONVERT(char,getDate(),101)),(select convert(char(10),getdate(),108)),cli_codcli,'Clientes','A'
            from inserted
            SELECT @ID = SCOPE_IDENTITY () -- A variável @ID recebe o último contador da tabela LogCadastro
            set @IDMAXIMO = @ID
        END
        --inserir na tabela de auditoria-detalhe (LogCadastroDetalhe)
        if (select Cli_NOME from deleted)<> (select Cli_NOME from inserted)     -- se houver diferença, é porque ocorreu alteração
        BEGIN
            insert into LogCadastroDetalhe
            (lcd_idlogcadastro , lcd_campo ,lcd_valorold,lcd_valornew)  
            values (@ID ,'Cli_NOME',(select Cli_NOME from deleted),(select Cli_NOME from inserted))
        END
    END
    IF UPDATE(Cli_CGC)
    BEGIN
        --inserir na tabela de auditoria-cabeçalho
        IF @IDMAXIMO = 0
        BEGIN
            insert LogCadastro
            select (select CONVERT(char,getDate(),101)),(select convert(char(10),getdate(),108)),cli_codcli,'Clientes',@USUARIO,'A'
            from inserted
            SELECT @ID = SCOPE_IDENTITY () -- ATRIBUINDO O VALOR DO CONTADOR INSERIDO
            set @IDMAXIMO = @ID
        END
        --inserir no detalhe
        if (select Cli_CGC from deleted)<> (select Cli_CGC from inserted)  -- se houver diferença, é porque ocorreu alteração
        BEGIN
            insert into LogCadastroDetalhe
            (lcd_idlogcadastro , lcd_campo ,lcd_valorold,lcd_valornew)  
            values (@ID ,'Cli_CGC',(select Cli_CGC from deleted),(select Cli_CGC from inserted))
        END
    END

    Desde já, muito obrigado!

    segunda-feira, 8 de outubro de 2012 12:54
  • Junior,

    Eu particularmente utilizaria o CDC, bem como, em algumas situações eu fiz uso de Trigger, mas a facilidade de utilização do CDC é muito grande.

    Você poderá encontrar um artigo meu na Revista SQL Magazine, edição 85, publicado já fazem 2 anos, como também no meu Blog:

    Mas acredito que o exemplo abaixo poderá ajudar:

    -- Habilitando o CDC para o Banco de Dados --
    Use Artigos
    Go
    
    Exec sys.sp_cdc_enable_db
    Go
    
    -- Desabilitando o CDC para o Banco de Dados --
    Use Artigos
    Go
    
    Exec sys.sp_cdc_disable_db
    Go
    
    -- Criando a Tabela de Exemplo --
    Create Table Produtos
     (Codigo Int Identity(1,1),
       Descricao VarChar(20))
    Go
    
    -- Adicionando a Chave Primaria --
    Alter Table Produtos
        Add Constraint [PK_Codigo_Produtos] Primary Key (Codigo)
    Go
    
    -- Inserindo a Massa de Registros para Teste --   
    Declare @ContadorRegistros Int
    Set @ContadorRegistros=1
    
    While @ContadorRegistros <=1000
     Begin
     
      If @ContadorRegistros =1
       Insert Into Produtos Values ('Produto Nº: 1')
      Else
       Insert Into Produtos Values ('Produto Nº: '+Convert(VarChar(4),@@Identity+1))
       
       Set @ContadorRegistros += 1;
     End
     
    -- Visualizando os Dados --   
    Select * from Produtos
          
    -- Habilitando o Change Data Capture para trabalhar sobre a table Produtos --
    EXECUTE sys.sp_cdc_enable_table
        @source_schema = N'dbo',
        @source_name = N'Produtos',
        @role_name = N'cdc_Admin';
    GO
    
    -- Retornando todas as linhas capturadas pelo CDC --
    DECLARE @from_lsn binary(10), 
                      @to_lsn binary(10)
    
    SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Produtos')
    SET @to_lsn   = sys.fn_cdc_get_max_lsn()
    
    SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Produtos
      (@from_lsn, @to_lsn, N'all')
    Go
    
    -- Inserindo novos dados --
    Insert Into Produtos Values ('Produto Nº: '+Convert(VarChar(4),@@Identity+1))
    Go
    
    -- Atualizando dados já existentes --
    Update Produtos
    Set Descricao= Descricao+' - Upd'
    Where Codigo Between 11 And 21
    Go
    
    -- Retornando todas as linhas capturadas pelo CDC com Net Changes--
    DECLARE @from_lsn binary(10), 
                      @to_lsn binary(20)
    
    SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Produtos')
    SET @to_lsn   = sys.fn_cdc_get_max_lsn()
    
    SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Produtos
                                  (@from_lsn, @to_lsn, N'all')
    GO
    
    -- Retornando as colunas utilizadas pelo CDC para Captura --
    Execute sys.sp_cdc_get_captured_columns 
                             @capture_instance = N'dbo_Produtos';
    Go
    
    -- Retornando informações de configuração da captura de dados de alteração de uma tabela específica --
    Execute sys.sp_cdc_help_change_data_capture 
                              @source_schema = N'dbo', 
                              @source_name = N'Produtos';
    Go
    
    -- Retornando informações de configuração da captura de dados de alteração de todas as tabelas --
    EXECUTE sys.sp_cdc_help_change_data_capture;


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]

    segunda-feira, 8 de outubro de 2012 13:05
  • Bom dia Junior Galvão - MVP,

    Realmente seria melhor usar o CDC, o Roberson Ferreira tinha até me falado sobre essa melhor forma de realizar auditoria.
    Mais no momento, terei que ficar limitado as triggers, pois os clientes usarão a versão Express do sql.

    segunda-feira, 8 de outubro de 2012 13:33