Aguarde… Carregando

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?

Cadastre seu e-mail para receber novos Posts e curta minha Página no Facebook para receber Dicas de Leituras 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.

Abraços,

Fabrício Lima

MCITP – Database Administrator

Consultor e Instrutor SQL Server

Trabalha com SQL Server desde 2006

7 thoughts on “Casos do Dia a Dia – Trigger para descobrir como um registro é inserido, alterado e deletado

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

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

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

    1. 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?

Deixe uma resposta

%d blogueiros gostam disto: