Trigger de auditoria no SQL Server

Publicado: 27 de outubro de 2008 em T-SQL
Tags:
Boa tarde pessoal,
 
Sejam bem vindos ao meu blog, meu nome é Demétrio Silva e a partir de hoje estarei publicando artigos na área de banco de dados ( mais especificamente Sql Server ).
 
De início, segue uma stored procedure dinâmica que cria uma trigger de auditoria e uma tabela espelho para a tabela passada como parâmetro.
 
Amanhã descrevo melhor como ela funciona.
 
Abraços
 

/*
exec stp_gerar_auditoria_sql ‘tb_exemplo’
*/

create

procedure stp_gerar_auditoria_sql

 
@tabela

varchar(128),

@owner

varchar(128) = ‘dbo’

 
as
begin
 
set nocount on
 
–início variáveis do cursor
declare @coluna varchar(128)
declare @tipo varchar(128)
declare @tamanho smallint
declare @null int
declare @collate sysname
declare @precisao tinyint
declare @scala tinyint
–fim variáveis do cursor
 
–início variáveis para montar o sql
declare @sql varchar(max)
declare @campos varchar(max)
–fim variáveis para montar o sql
 
set @campos =
–pega cada campo da tabela a ser auditada nas tabelas de sistema
–ordena pela ordem de criação dos campos da tabela
declare c_sql cursor read_only for
select
b.name,
c.name as tipo,
b.length,
b.isnullable,
b.collation,
b.xprec,
b.xscale
from sysobjects a
inner join syscolumns b on a.id = b.id
inner join systypes c on b.xtype = c.xtype and c.name <> ‘sysname’
where
a.id = object_id(n + @owner + ‘.’ + @tabela + )
and objectproperty(a.id, n‘isusertable’) = 1
order by b.colorder
 
open c_sql
 
–início cria a tabela de log
set @sql = ‘create table ‘ + @owner + ‘.’ + @tabela + ‘_sql’ + ‘ (‘
set @sql = @sql + ‘id_log int identity (1, 1) not null,’
 
fetch next from c_sql
into @coluna, @tipo, @tamanho, @null, @collate, @precisao, @scala
 
set @campos = + char(9)
 
while @@fetch_status = 0
begin
 
if (@tipo <> ‘text’ and @tipo <> ‘ntext’ and @tipo <> ‘image’ and @tipo <> ‘timestamp’)
begin
set @campos = @campos + @coluna + ‘,’ + char(13) + char(9) + char(9)
 
set @sql = @sql + + @coluna + ‘ ‘ + @tipo + ‘ ‘
 
if @tipo in (‘binary’, ‘char’, ‘nchar’, ‘nvarchar’, ‘varbinary’, ‘varchar’)
begin
if (@tamanho = 1)
set @sql = @sql + ‘(max) ‘
else
set @sql = @sql + ‘(‘ + cast(@tamanho as varchar(10)) + ‘) ‘
end
 
if @tipo in (‘decimal’, ‘numeric’)
set @sql = @sql + ‘(‘ + cast(@precisao as varchar(10)) + ‘,’ + cast(@scala as varchar(10)) + ‘) ‘
 
if @tipo in (‘char’, ‘nchar’, ‘nvarchar’, ‘varchar’, ‘text’, ‘ntext’)
set @sql = @sql + ‘collate ‘ + @collate + ‘ ‘
 
if @null = 0
set @sql = @sql + ‘not ‘
 
set @sql = @sql + ‘null, ‘
end
fetch next from c_sql
into @coluna, @tipo, @tamanho, @null, @collate, @precisao, @scala
end
 
close c_sql
deallocate c_sql
 
— colunas de controle
set @sql = @sql + ‘acao_log char (1),’
set @sql = @sql + ‘data_log datetime constraint df_’ + @tabela + ‘_data_log default getdate(), ‘
set @sql = @sql + ‘usuario_log varchar (50) constraint df_’ + @tabela + ‘_usuario_log default (suser_sname()), ‘
set @sql = @sql + ‘aplicacao_log varchar(128) constraint df_’ + @tabela + ‘_aplicacao_log default ( rtrim(isnull(app_name(),””)) )’
set @sql = @sql + ‘)’
 
 
exec (@sql)
 
set @sql =
set @sql = char(13) +
‘create trigger tiud_log_’ + @tabela + ‘ on ‘ + @owner + ‘.’ + @tabela + char(13) +
 
‘for insert, update, delete ‘ + char(13) + char(13) +
 
