Trigger para Log de alteração

Question

 

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!

Answer 1

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

Answer 2

 

 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

Answer 3

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

 

Answer 4

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

Answer 5

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