{"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":"","jetpack_post_was_ever_published":false},"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"],"jetpack_featured_media_url":"","jetpack-related-posts":[{"id":286,"url":"https:\/\/fabriciolima.net\/blog\/2010\/04\/11\/como-criar-um-controle-de-versao-de-procedures-views-e-functions-no-sql-server\/","url_meta":{"origin":2313,"position":0},"title":"Como criar um Controle de Vers\u00e3o de Procedures, Views e Functions no SQL Server","author":"Fabr\u00edcio Lima","date":"11 de abril de 2010","format":false,"excerpt":"Quantas vezes j\u00e1 te perguntaram qual era o c\u00f3digo de uma fun\u00e7\u00e3o, procedure ou view em uma determinada data? At\u00e9 o SQL Server 2000, s\u00f3 era poss\u00edvel obter essa informa\u00e7\u00e3o atrav\u00e9s da gera\u00e7\u00e3o peri\u00f3dica de arquivos com scripts dos objetos do banco de dados. Entretanto, a partir do SQL Server\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/fg7djq.bay.livefilestore.com\/y1pZnviks_oXTAbvTepEXLmnGOQrvNjLmPIYsnTtHeOc35EqnNRyKgWy5UP9pLfVfu7xdnua1G4ZCJ-dmwm3lqJfpEEYCjfqwI3\/Figura%201%20-%20Resultado%20query.JPG?resize=350%2C200","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/fg7djq.bay.livefilestore.com\/y1pZnviks_oXTAbvTepEXLmnGOQrvNjLmPIYsnTtHeOc35EqnNRyKgWy5UP9pLfVfu7xdnua1G4ZCJ-dmwm3lqJfpEEYCjfqwI3\/Figura%201%20-%20Resultado%20query.JPG?resize=350%2C200 1x, https:\/\/i0.wp.com\/fg7djq.bay.livefilestore.com\/y1pZnviks_oXTAbvTepEXLmnGOQrvNjLmPIYsnTtHeOc35EqnNRyKgWy5UP9pLfVfu7xdnua1G4ZCJ-dmwm3lqJfpEEYCjfqwI3\/Figura%201%20-%20Resultado%20query.JPG?resize=525%2C300 1.5x, https:\/\/i0.wp.com\/fg7djq.bay.livefilestore.com\/y1pZnviks_oXTAbvTepEXLmnGOQrvNjLmPIYsnTtHeOc35EqnNRyKgWy5UP9pLfVfu7xdnua1G4ZCJ-dmwm3lqJfpEEYCjfqwI3\/Figura%201%20-%20Resultado%20query.JPG?resize=700%2C400 2x"},"classes":[]},{"id":1238,"url":"https:\/\/fabriciolima.net\/blog\/2012\/03\/27\/casos-do-dia-a-dia-create-table-gerando-um-arquivo-dump-isso-e-um-bug\/","url_meta":{"origin":2313,"position":1},"title":"Casos do Dia a Dia – CREATE TABLE gerando um arquivo DUMP! Isso \u00e9 um bug?","author":"Fabr\u00edcio Lima","date":"27 de mar\u00e7o de 2012","format":false,"excerpt":"Ol\u00e1 Pessoal, No meu ambiente existem uns 30 profissionais que utilizam o SQL Server diariamente (Dev, ERP e BI). Com isso, cada dia uma pessoa me aparece com um problema diferente para resolver. Entretanto, em uma dessas vezes, eles se superaram e me mandaram um problema que eu n\u00e3o consegui\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2012\/02\/Erro1.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2012\/02\/Erro1.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2012\/02\/Erro1.png?resize=525%2C300&ssl=1 1.5x, https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2012\/02\/Erro1.png?resize=700%2C400&ssl=1 2x"},"classes":[]},{"id":1323,"url":"https:\/\/fabriciolima.net\/blog\/2012\/02\/29\/colunas-com-valores-null-ocupam-espaco-em-disco-mito\/","url_meta":{"origin":2313,"position":2},"title":"Colunas com valores NULL ocupam espa\u00e7o em disco? Mito?","author":"Fabr\u00edcio Lima","date":"29 de fevereiro de 2012","format":false,"excerpt":"Ol\u00e1 Pessoal, Muitos de voc\u00eas j\u00e1 devem ter recebido a seguinte solicita\u00e7\u00e3o: \"Favor criar uma coluna nova na tabela X (80 milh\u00f5es de linhas). Como os valores existentes ser\u00e3o NULLs, esse campo n\u00e3o ocupar\u00e1 espa\u00e7o em disco e n\u00e3o teremos nenhum impacto nessa tabela\". Entretanto, n\u00e3o \u00e9 exatamente isso que\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2012\/02\/spaceusedTabelas.png?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":133,"url":"https:\/\/fabriciolima.net\/blog\/2010\/10\/15\/casos-do-dia-a-dia-exclusao-de-campo-text-nao-libera-espaco-em-disco\/","url_meta":{"origin":2313,"position":3},"title":"Casos do Dia a Dia: Exclus\u00e3o de campo Text n\u00e3o libera espa\u00e7o em disco?","author":"Fabr\u00edcio Lima","date":"15 de outubro de 2010","format":false,"excerpt":"Fala pessoal, Compartilhando mais uma experi\u00eancia que tive no meu dia a dia de trabalho, ap\u00f3s a verifica\u00e7\u00e3o com a equipe de desenvolvimento da possibilidade de exclus\u00e3o de 2 campos do tipo text de uma tabela muito utilizada, fui com toda empolga\u00e7\u00e3o na minha base de testes verificar o quanto\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"spaceused_antes","src":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2010\/10\/spaceused_antes_thumb.jpg?resize=350%2C200&ssl=1","width":350,"height":200},"classes":[]},{"id":2111,"url":"https:\/\/fabriciolima.net\/blog\/2013\/02\/18\/feedback-microsoft-connect-resolvido-como-nao-sera-corrigido\/","url_meta":{"origin":2313,"position":4},"title":"FeedBack Microsoft Connect: Resolvido como N\u00c3O ser\u00e1 corrigido","author":"Fabr\u00edcio Lima","date":"18 de fevereiro de 2013","format":false,"excerpt":"Ol\u00e1 Pessoal, Ano passado fiz um post registrando um Bug que acontece at\u00e9 a vers\u00e3o 2008 R2 do SQL Server, quando se cria uma tabela com dois campos calculados com o mesmo nome, conforme pode ser visto abaixo: create table Bug_SQL2008R2( Cod int, Bug as cod+cod, Bug as cod+cod )\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"","src":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2013\/02\/BugSQL2008R2.png?resize=350%2C200&ssl=1","width":350,"height":200,"srcset":"https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2013\/02\/BugSQL2008R2.png?resize=350%2C200&ssl=1 1x, https:\/\/i0.wp.com\/www.fabriciolima.net\/blog\/wp-content\/uploads\/2013\/02\/BugSQL2008R2.png?resize=525%2C300&ssl=1 1.5x"},"classes":[]},{"id":285,"url":"https:\/\/fabriciolima.net\/blog\/2010\/03\/24\/criando-um-checklist-automatico-do-banco-de-dados\/","url_meta":{"origin":2313,"position":5},"title":"Criando um CheckList Autom\u00e1tico do Banco de Dados","author":"Fabr\u00edcio Lima","date":"24 de mar\u00e7o de 2010","format":false,"excerpt":"Atualizado em: 01\/05\/2017 OBS: Segue abaixo o link de um Post com uma nova vers\u00e3o desse CheckList muito mais pr\u00e1tica e com diversas melhorias. Desta vez, o CheckList ser\u00e1 enviado por e-mail em formato HTML: Link Post: Criando um E-mail de CheckList Di\u00e1rio no SQL Server \u00a0 Ol\u00e1 pessoal, Assim\u2026","rel":"","context":"Em "Administra\u00e7\u00e3o de Banco de Dados"","block_context":{"text":"Administra\u00e7\u00e3o de Banco de Dados","link":"https:\/\/fabriciolima.net\/blog\/category\/sql-server\/administracao-de-bd\/"},"img":{"alt_text":"","src":"","width":0,"height":0},"classes":[]}],"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}]}}