none
Trigger para Log de alteração RRS feed

  • Pergunta

  •  

    Galera estou com dificuldade em criar uma Trigger que grave para mim um Log de alteração de uma tabela

    tenho esta tabela ...

     

    CREATE TABLE [produtoVendido] (
     [numreq] [char] (6) NULL ,
     [seqit] [char] (3) NULL ,
     [codProduto] [varchar] (6) NULL ,
     [unidade] [varchar] (2) NULL ,
     [nome] [varchar] (50) NULL ,
     [qtdade] [decimal](10, 3) NULL CONSTRAINT [DF_ITEMVDA_qtdade] DEFAULT (0),
     [preco] [decimal](12, 2) NULL CONSTRAINT [DF_TEMVDA_preco] DEFAULT (0),
     [total] [decimal](12, 2) NULL CONSTRAINT [DF_TEMVDA_total] DEFAULT (0),
     [dataEmissao] [datetime] NULL ,
     [dthAtualizacao] [datetime] NULL ,
     [codOperadorEditou] [varchar] (5) COLLATE Latin1_General_CI_AS NULL
    ) ON [PRIMARY]

    e a cada Update queria que o banco insirá os dados na segunte tabela

     


    CREATE TABLE [Log_produtoVendido] (
     [numreq_antes] [char] (6) NULL ,
     [seqit_antes] [char] (3) NULL ,
     [codProduto_antes] [varchar] (6) NULL ,
     [unidade_antes] [varchar] (2) NULL ,
     [nome_antes] [varchar] (50) NULL ,
     [qtdade_antes] [decimal](10, 3) NULL CONSTRAINT [DF_ITEMVDA_qtdade] DEFAULT (0),
     [preco_antes] [decimal](12, 2) NULL CONSTRAINT [DF_TEMVDA_preco] DEFAULT (0),
     [total_antes] [decimal](12, 2) NULL CONSTRAINT [DF_TEMVDA_total] DEFAULT (0),
     [dataEmissao_antes] [datetime] NULL ,
     [dthAtualizacao_antes] [datetime] NULL ,
     [codOperadorEditou_antes] [varchar] (5) COLLATE Latin1_General_CI_AS NULL,


     [numreq_depois]      [char] (6) NULL ,
     [seqit_depois]       [char] (3) NULL ,
     [codProduto_depois]  [varchar] (6) NULL ,
     [unidade_depois]     [varchar] (2) NULL ,
     [nome_depois]        [varchar] (50) NULL ,
     [qtdade_depois]      [decimal](10, 3) NULL CONSTRAINT [DF_ITEMVDA_qtdade] DEFAULT (0),
     [preco_depois]       [decimal](12, 2) NULL CONSTRAINT [DF_TEMVDA_preco] DEFAULT (0),
     [total_depois]       [decimal](12, 2) NULL CONSTRAINT [DF_TEMVDA_total] DEFAULT (0),
     [dataEmissao_depois] [datetime] NULL ,
     [dthAtualizacao_depois] [datetime] NULL ,
     [codOperadorEditou_depois] [varchar] (5) COLLATE Latin1_General_CI_AS NULL
    ) ON [PRIMARY]

     

    assim teria os dados de  antes e depois do Update!

    quinta-feira, 24 de janeiro de 2008 19:51

