TRIGGER DE AUDITORIA
24/04/2019
0
BOA TARDE PESSOAL, GOSTARIA DE UM NORTE DE VOCÊS.
ESTOU DESENVOLVENDO UM SISTEMA ONDE EU PRECISO REGISTRAR O LOG REALIZADO EM UMA TABELA DE USUÁRIO, ONDE APÓS A INSERÇÃO, ALTERAÇÃO E EXCLUSÃO DE UM REGISTRO NA MESMA, SERÁ DISPARADA UMA TRIGGER PARA GRAVAR EM UMA TABELA DE AUDITORIA,E NESSA TABELA DEVERÁ GRAVAR O USUÁRIO LOGADO NA APLICAÇÃO E NÃO O USUÁRIO LOGADO NO BANCO DE DADOS, A DATA E A HORA, E UM CAMPO MOTIVO ONDE EU DEVERIA CONCATENAR A FRASE (''''Registro Alterado de: '''' + campo, campo, campo + ''''Para: '''' campo, campo , campo).
QUAL CAMINHO SEGUIR OU QUAL ASSUNTO DEVO PESQUISAR PARA CONSEGUIR PASSAR O USUÁRIO LOGADO NA MINHA APLICAÇÃO PARA A TRIGGER E, COMO PEGAR O REGISTRO ANTERIORMENTE SALVO NA TABELA E SUA NOVA ALTERAÇÃO.
ALGUNS BANCOS DE DADOS, UTILIZAM UMA FUNÇÃO CHAMADA OLD (VELHO) E NEW (NOVO) PARA PODER PEGAR OS VALORES E PASSAR PARA MEU CAMPO HISTÓRICO, EXEMPLO:
''''Alterado de: '''' + OLD.NOME + '''' Para: '''' + NEW.NOME''''
ESSA É MINHA TRIGGER QUE AINDA ESTÁ EM PROCESSO DE DESENVOLVIMENTO, PORÉM JÁ GRAVANDO OS RESPECTIVOS LOG`s.
USE [Imobiliaria]
GO
/****** Object: Trigger [dbo].[trgAuditoria_Usuario] Script Date: 24/04/2019 17:06:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trgAuditoria_Usuario] ON [dbo].[tblUsuario] -- Tabela que a trigger será associada
AFTER INSERT, UPDATE, DELETE AS
declare NEW AS NEW OLD AS OLD
BEGIN
SET NOCOUNT ON
IF (EXISTS(SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted))
BEGIN
INSERT INTO tblAuditoriaUsuario(TABELA, ACAO, USUARIOAPLICACAO, DATAHORA, HISTORICO)
VALUES(''''TBLUSUARIO'''', ''''ALTERAÇÃO'''', CURRENT_USER, GETDATE(), ''''Alterado de: '''' ) ;
END
ELSE BEGIN
IF (EXISTS(SELECT * FROM Inserted))
BEGIN
INSERT INTO tblAuditoriaUsuario(TABELA, ACAO, USUARIOAPLICACAO, DATAHORA, HISTORICO)
VALUES(''''TBLUSUARIO'''', ''''INSERÇÃO'''', HOST_NAME(), GETDATE(), ''''Novo Registro'''' ) ;
END
ELSE BEGIN
INSERT INTO tblAuditoriaUsuario(TABELA, ACAO, USUARIOAPLICACAO, DATAHORA, HISTORICO)
VALUES(''''TBLUSUARIO'''', ''''EXCLUSÃO'''', CURRENT_USER, GETDATE(), ''''Registro Deletado'''') ;
END
END
END
ESTOU DESENVOLVENDO UM SISTEMA ONDE EU PRECISO REGISTRAR O LOG REALIZADO EM UMA TABELA DE USUÁRIO, ONDE APÓS A INSERÇÃO, ALTERAÇÃO E EXCLUSÃO DE UM REGISTRO NA MESMA, SERÁ DISPARADA UMA TRIGGER PARA GRAVAR EM UMA TABELA DE AUDITORIA,E NESSA TABELA DEVERÁ GRAVAR O USUÁRIO LOGADO NA APLICAÇÃO E NÃO O USUÁRIO LOGADO NO BANCO DE DADOS, A DATA E A HORA, E UM CAMPO MOTIVO ONDE EU DEVERIA CONCATENAR A FRASE (''''Registro Alterado de: '''' + campo, campo, campo + ''''Para: '''' campo, campo , campo).
QUAL CAMINHO SEGUIR OU QUAL ASSUNTO DEVO PESQUISAR PARA CONSEGUIR PASSAR O USUÁRIO LOGADO NA MINHA APLICAÇÃO PARA A TRIGGER E, COMO PEGAR O REGISTRO ANTERIORMENTE SALVO NA TABELA E SUA NOVA ALTERAÇÃO.
ALGUNS BANCOS DE DADOS, UTILIZAM UMA FUNÇÃO CHAMADA OLD (VELHO) E NEW (NOVO) PARA PODER PEGAR OS VALORES E PASSAR PARA MEU CAMPO HISTÓRICO, EXEMPLO:
''''Alterado de: '''' + OLD.NOME + '''' Para: '''' + NEW.NOME''''
ESSA É MINHA TRIGGER QUE AINDA ESTÁ EM PROCESSO DE DESENVOLVIMENTO, PORÉM JÁ GRAVANDO OS RESPECTIVOS LOG`s.
USE [Imobiliaria]
GO
/****** Object: Trigger [dbo].[trgAuditoria_Usuario] Script Date: 24/04/2019 17:06:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER TRIGGER [dbo].[trgAuditoria_Usuario] ON [dbo].[tblUsuario] -- Tabela que a trigger será associada
AFTER INSERT, UPDATE, DELETE AS
declare NEW AS NEW OLD AS OLD
BEGIN
SET NOCOUNT ON
IF (EXISTS(SELECT * FROM Inserted) AND EXISTS (SELECT * FROM Deleted))
BEGIN
INSERT INTO tblAuditoriaUsuario(TABELA, ACAO, USUARIOAPLICACAO, DATAHORA, HISTORICO)
VALUES(''''TBLUSUARIO'''', ''''ALTERAÇÃO'''', CURRENT_USER, GETDATE(), ''''Alterado de: '''' ) ;
END
ELSE BEGIN
IF (EXISTS(SELECT * FROM Inserted))
BEGIN
INSERT INTO tblAuditoriaUsuario(TABELA, ACAO, USUARIOAPLICACAO, DATAHORA, HISTORICO)
VALUES(''''TBLUSUARIO'''', ''''INSERÇÃO'''', HOST_NAME(), GETDATE(), ''''Novo Registro'''' ) ;
END
ELSE BEGIN
INSERT INTO tblAuditoriaUsuario(TABELA, ACAO, USUARIOAPLICACAO, DATAHORA, HISTORICO)
VALUES(''''TBLUSUARIO'''', ''''EXCLUSÃO'''', CURRENT_USER, GETDATE(), ''''Registro Deletado'''') ;
END
END
END
Renato Carrasco
Curtir tópico
+ 0
Responder
Posts
25/04/2019
Emerson Nascimento
- eu trabalho com uma tabela de auditoria com a seguinte estrutura:
- tenho em todas as minhas tabelas um campo ID, autoincremental (IDENTITY), que é o campo que serializa os registros. este campo é utilizado para gravar corretamente os registros de auditoria.
-tenho uma stored procedure genérica que grava a informação de auditoria. ela faz o seguinte:
. - ao incluir um registro, todo o seu conteúdo gravado, e o conteúdo de 'conteudoanterior' será sempre nulo
. - ao alterar um registro, grava somente os campos alterados, onde 'conteudoanterior' guardará o valor original do campo
. - ao excluir um registro, grava somente a indicação de exclusão (não grava qualquer outro conteúdo)
o usuário gravado na tabela de auditoria é aquele logado no SQLServer. não consegui obter o usuário logado no sistema
segue o código da stored procedure, que da forma que está só funciona a partir do TRIGGER (de propósito), porque ali que são criadas as tabelas temporárias:
- segue também um exemplo de TRIGGER utilizando a stored procedure. como a stored procedure é genérica, basta criar o TRIGGER sempre da mesma forma, alterando, obviamente, o conteúdo da variável @tabela, que deve conter o nome da tabela à qual o TRIGGER se refere:
CREATE TABLE [dbo].[auditoria]( [usuario] [varchar](50) NOT NULL, [data] [datetime] NOT NULL, [tabela] [varchar](50) NOT NULL, [campo] [varchar](50) NOT NULL, [conteudoanterior] [varchar](max) NULL, [conteudoatual] [varchar](max) NOT NULL, [acao] [char](1) NOT NULL, [registro] [int] NOT NULL ) ON [PRIMARY]
- tenho em todas as minhas tabelas um campo ID, autoincremental (IDENTITY), que é o campo que serializa os registros. este campo é utilizado para gravar corretamente os registros de auditoria.
-tenho uma stored procedure genérica que grava a informação de auditoria. ela faz o seguinte:
. - ao incluir um registro, todo o seu conteúdo gravado, e o conteúdo de 'conteudoanterior' será sempre nulo
. - ao alterar um registro, grava somente os campos alterados, onde 'conteudoanterior' guardará o valor original do campo
. - ao excluir um registro, grava somente a indicação de exclusão (não grava qualquer outro conteúdo)
o usuário gravado na tabela de auditoria é aquele logado no SQLServer. não consegui obter o usuário logado no sistema
segue o código da stored procedure, que da forma que está só funciona a partir do TRIGGER (de propósito), porque ali que são criadas as tabelas temporárias:
ALTER PROCEDURE [dbo].[LOG_AUDITORIA](
@tabela varchar(80),
@usuario varchar(80) )
AS
BEGIN
SET NOCOUNT ON
DECLARE @idregistro int
DECLARE @nomecampo varchar(255)
DECLARE @acao char(1)
DECLARE @instrucao nvarchar(500)
DECLARE @data datetime = getdate()
DECLARE @conteudoanterior nvarchar(250) = null
DECLARE @conteudoatual nvarchar(250)
SET @usuario = rtrim(rtrim(@usuario))
SET @tabela = rtrim(ltrim(@tabela))
DECLARE TAB_CAMPOS CURSOR LOCAL
FOR select ltrim(rtrim(name))
from sys.syscolumns
where id = OBJECT_ID(ltrim(rtrim(@tabela)))
DECLARE TAB_REGISTROS CURSOR LOCAL
FOR select id,
(case when EXISTS(SELECT * FROM ##tmpdeleted WHERE ##tmpdeleted.id = ##tmpinserted.id) then 'A' else 'I' end) acao
from ##tmpinserted
union all
select id, 'E'
from ##tmpdeleted
where NOT EXISTS (SELECT * FROM ##tmpinserted WHERE ##tmpinserted.id = ##tmpdeleted.id)
OPEN TAB_REGISTROS
FETCH NEXT FROM TAB_REGISTROS
INTO @idregistro, @acao
WHILE @@FETCH_STATUS = 0
BEGIN
IF (@acao = 'E') -- exclusão
BEGIN
SET @nomecampo = ''
SET @conteudoanterior = ''
SET @conteudoatual = ''
INSERT INTO auditoria (usuario, data, tabela, registro, acao, campo, conteudoanterior, conteudoatual)
VALUES (@usuario, @data, @tabela, @idregistro, @acao, @nomecampo, @conteudoanterior, @conteudoatual)
END
ELSE
BEGIN
OPEN TAB_CAMPOS
FETCH NEXT FROM TAB_CAMPOS
INTO @nomecampo
WHILE @@FETCH_STATUS = 0
BEGIN
SET @instrucao = N'SELECT @conteudoanterior = max(cast(' + @nomecampo + ' as varchar(250))) FROM ##tmpdeleted where id = ' + STR(@idregistro)
EXECUTE sp_executesql @instrucao, N'@conteudoanterior nvarchar(250) OUTPUT', @conteudoanterior=@conteudoanterior OUTPUT
SET @instrucao = N'SELECT @conteudoatual = max(cast(' + @nomecampo + ' as varchar(250))) FROM ##tmpinserted where id = ' + STR(@idregistro)
EXECUTE sp_executesql @instrucao, N'@conteudoatual nvarchar(250) OUTPUT', @conteudoatual=@conteudoatual OUTPUT
IF ((@acao = 'I') OR (@conteudoanterior <> @conteudoatual))
INSERT INTO auditoria (usuario, data, tabela, registro, acao, campo, conteudoanterior, conteudoatual)
VALUES (@usuario, @data, @tabela, @idregistro, @acao, @nomecampo, @conteudoanterior, @conteudoatual)
-- pega o próximo campo
FETCH NEXT FROM TAB_CAMPOS
INTO @nomecampo
END
-- fecha o cursor de campos da tabela
CLOSE TAB_CAMPOS
END
-- pega o próximo registro
FETCH NEXT FROM TAB_REGISTROS
INTO @idregistro, @acao
END
CLOSE TAB_REGISTROS
DEALLOCATE TAB_REGISTROS
DEALLOCATE TAB_CAMPOS
END- segue também um exemplo de TRIGGER utilizando a stored procedure. como a stored procedure é genérica, basta criar o TRIGGER sempre da mesma forma, alterando, obviamente, o conteúdo da variável @tabela, que deve conter o nome da tabela à qual o TRIGGER se refere:
ALTER TRIGGER cliente_auditoria ON cliente AFTER INSERT,DELETE,UPDATE AS BEGIN DECLARE @usuario varchar(100) = SYSTEM_USER -- aqui deveria indicar o usuário correto DECLARE @tabela varchar(100) = 'cliente' -- cria as tabelas temporárias SELECT * into ##tmpinserted FROM inserted SELECT * into ##tmpdeleted FROM deleted -- grava os registros na tabela de auditoria EXEC LOG_AUDITORIA @tabela, @usuario -- exclui as tabelas temporárias DROP TABLE ##tmpinserted DROP TABLE ##tmpdeleted END GO
Responder
29/04/2019
Renato Carrasco
Boa tarde amigo, vou testar aqui e te retorno.
Muito obrigado.
Muito obrigado.
Responder
Clique aqui para fazer login e interagir na Comunidade :)
