Auditoria utilizando SQL Server 

/*
    Alterar somente o nome da dataBase
*/
 
use GrandeSeletor
 
/**
* A TRIGGER DE AUDITORIA NÃO FUNCIONA PARA OS SEGUINTES TIPOS: 
* 1 - TEXT, 
* 2 - NTEXT, 
* 3 - IMAGE
*/
 
SET NOCOUNT ON 
--Verifica se existe uma tabela com o nome PowerAuditoria, se não existir criamos a tabela.
if not exists (SELECT name FROM SYSOBJECTS WHERE type = 'U' AND name = 'PowerAuditoria')
begin
    create table PowerAuditoria(
        Id int not null identity(1,1) primary key,
        Usuario varchar(10) not null,
        DataHora datetime not null,
        Tabela varchar(50) not null,
        AcaoExecutada char(1) not null,
        RegistroAnterior varchar(3500),
        RegistroAtual varchar(3500)
    )
end
 
Declare @idTabela int,
        @nameTabela varchar(20),
        @nameColuna varchar(50),
        @query varchar(8000),
        @tipoCampo varchar(100),
        @tamanhoCampo int,
        @auxdeclare varchar(8000), 
        @auxStringTrigger varchar(8000),
        @queryInsertUpdate varchar(8000),
        @queryDelete varchar(8000),
        @colunaValorDE varchar(8000),
        @colunaValorIU varchar(8000)     
        
        
        ---  Inicializando variáveis
        set @queryInsertUpdate = 'SELECT '
        set @queryDelete = 'SELECT '
        set @auxDeclare = 'DECLARE '
        set @colunaValorDE = ''
        set @colunaValorIU = ''
        
        
