Usuário com melhor resposta
Trigger para Log de alteração

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!
Respostas
-
Boa Tarde,
Normalmente o código abaixo seria suficiente:
Code SnippetCREATE
TRIGGER AuditProdutoVendidoON
[produtoVendido]FOR
UPDATEAS
BEGININSERT 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
-
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 UPDATEAS 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
Todas as Respostas
-
Boa Tarde,
Normalmente o código abaixo seria suficiente:
Code SnippetCREATE
TRIGGER AuditProdutoVendidoON
[produtoVendido]FOR
UPDATEAS
BEGININSERT 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
-
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
-
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).
-
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 UPDATEAS 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
-
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