Casos do Dia a Dia – Trigger para descobrir como um registro é inserido, alterado e deletado
Fala Pessoal,
Após um longo tempo, segue mais um post com um caso do dia a dia que aconteceu comigo.
Onde trabalho, estavam tentando descobrir o que estava alterando dados de uma tabela e como isso estava sendo feito. Uma das formas de fazer isso era criando um trace, contudo, decidi criar uma trigger, visto que essa tabela não tinha muita manipulação de dados.
Para visualizar o que essa trigger faz, criei as duas tabelas abaixo:
CREATE TABLE TesteTrigger(
Id_TesteTrigger int IDENTITY,
DATA datetime
)
CREATE TABLE [dbo].[TesteTrigger_Log](
Operacao VARCHAR(50),
session_id [smallint] NOT NULL,
[login_name] [nvarchar](128) NOT NULL,
hostname VARCHAR(200),
[start_time] [datetime] NOT NULL,
[program_name] [nvarchar](128) NULL,
[Query] [nvarchar](max) NULL,
Id_TesteTrigger [int] NOT NULL — chave primaria da tabela
) ON [PRIMARY]
Em seguida, executei o script abaixo para criar a trigger:
CREATE TRIGGER [dbo].[trgTesteTrigger]
ON [dbo].TesteTrigger
FOR UPDATE, DELETE , INSERT
AS
BEGIN
SET NOCOUNT ON;
DECLARE @Id_TesteTrigger INT, @Tp_Alteracao VARCHAR(50)
CREATE TABLE #log(eventtype VARCHAR(MAX),parameters int,text VARCHAR(MAX))
INSERT INTO #log
EXEC(‘DBCC INPUTBUFFER(@@spid)’)
if not exists(select top 1 null from inserted) –deleted
begin
select @Tp_Alteracao = ‘DELETE’, @Id_TesteTrigger =Id_TesteTrigger
from deleted
end
else if not exists(select top 1 null from deleted) –inserted
begin
select @Tp_Alteracao =’INSERT’,@Id_TesteTrigger =Id_TesteTrigger
from inserted
end
else
begin –update
select @Tp_Alteracao =’UPDATE’,@Id_TesteTrigger =Id_TesteTrigger
from deleted
end
INSERT INTO TesteTrigger_Log(Operacao,session_id,login_name,hostname,start_time, program_name,Query,
Id_TesteTrigger)
SELECT @Tp_Alteracao,A.session_id,login_name,host_name,B.start_time, program_name,
(SELECT text FROM #log) QueryAtual,
@Id_TesteTrigger
from sys.dm_exec_sessions A
JOIN sys.dm_exec_requests B on A.session_id = B.session_id
JOIN sys.dm_exec_connections C on B.session_id = C.session_id
where A.session_id = @@spid
END
Para validar o log gerado pela trigger, basta executar um insert, um update e um delete:
INSERT INTO testetrigger SELECT GETDATE()
UPDATE TesteTrigger SET DATA = GETDATE()-1
DELETE FROM TesteTrigger
Segue abaixo o resultado armazenado pela trigger:
Destaque para os retornos abaixo:
A coluna Query armazena a query que está realizando a alteração. Se for uma procedure, aparece o nome da procedure.
A coluna Program_Name diz se é um comando executado do SSMS ou de uma aplicação.
Id_TesteTrigger é o Id do registro alterado.
Hostname é de onde o comando foi executado.
obs.: Lembrando que isso não é recomendado para uma tabela com uma manipulação grande de dados.
Espero ter ajudado.
Gostou dessa Dica?
Curta, comente, compartilhe…
Assine meu canal no Youtube e curta minha página no Facebook para receber Dicas de Leituras, Vídeos e Eventos sobre SQL Server.
Confira mais experiências do Dia a Dia de um DBA no meu Treinamento de Tarefas do Dia a Dia de um DBA.
Até a próxima.
Fabrício Lima
MCITP – Database Administrator
Consultor e Instrutor SQL Server
Trabalha com SQL Server desde 2006
Bom dia Fabricio, gostei deste post e na verdade, essa trigger que voce crio nada mais é do que uma pequena auditoria de quem está realizando alguma tarefa em cima de uma determinada tabela e é justamente o que eu estava desejando utilizar para o meu ambiente de trabalho, visto que ja aconteceu de alterarem alguns conteudos de campos em que nao descobrimos a autoria do feito. Com esta trigger em mãos e rodando, teremos um grande documento de tudo q é feito de um determinado usuario. Porém, existe um detalhe no meu caso: temos uma aplicação em que utiliza somente um usuário do SQL Server, ou seja, no hostname, ele pega o usuario do SQL Server ou o usuário do dominio? Se caso for a primeira opção, existe uma maneira de pegar o usuário do dominio ou ate mesmo, o nome da máquina? Porque eu tendo o nome da máquina, ja me ajudaria, pois cada usuário tem a sua máquina e saberia identificar quem executou aquela tarefa. No mais, seu blog está na minha lista de favoritos e parabens por compartilhar seus conhecimentos com todos, visto que são poucos que fazem isso, mas são poucos mesmo! A maioria gosta de guardar conhecimentos por medo de ser passado pra tras, mas… pena que pensam assim! Abraços e continue assim.
Obrigado José. Hostname pega o nome da máquina.
Faz um teste para validar no seu ambiente.
Abraços.
Fabricio,
é possível eu gerar uma trigger que pegue (insert, update, delete) do banco inteiro e não apenas de uma tabela?
Bom Dia Renata,
Esse tipo de trigger tem que ser criada por tabela.
a entendi, eu habilitei a auditoria pra pegar isso mas a function fn_get_audit_file não traz o nome da maquina e estamos tendo alguns problemas em uma base de dados que precisamos auditar.
Olá, como vai!
Achei o exemplo excelente e pretendo estabelecer na base de dados de um sistema de onde trabalho.
Eu apenas fiquei com a dúvida de porquê essa trigger não poderia ser usada em grandes manipulações de dados. O que seria classificado assim? Qual o problema que a trigger traria numa grande manipulação de dados?
Eu sou um pouco novato nessa área de gerencia e operação de BDs.
Bom Dia Elias,
Uma trigger em uma tabela muito grande com muito insert, update e delete pode causar problemas de performance. Além do insert normal que iria ser feito, ela tem que fazer a verificação da trigger e um outro insert na tabela de log. Aumentando bem o custo dessas operações.
Entendeu?
Boa tarde! O que vc indica para auditar tabelas com grandes movimentações DML? Que disponibilize o hostname do executor…
Boa tarde,
Toda auditoria tem um custo para auditar, então só conhecendo o cenário para pode indicar ou não.
Boa tarde Fabrício, parabéns pela trigger será de grande valia em um sistema qual estou dando manutenção, e gostaria de saber se é possível não tenho muito conhecimento em triggers, de montar uma de forma a atualizar mais de uma tabela no momento em que foi executada ?
Exemplo:
Em uma tabela com + de 50.000 reg até então atualizada parcialmente, porque a trigger ficou incompleta, agora preciso atualizar o que ficou incompleto e os próximos registros, até ai tudo bem, via programação cria-se a rotina e executa, mas estou batendo na tecla que mesmo com a trigger é possível atualizar tudo sem precisar criar programa.
Bom, você teria um exemplo deste tipo de trigger ou um e-mail qual possa enviar-lhe o que já fiz, porque trigger roda mas não atualiza, ou se for o caso meu e-mail é [email protected].
São 3 tabelas envolvidas na transação, duas se relaciona por chave principal e outra será via resultado de consulta em determinado campo, que é chave principal em outra tabela..
Abraço,
Aguardo.
Ola,
Obrigado.
Dentro de uma trigger consegue inserir em outras tabelas se é o que entendi que perguntou.
Nao tenho exemplo disso pronto, mas da uma olhada em materiais com trigger que deve ter. Cria um lab menor de teste.
Sempre me salvando!!! Monstro!!!!
kkkkkk
Obrigado.