{"id":2313,"date":"2013-08-26T22:34:00","date_gmt":"2013-08-27T00:34:00","guid":{"rendered":"http:\/\/fabriciolima.net\/blog\/?p=2313"},"modified":"2017-02-04T14:29:33","modified_gmt":"2017-02-04T16:29:33","slug":"casos-do-dia-a-dia-trigger-para-descobrir-como-um-registro-e-inserido-alterado-e-deletado","status":"publish","type":"post","link":"https:\/\/fabriciolima.net\/blog\/2013\/08\/26\/casos-do-dia-a-dia-trigger-para-descobrir-como-um-registro-e-inserido-alterado-e-deletado\/","title":{"rendered":"Casos do Dia a Dia – Trigger para descobrir como um registro \u00e9 inserido, alterado e deletado"},"content":{"rendered":"

Fala Pessoal,<\/p>\n

Ap\u00f3s um longo tempo, segue mais um post com um caso do dia a dia que aconteceu comigo.<\/p>\n

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\u00e3o tinha muita manipula\u00e7\u00e3o de dados.<\/p>\n

Para visualizar o que essa trigger faz, criei as duas tabelas abaixo:<\/p>\n

CREATE TABLE TesteTrigger(
\nId_TesteTrigger int IDENTITY,
\nDATA datetime
\n)<\/p>\n

CREATE TABLE [dbo].[TesteTrigger_Log](
\nOperacao VARCHAR(50),
\nsession_id [smallint] NOT NULL,
\n[login_name] [nvarchar](128) NOT NULL,
\nhostname VARCHAR(200),
\n[start_time] [datetime] NOT NULL,
\n[program_name] [nvarchar](128) NULL,
\n[Query] [nvarchar](max) NULL,
\nId_TesteTrigger [int] NOT NULL — chave primaria da tabela
\n) ON [PRIMARY]\n

Em seguida, executei o script abaixo para criar a trigger:<\/p>\n

CREATE TRIGGER [dbo].[trgTesteTrigger]\nON [dbo].TesteTrigger
\nFOR UPDATE, DELETE , INSERT
\nAS
\nBEGIN
\nSET NOCOUNT ON;
\nDECLARE @Id_TesteTrigger INT, @Tp_Alteracao VARCHAR(50)<\/p>\n

CREATE TABLE #log(eventtype VARCHAR(MAX),parameters int,text VARCHAR(MAX))
\nINSERT INTO #log
\nEXEC(‘DBCC INPUTBUFFER(@@spid)’)<\/p>\n

if not exists(select top 1 null from inserted) –deleted
\nbegin
\nselect @Tp_Alteracao = ‘DELETE’, @Id_TesteTrigger =Id_TesteTrigger
\nfrom deleted
\nend
\nelse if not exists(select top 1 null from deleted) –inserted
\nbegin
\nselect @Tp_Alteracao =’INSERT’,@Id_TesteTrigger =Id_TesteTrigger
\nfrom inserted
\nend
\nelse
\nbegin –update
\nselect @Tp_Alteracao =’UPDATE’,@Id_TesteTrigger =Id_TesteTrigger
\nfrom deleted
\nend<\/p>\n

INSERT INTO TesteTrigger_Log(Operacao,session_id,login_name,hostname,start_time, program_name,Query,
\nId_TesteTrigger)
\nSELECT @Tp_Alteracao,A.session_id,login_name,host_name,B.start_time, program_name,
\n(SELECT text FROM #log) QueryAtual,
\n@Id_TesteTrigger
\nfrom sys.dm_exec_sessions A
\nJOIN sys.dm_exec_requests B on A.session_id = B.session_id
\nJOIN sys.dm_exec_connections C on B.session_id = C.session_id
\nwhere A.session_id = @@spid<\/p>\n

END<\/p>\n

Para validar o log gerado pela trigger, basta executar um insert, um update e um delete:<\/p>\n

INSERT INTO testetrigger SELECT GETDATE()<\/p>\n

UPDATE TesteTrigger SET DATA = GETDATE()-1<\/p>\n

DELETE FROM TesteTrigger<\/p>\n

Segue abaixo o resultado armazenado pela trigger:<\/p>\n

\"\"<\/a><\/p>\n

 <\/p>\n

Destaque para os retornos abaixo:<\/p>\n

A coluna Query<\/strong> armazena a query que est\u00e1 realizando a altera\u00e7\u00e3o. Se for uma procedure, aparece o nome da procedure.<\/p>\n

A coluna Program_Name<\/strong> diz se \u00e9 um comando executado do SSMS ou de uma aplica\u00e7\u00e3o.<\/p>\n

Id_TesteTrigger<\/strong> \u00e9 o Id do registro alterado.<\/p>\n

Hostname<\/strong> \u00e9 de onde o comando foi executado.<\/p>\n

obs.: Lembrando que isso n\u00e3o \u00e9 recomendado para uma tabela com uma manipula\u00e7\u00e3o grande de dados.<\/span><\/p>\n

Espero ter ajudado.<\/p>\n

 <\/p>\n

Gostou dessa Dica?<\/b><\/p>\n

Curta, comente, compartilhe\u2026<\/p>\n

Assine meu canal no Youtube<\/a> e curta minha p\u00e1gina no Facebook<\/a> para receber Dicas de Leituras, V\u00eddeos e Eventos sobre SQL Server.<\/p>\n

Confira mais experi\u00eancias do Dia a Dia de um DBA no meu Treinamento de Tarefas do Dia a Dia de um DBA<\/a>.<\/p>\n

At\u00e9 a pr\u00f3xima.<\/p>\n

Fabr\u00edcio Lima<\/p>\n

MCITP \u2013 Database Administrator<\/p>\n

Consultor e Instrutor SQL Server<\/p>\n

Trabalha com SQL Server desde 2006<\/p>\n","protected":false},"excerpt":{"rendered":"

Fala Pessoal, Ap\u00f3s 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 […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"_exactmetrics_skip_tracking":false,"_exactmetrics_sitenote_active":false,"_exactmetrics_sitenote_note":"","_exactmetrics_sitenote_category":0,"footnotes":""},"categories":[3,6,13,33,280],"tags":[959,368,960,962,961,963,33,958],"class_list":["post-2313","post","type-post","status-publish","format-standard","hentry","category-administracao-de-bd","category-casos-do-dia-a-dia","category-querys-do-dia-a-dia","category-sql-server","category-virtual-pass-br","tag-auditoria-sql-server","tag-log","tag-log-sql-server","tag-logar-delete","tag-logar-insert","tag-logar-update","tag-sql-server","tag-trigger"],"post_mailing_queue_ids":[],"_links":{"self":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/2313","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/comments?post=2313"}],"version-history":[{"count":0,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/posts\/2313\/revisions"}],"wp:attachment":[{"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/media?parent=2313"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/categories?post=2313"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/fabriciolima.net\/blog\/wp-json\/wp\/v2\/tags?post=2313"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}