Respostas

  • Boa Tarde,

     

    Normalmente o código abaixo seria suficiente:

     

    Code Snippet

    CREATE TRIGGER AuditProdutoVendido

    ON [produtoVendido]

    FOR UPDATE

    AS BEGIN

     

    INSERT INTO [Log_produtoVendido] (

     

    [numreq_antes], [seqit_antes], [codProduto_antes], [unidade_antes],

    [nome_antes], [qtdade_antes], [preco_antes], [total_antes],

    [dataEmissao_antes], [dthAtualizacao_antes],[codOperadorEditou_antes],

     

    [numreq_depois], [seqit_depois], [codProduto_depois], [unidade_depois],

    [nome_depois], [qtdade_depois], [preco_depois], [total_depois],

    [dataEmissao_depois], [dthAtualizacao_depois],[codOperadorEditou_depois])

     

    SELECT

    INS.[numreq], INS.[seqit], INS.[codProduto], INS.[unidade],

    INS.[nome], INS.[qtdade], INS.[preco], INS.[total],

    INS.[dataEmissao], INS.[dthAtualizacao], INS.[codOperadorEditou],

     

    DEL.[numreq], DEL.[seqit], DEL.[codProduto], DEL.[unidade],

    DEL.[nome], DEL.[qtdade], DEL.[preco], DEL.[total],

    DEL.[dataEmissao], DEL.[dthAtualizacao], DEL.[codOperadorEditou]

     

    FROM

    INSERTED

    INNER JOIN DELETED ON INS.[numreq] = DEL.[numreq]

    END

     

     

    No entanto, existem questionamentos que gostaria de fazer antes de propriamente postar a solução.

     

    Qual é a chave primária da tabela ?

    Definir a chave primária da tabela é importante para relacionar o registro antes e depois. Imagino que seja numreq.

     

    A chave primária pode ser alterada ?

    Permitir a alteração de uma chave primária é um prática com muitas implicações (uma delas e a dificuldade em rastrear as mudanças na chaves e nas tabelas que a usam como FK).

     

    A trigger é apenas para update ?

    Não será necessário auditar o INSERT e o DELETE ?

     

    Existe a possibilidade de você alterar o esquema da tabela ?

    É possível introduzir novas colunas (um Identity por exemplo) em sua tabela ?

     

    [ ]s,

     

    Gustavo

    quinta-feira, 24 de janeiro de 2008 20:09
  • Gustavo, Obrigado pela atenção

     

    Utilizei do seu exemplo que me exclareceu muito! e funcionou eu apenas inverti o select da trigger deixando assim...

     

    CREATE TRIGGER AuditProdutoVendido ON [produtoVendido]
    FOR UPDATE

    AS BEGIN
    INSERT INTO Log_produtoVendido (
    [numreq_antes], [seqit_antes], [codProduto_antes], [unidade_antes],
    [nome_antes], [qtdade_antes], [preco_antes], [total_antes],
    [dataEmissao_antes], [dthAtualizacao_antes],[codOperadorEditou_antes],

    [numreq_depois], [seqit_depois], [codProduto_depois], [unidade_depois],
    [nome_depois], [qtdade_depois], [preco_depois], [total_depois],
    [dataEmissao_depois], [dthAtualizacao_depois],[codOperadorEditou_depois])

    SELECT

    DEL.[numreq], DEL.[seqit], DEL.[codProduto], DEL.[unidade],
    DEL.[nome], DEL.[qtdade], DEL.[preco], DEL.[total],
    DEL.[dataEmissao], DEL.[dthAtualizacao], DEL.[codOperadorEditou]

    INS.[numreq], INS.[seqit], INS.[codProduto], INS.[unidade],
    INS.[nome], INS.[qtdade], INS.[preco], INS.[total],
    INS.[dataEmissao], INS.[dthAtualizacao], INS.[codOperadorEditou],


    FROM INSERTED AS INS
    INNER JOIN DELETED AS DEL ON INS.[numreq] = DEL.[numreq]

    END


    Obrigado! estou muito agradecido!

    valeu galera

    sábado, 26 de janeiro de 2008 12:34

Todas as Respostas

  • Boa Tarde,

     

    Normalmente o código abaixo seria suficiente:

     

    Code Snippet

    CREATE TRIGGER AuditProdutoVendido

    ON [produtoVendido]

    FOR UPDATE

    AS BEGIN

     

    INSERT INTO [Log_produtoVendido] (

     

    [numreq_antes], [seqit_antes], [codProduto_antes], [unidade_antes],

    [nome_antes], [qtdade_antes], [preco_antes], [total_antes],

    [dataEmissao_antes], [dthAtualizacao_antes],[codOperadorEditou_antes],

     

    [numreq_depois], [seqit_depois], [codProduto_depois], [unidade_depois],

    [nome_depois], [qtdade_depois], [preco_depois], [total_depois],

    [dataEmissao_depois], [dthAtualizacao_depois],[codOperadorEditou_depois])

     

    SELECT

    INS.[numreq], INS.[seqit], INS.[codProduto], INS.[unidade],

    INS.[nome], INS.[qtdade], INS.[preco], INS.[total],

    INS.[dataEmissao], INS.[dthAtualizacao], INS.[codOperadorEditou],

     

    DEL.[numreq], DEL.[seqit], DEL.[codProduto], DEL.[unidade],

    DEL.[nome], DEL.[qtdade], DEL.[preco], DEL.[total],

    DEL.[dataEmissao], DEL.[dthAtualizacao], DEL.[codOperadorEditou]

     

    FROM

    INSERTED

    INNER JOIN DELETED ON INS.[numreq] = DEL.[numreq]

    END

     

     

    No entanto, existem questionamentos que gostaria de fazer antes de propriamente postar a solução.

     

    Qual é a chave primária da tabela ?

    Definir a chave primária da tabela é importante para relacionar o registro antes e depois. Imagino que seja numreq.

     

    A chave primária pode ser alterada ?

    Permitir a alteração de uma chave primária é um prática com muitas implicações (uma delas e a dificuldade em rastrear as mudanças na chaves e nas tabelas que a usam como FK).

     

    A trigger é apenas para update ?

    Não será necessário auditar o INSERT e o DELETE ?

     

    Existe a possibilidade de você alterar o esquema da tabela ?

    É possível introduzir novas colunas (um Identity por exemplo) em sua tabela ?

     

    [ ]s,

     

    Gustavo

    quinta-feira, 24 de janeiro de 2008 20:09
  •  

     Bom eu tenho outro conselho aplicar somente quando e necessario usar trigger ja nao e muito bom, ainda mais para estes tipos de caso, e logico que existem casos que nao podemos fugir da situacao, eu tenho feito da sequinte maneira:

     

     uma trigger para cada procedimento com Utr_Nome_Tabela_Insert, ...

     

     dentro desta trigger para pegar a acao e no nome da tabela uso o Object_Name com o @@procID entao tenho a tabela + o procedimento, na tabela de log criada especificamente para isso contem uma coluna para receber o acao usando o object_name, usuraio, data, cabecalhoID  e uma coluna varchar(max) ou se for sql 2000 varchar(xx) onde xx e o que resta dos 8000 bytes que vc. tem para alinha com as colunas contatenadas ( usando as conversoes ) separadas por | ( pipe ), em outra tabela tenho o cabecalho relacionado com a tabela de log, quando preciso auditar alguma coisa exporto a consulta do cabecalho + a tabela de log para um texto, como tenho delimitador abro este texto no excel e ai tenho como analisar com os recursos do excel mesmo.

     

    Nao sei de ajuda mais qualquer coisa retone.

     

    Abbs

    sexta-feira, 25 de janeiro de 2008 09:28
  • Denilson,

     

    As orientações postadas pelo Marcelo e também pelo Gustavo, são extremamente aconselháveis de se utilizar, principalmente se levarmos em consideração alguns critérios importantes quando se trabalho com trigger.

     

    Agora para resolver a sua dúvida, você vai precisar criar um trigger de Update alinhado com a table produtoVendido, mas fazendo insert na table Log_produtoVendido.

     

    Seguindo o exemplo de Gustavo você poderá fazer isso, o importante é entender que o Trigger criar durante seu processo de execução do table sendo elas: Inserted(contêm os novos valores) e Deleted(Contêm os valores antigos).

     

    sexta-feira, 25 de janeiro de 2008 10:15
  • Gustavo, Obrigado pela atenção

     

    Utilizei do seu exemplo que me exclareceu muito! e funcionou eu apenas inverti o select da trigger deixando assim...

     

    CREATE TRIGGER AuditProdutoVendido ON [produtoVendido]
    FOR UPDATE

    AS BEGIN
    INSERT INTO Log_produtoVendido (
    [numreq_antes], [seqit_antes], [codProduto_antes], [unidade_antes],
    [nome_antes], [qtdade_antes], [preco_antes], [total_antes],
    [dataEmissao_antes], [dthAtualizacao_antes],[codOperadorEditou_antes],

    [numreq_depois], [seqit_depois], [codProduto_depois], [unidade_depois],
    [nome_depois], [qtdade_depois], [preco_depois], [total_depois],
    [dataEmissao_depois], [dthAtualizacao_depois],[codOperadorEditou_depois])

    SELECT

    DEL.[numreq], DEL.[seqit], DEL.[codProduto], DEL.[unidade],
    DEL.[nome], DEL.[qtdade], DEL.[preco], DEL.[total],
    DEL.[dataEmissao], DEL.[dthAtualizacao], DEL.[codOperadorEditou]

    INS.[numreq], INS.[seqit], INS.[codProduto], INS.[unidade],
    INS.[nome], INS.[qtdade], INS.[preco], INS.[total],
    INS.[dataEmissao], INS.[dthAtualizacao], INS.[codOperadorEditou],


    FROM INSERTED AS INS
    INNER JOIN DELETED AS DEL ON INS.[numreq] = DEL.[numreq]

    END


    Obrigado! estou muito agradecido!

    valeu galera

    sábado, 26 de janeiro de 2008 12:34
  • Olá Denilson,

     

    Que bom que o meu script conseguiu ajudá-lo juntamente com a recomendação dos colegas. No entanto, acho que você sentirá uma certa necessidade de mudá-lo daqui a algum tempo por conta de algumas questões aqui colocadas. Se tiver mais dúvidas, basta postar.

     

    [ ]s,

     

    Gustavo

    sábado, 26 de janeiro de 2008 14:31