--Declarando cursor para as tabelas de uma database        
DECLARE cTabela CURSOR FOR (Select id, name from  sysobjects where xtype = 'u' and name <> 'PowerAuditoria')
open cTabela
FETCH NEXT FROM cTabela Into @idTabela, @nameTabela
WHILE @@FETCH_STATUS = 0
begin
    --Declarando cursor para as colunas de uma tabela selecionada no cursor
    Declare cColuna cursor for (Select sc.name, st.name, sc.length from syscolumns sc inner join systypes st on sc.xusertype = st.xusertype where sc.id = @idTabela)
    open cColuna
    Fetch next from cColuna into @nameColuna, @tipoCampo, @tamanhoCampo
    while @@fetch_status = 0
    begin
        
        --- Início variavel
        if (@tipoCampo = 'int' or @tipoCampo = 'bit' or @tipoCampo = 'bigint' or @tipoCampo = 'money')--Não aceita tamanho
        begin
            set @auxDeclare = @auxDeclare + '@' + @nameColuna + '_UI ' + @tipoCampo + ', @' + @nameColuna + '_DE ' + @tipoCampo
            set @colunaValorIU = @colunaValorIU + '' + @nameColuna + '=''+' + ('cast(@'+ @nameColuna + '_UI as varchar)')
            set @colunaValorDE = @colunaValorDE + '' + @nameColuna + '=''+' + ('cast(@'+ @nameColuna + '_DE as varchar)')
        end
        else if(@tipoCampo = 'dateTime' or @tipoCampo = 'smalldatetime')--Não aceita tamanho
        begin
            set @auxDeclare = @auxDeclare + '@' + @nameColuna + '_UI ' + @tipoCampo + ', @' + @nameColuna + '_DE ' + @tipoCampo
            set @colunaValorIU = @colunaValorIU + ('') + @nameColuna + ('=''+') + ('convert(varchar,@'+ @nameColuna + '_UI,121)')
            set @colunaValorDE = @colunaValorDE + ('') + @nameColuna + ('=''+') + ('convert(varchar,@'+ @nameColuna + '_DE,121)')
        end
        else if(@tipoCampo = 'text' or @tipoCampo = 'ntext' or @tipoCampo = 'image')--Não aceita os tipos citados
        begin
            print ('Os tipos text, ntext e image não são compatíveis com a auditoria até o momento.')--não faz nada
        end
        else
        begin
            set @auxDeclare = @auxDeclare + '@' + @nameColuna + '_UI ' + @tipoCampo + '(' + cast(@tamanhoCampo as varchar(5)) +'), @' + @nameColuna + '_DE ' + @tipoCampo + '(' + cast(@tamanhoCampo as varchar(5)) +')'
            set @colunaValorIU = @colunaValorIU + '' + @nameColuna + '=''+' + ('@'+ @nameColuna + '_UI')
            set @colunaValorDE = @colunaValorDE + '' + @nameColuna + '=''+' + ('@'+ @nameColuna + '_DE')
        end
        --- Fim variavel
                
        --- Início query
        --- Insert
        if (len(@queryInsertUpdate) > 0 and @tipoCampo <> 'text' and @tipoCampo <> 'ntext' and @tipoCampo <> 'image' )
        begin
            set @queryInsertUpdate = @queryInsertUpdate + '@' +@nameColuna +'_UI='+ @nameColuna
        end
        --- Delete
        if (len(@queryDelete) > 0 and @tipoCampo <> 'text' and @tipoCampo <> 'ntext' and @tipoCampo <> 'image')
        begin
            set @queryDelete = @queryDelete + '@' +@nameColuna +'_DE='+ @nameColuna
        end
        --- Fim query
        
        Fetch next from cColuna into @nameColuna, @tipoCampo, @tamanhoCampo -- Próxima coluna
        
        --Verifica a posição para adicionar a última vírgula
        if (@@FETCH_STATUS = 0)
        begin
            if (@tipoCampo <> 'text' and @tipoCampo <> 'ntext' and @tipoCampo <> 'image')
            begin
     set @auxDeclare = @auxDeclare + ','
     set @queryInsertUpdate = @queryInsertUpdate + ','
     set @queryDelete = @queryDelete + ','
     set @colunaValorIU = @colunaValorIU + '+'';'
     set @colunaValorDE = @colunaValorDE + '+'';'
            end
        end
        else
        begin
            set @auxDeclare = @auxDeclare + ',@regAnterior varchar(8000), @regAtual varchar(8000), @nameTable varchar(50)'
        end
    end
    close cColuna
    deallocate cColuna
    
    set @queryInsertUpdate = @queryInsertUpdate+ ' FROM INSERTED' 
    set @queryDelete = @queryDelete + ' FROM DELETED'
    --Verificando se a tabela existe.
    if exists (select * from sysobjects where xtype = 'u' and name = @nameTabela)
    begin
    --Criando a trigger para tabela selecionada no cursor
    if not exists (SELECT * FROM sysobjects WHERE name = 'tg_'+@nameTabela+'' AND type = 'tr')
    begin
    exec ('CREATE TRIGGER tg_'+ @nameTabela +' ON  '+ @nameTabela +' AFTER INSERT,DELETE,UPDATE AS 
    BEGIN 
        SET NOCOUNT ON;
         
            declare @usuario varchar
            SELECT @usuario = CAST(CONTEXT_INFO AS VARCHAR)FROM master.dbo.SYSPROCESSES WHERE SPID=@@SPID 
         
        '+ @auxDeclare +'
        
        set @nameTable = '''+ @nameTabela +'''
        
        if  ((exists (select * from inserted)) and (exists (select * from deleted))) 
        begin 
     '+ @queryDelete +'    
     set @regAnterior = '''+ @colunaValorDE +'
                 
     '+ @queryInsertUpdate +'
     set @regAtual = '''+ @colunaValorIU +'
           
     INSERT INTO POWERAUDITORIA(USUARIO, DATAHORA, TABELA, ACAOEXECUTADA, REGISTROANTERIOR, REGISTROATUAL) VALUES (@usuario, GETDATE(), @nameTable, ''U'', @regAnterior, @regAtual)
        end
        if ((exists (select * from inserted)) and (not exists (select * from deleted))) 
        begin 
     set @regAnterior = null
                
     '+ @queryInsertUpdate +'
     set @regAtual = '''+ @colunaValorIU +'
           
     INSERT INTO POWERAUDITORIA(USUARIO, DATAHORA, TABELA, ACAOEXECUTADA, REGISTROANTERIOR, REGISTROATUAL) VALUES (@usuario, GETDATE(), @nameTable, ''I'', @regAnterior, @regAtual)
        end 
        if ((not exists (select * from inserted)) and (exists (select * from deleted))) 
        begin 
            '+ @queryDelete +'    
            set @regAnterior = '''+ @colunaValorDE +'
            
            set @regAtual = null
            INSERT INTO POWERAUDITORIA(USUARIO, DATAHORA, TABELA, ACAOEXECUTADA, REGISTROANTERIOR, REGISTROATUAL) VALUES (@usuario, GETDATE(), @nameTable, ''D'', @regAnterior, @regAtual)
        END
    END')
    END
    ELSE
    BEGIN
      exec ('ALTER TRIGGER tg_'+ @nameTabela +' ON  '+ @nameTabela +' AFTER INSERT,DELETE,UPDATE AS 
    BEGIN 
        SET NOCOUNT ON;
        
            declare @usuario varchar(10)
            SELECT @usuario = CAST(CONTEXT_INFO AS VARCHAR)FROM master.dbo.SYSPROCESSES WHERE SPID=@@SPID 
         
        '+ @auxDeclare +'
        
        set @nameTable = '''+ @nameTabela +'''
        
        if  ((exists (select * from inserted)) and (exists (select * from deleted))) 
        begin 
     '+ @queryDelete +'    
     set @regAnterior = '''+ @colunaValorDE +'
                 
     '+ @queryInsertUpdate +'
     set @regAtual = '''+ @colunaValorIU +'
           
    INSERT INTO POWERAUDITORIA(USUARIO, DATAHORA, TABELA, ACAOEXECUTADA, REGISTROANTERIOR, REGISTROATUAL) VALUES (@usuario, GETDATE(), @nameTable, ''U'', @regAnterior, @regAtual)
        end
        if ((exists (select * from inserted)) and (not exists (select * from deleted))) 
        begin 
     set @regAnterior = null
                
     '+ @queryInsertUpdate +'
     set @regAtual = '''+ @colunaValorIU +'
           
     INSERT INTO POWERAUDITORIA(USUARIO, DATAHORA, TABELA, ACAOEXECUTADA, REGISTROANTERIOR, REGISTROATUAL) VALUES (@usuario, GETDATE(), @nameTable, ''I'', @regAnterior, @regAtual)
        end 
        if ((not exists (select * from inserted)) and (exists (select * from deleted))) 
        begin 
            '+ @queryDelete +'    
            set @regAnterior = '''+ @colunaValorDE +'
            
            set @regAtual = null
            INSERT INTO POWERAUDITORIA(USUARIO, DATAHORA, TABELA, ACAOEXECUTADA, REGISTROANTERIOR, REGISTROATUAL) VALUES (@usuario, GETDATE(), @nameTable, ''D'', @regAnterior, @regAtual)
        END
    END')
    END  
    END 
    FETCH NEXT FROM cTabela Into @idTabela, @nameTabela
   
    -- Limpando variáveis
    set @queryInsertUpdate = 'SELECT '
    set @queryDelete = 'SELECT '
    set @auxDeclare = 'DECLARE '
    set @colunaValorDE = ''
    set @colunaValorIU = ''
end
close cTabela
deallocate cTabela
 
--Cria a função para setar o usuário da aplicação
if not exists (SELECT * FROM   sysobjects  WHERE  name = N'SetUsuario' AND type = 'P')
begin
        exec ('Create procedure SetUsuario (
         @user varchar(8)
     )
     as
     begin
     DECLARE @ContextInfo varbinary(128)
     SELECT @ContextInfo = cast( @user AS varbinary(128) )
     SET CONTEXT_INFO @ContextInfo
     end')
end

Comentários

Postar um comentário

Os códigos postados aqui são uma seqüência de instruções de forma ordenada, não compilada para o código de máquina, em uma determinada linguagem de programação.
Ajude a melhorar esses códigos.

Postagens mais visitadas deste blog

Funcão SQL para abreviar nomes

CREATE FUNCTION dbo.Abrevia ( @nome varchar (200) ) RETURNS varchar (25) AS BEGIN declare @aux varchar (120) declare @aux2 varchar (25) declare @fim varchar (25)   --Pegar o primeiro nome set @fim= left (@nome,charindex( ' ' ,@nome)) set @aux=@fim --Inicializa variável set @aux2= '' --Inicializa variável --Enquanto tem nomes do meio while len(@aux)>0 begin --pega primeiro nome set @aux= left (@nome,charindex( ' ' ,@nome)) --remove primeiro nome set @nome=ltrim(replace(@nome,@aux, '' )) --exclui de,da,dos(da silva, dos santos..) if charindex( ' ' ,@nome)>4 begin --Abrevia com . set @aux2=@aux2+ substring (@nome,1,1)+ '. ' end end --concatena tudo(primeiro nome+abreviações+último select @fim=@fim+@aux2+@nome nome) RETURN ( @fim ) END
Leia mais

Função em JavaScript para consulta de CEP com retorno em XML ou Json

Para criar uma biblioteca em JavaScript que consulta um CEP e retorna os dados em XML ou JSON, podemos utilizar uma API pública que realiza essa consulta e retorna a resposta no formato desejado. Nesse exemplo, vamos utilizar a API do ViaCEP, que permite realizar consultas de CEP e retornar os dados em JSON ou XML. O código a seguir mostra uma implementação simples de uma função que consulta um CEP utilizando a API do ViaCEP e retorna os dados em XML ou JSON, dependendo do formato especificado: function consultarCEP(cep, formato, callback) { var url = 'https://viacep.com.br/ws/' + cep + '/' + formato + '/'; var xhr = new XMLHttpRequest(); xhr.open('GET', url, true); xhr.onreadystatechange = function() { if (xhr.readyState == 4 && xhr.status == 200) { var resposta = xhr.responseText; if (formato == 'json') { callback(JSON.parse(resposta)); } else { callback(resposta); } ...
Leia mais