‘as ‘ + char(13) + char(13) +
 
‘begin ‘ + char(13) + char(13) +
 
‘set nocount on ‘ + char(13) + char(13) +
 
‘declare @operacao char(1) ‘ + char(13) + char(13) +
 
‘if exists(select 1 from inserted) ‘ + char(13) +
‘begin ‘ + char(13) +
‘ set @operacao = ”i” ‘ + char(13) +
‘end ‘ + char(13) + char(13) +
‘if exists(select 1 from deleted) ‘ + char(13) +
‘begin ‘ + char(13) +
‘ if @operacao is null ‘ + char(13) +
‘ set @operacao = ”d” ‘ + char(13) +
‘ else ‘ + char(13) +
‘ set @operacao = ”u” ‘ + char(13) +
‘end ‘ + char(13) + char(13) +
 
‘if @operacao = ”i” ‘ + char(13) +
‘begin ‘ + char(13) +
‘ insert into ‘ + @tabela + ‘_sql’ + ‘(‘ + char(13) + char(9) + @campos + ‘acao_log’ + char(13) + char(9) + ‘)’ + char(13) + char(9) + ‘select ‘ + char(13) + char(9) + @campos + ‘@operacao ‘ + char(13) + char(9) + ‘from inserted’ + char(13) +
‘end ‘ + char(13) +
‘else if @operacao = ”u” ‘ + char(13) +
‘begin ‘ + char(13) +
‘ insert into ‘ + @tabela + ‘_sql’ + ‘(‘ + char(13) + char(9) + @campos + ‘acao_log’ + char(13) + char(9) + ‘)’ + char(13) + char(9) + ‘select ‘ + char(13) + char(9) + @campos + ‘@operacao ‘ + char(13) + char(9) + ‘from inserted’ + char(13) +
‘end ‘ + char(13) +
‘else if @operacao = ”d” ‘ + char(13) +
‘begin ‘ + char(13) +
‘ insert into ‘ + @tabela + ‘_sql’ + ‘(‘ + char(13) + char(9) + @campos + ‘acao_log’ + char(13) + char(9) + ‘)’ + char(13) + char(9) + ‘select ‘ + char(13) + char(9) + @campos + ‘@operacao ‘ + char(13) + char(9) + ‘from deleted ‘ + char(13) +
‘end ‘ + char(13) + char(13) +
‘end ‘
exec(@sql)
end
 
Anúncios
comentários
  1. marcelo disse:

    Tu é o cara , muito bom . Gostei!

  2. Demétrio disse:

    Valeu Marcelo.Abraço,Demétrio Silva

  3. Marcelo Nobre disse:

    Meu querido Dema gostaria de saber se este gatilho deixa lento o banco de dados caso a quantidade de registro supere 500.000?

    • demetrioi disse:

      Olá Marcelo,

      Mesmo sem trigger, operações que afetam mais de 500 mil registros tendem a ser lentas.

      O que você pode fazer para diminuir o impacto é gerar vários batchs. Algo como,

      Update top (10000) nome from tabela
      GO
      Update top (10000) nome from tabela
      GO

      Abraço,

      Demétrio Silva

  4. Marcelinho disse:

    Demetrio, estou iniciando trabalho com o sqlServer2008, cheguei a um nivel onde preciso controlar as alterações de alguns campos da tabela, li alguns artigos e cheguei a conclusao que preciso usar triggers, porem o resultado que cheguei é que toda vez ela executa trazendo todos os campos como alterados De Para, poderia me auxiliar para fazer o tratamento e insermirmos apenas os campos devidamente alterados

  5. Kleber Rafael disse:

    Marcelo,

    ótimo artigo meu velho, mais me tira uma duvida, tenho que criar uma procedure para cada tabela de cada database? não existe uma maneira de deixarmos uma unica proc pra todos os dbs??

    abraços,

    Kleber Rafael

  6. Kleber Rafael disse:

    desculpe-me Demétrio, errei seu nome…

  7. Ricardo Cassiano disse:

    muito massa Demétrio!!

Deixe um comentário

Preencha os seus dados abaixo ou clique em um ícone para log in:

Logotipo do WordPress.com

Você está comentando utilizando sua conta WordPress.com. Sair / Alterar )

Imagem do Twitter

Você está comentando utilizando sua conta Twitter. Sair / Alterar )

Foto do Facebook

Você está comentando utilizando sua conta Facebook. Sair / Alterar )

Foto do Google+

Você está comentando utilizando sua conta Google+. Sair / Alterar )

Conectando a %s