/*
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.