Criar log através de Triggers

Question

Olá,
Preciso fazer um LOG de uma aplicação WEB, e seria ótimo se pudesse fazer isso atravéz de triggers, ja que ela oferece recursos para manipular informações que são excluídas, alteradas e inseridas.
O problema é que preciso gravar no LOG tb, a data e o usuário que fez a alteração.
Em relação a data sem problemas. O problema e, como recuperar o Usuário que fez essa alteraçõ, uma vez que não consigo fazer isso atravez da variavel USER do sql, pois preciso do usuario logado em minha aplicação, e não do usuário do banco. Existe alguma forma de passar essa informação para a trigger?

Exemplo:

PROCEDURE:

Code Snippet

create procedure pr_IncluirDepartamento (

@id_depto,
@nome_depto,
@userId

)
as
insert into tb_depto values (@id_depto, @nome_depto)

TRIGGER

Code Snippet

create trigger trg_IncluirDepartamento
on tb_depto
for insert
as

insert into tb_log_depto (id, nome, valor, data, usuario)

select id_depto, nome_depto, GETDATE(), [USUARIO DA PROC]
from inserted

Como fazer isso???

Vlw

Answer 1

Me diz uma coisa, você tembem tem uma proc para Apagar os Departamentos e outra para fazer o Update?

Answer 2

Ola Fabiano,
Tenho sim.

Answer 3

Otavio,

 

Qual o principal objetivo desta separação de procedure?

Answer 4

Otavio,

 

Veja se alguma destas procedures ou functions podem ajudar:

 

Current_User --> equivalente a User_Name();
User_Name() --> retorna o nome do usuário conectado ao banco de dados, de acordo com id especificado;
Session_User --> retorna o nome do usuário de acordo com o contexto de banco de dados;
Suser_Name() --> retorna o nome do usuário;
Suser_ID() --> retorna o identificador do login associado ao nome do usuário;
Suser_SID() --> retorna o SID associado ao nome do login;
Suser_SName() --> retorna o nome do login associado ao SID;
System_User --> retorna o nome do login no banco de dados corrrente, de acordo com as informações na system tables sys.sysusers dentro do atual conectado.User --> retorna o nome do usuário no banco de dados corrrente, de acordo com as informações na system tables sys.sysusers dentro do atual conectado.

Você pode utilizar também a table sys.sysusers, para obter informaçõe dos usuários.

Answer 5

Eu ainda estou analizando a melhor forma para fazer isso. Não estou dizendo que será assim.
E que eu achei que seria viável fazer isso com triggers porque o próprio banco já oferece recursos para que eu consiga resgatar as informações antigas. E isso seria muito util para mim porque não teria se fosse fazer isso pela aplicação, teria que desenvolver uma logica para fazer isso, o que esta gerando muito trabalho.

Answer 6

Junior,
Estas funções me retornam a user do Banco.
Esse e justamente o meu problema
Eu preciso gravar o nome do usuario que esta logado na aplicação. E não do usuário do banco.
Por esse motivo eu criei um parametro em minha proc para passar o nome do usuario atraves da aplicação.
Mas não sei como fazer com que a trigger encontre o valor desse parametro.

Answer 7

Oi Otavio, eu já imaginava que a resposta seria positiva,

Bom no meu ver você deve fazer a validação dentro de cada Proc, não há nenhuma razão para usar Triggers sendo que você já tem total controle(procs) sobre os eventos que ocorrem em sua tabela.

Dai fica fácil, coloque os inserts dentro das procs, e pronto. Lá você já tem o seu usuário, basta utilizar a variável.

Neste caso, Triggers só vão acarretar em um overhead extra e desnecessário.

Abraço.

Answer 8

Otavio,

 

Então você deseja obter o nome do usuário do Windows?

Answer 9

O Usuário que preciso recuperar, é um login de acesso ao sistema. O usuário que foi digitado lá na tela de login do sistema.

Answer 10

Ok Fabiano,
Mas da proc tem como pegar os valores antigos como na trigger?

Exemplo:

Code Snippet

Select id_depto, nome_depto from UPDATED

ou neste caso devo mesmo fazer um select na tabela antes de alterar os registros?

Answer 11

Segue um exemplo de como usar a clausula OUTPUT, que por sinal poucos usam e muitos desconhecem,

DECLARE @TabelaTeste TABLE (ID Int Identity(1,1), Nome VarChar(80))
DECLARE @Tab TABLE (NomeAtual VarChar(80), NomeAntigo VarChar(80))

INSERT INTO @TabelaTeste
OUTPUT INSERTED.Nome
INTO @Tab(NomeAtual)
VALUES('Fabiano')

SELECT * FROM @TAB

UPDATE @TabelaTeste set Nome = 'Novo Nome Fabiano'
OUTPUT INSERTED.Nome,
       DELETED.Nome
INTO @Tab
WHERE ID = 1
SELECT * FROM @TAB

DELETE @Tab

DELETE @TabelaTeste
OUTPUT DELETED.Nome
INTO @Tab(NomeAntigo)
SELECT * FROM @TAB


Aa, só rola no SQL2005

Espero que ajude, abraço

Answer 12

Olá Antônio,

 

Pelo que entendi, você está querendo pegar o id do usuário de sua aplicação ( que fica na tabela tb_usuarios por exemplo ), né isso?

 

Se for, você vai precisar adicionar o campo idUsuario na tabela a ser auditada e quando alterar e incluir você passa esse id na query de insert e update,

 

Para os casos de delete, você precisa dar um update e depois um delete:

 

Seria +- assim:

 

Para Delete

 

Update tabela set idUsuario = @idUsuario where id = @id

delete from tabela where id = @id

 

Para Insert:

 

Insert into tabela (id, nome, idUsuario)values(1, 'demetrio', 55) ---onde 55 é o código do usuário que inseriu,

 

para update segue o mesmo raciocínio;

 

Espero ter ajudado

Answer 13

Só corrigindo o nome;

 

Olá Antônio por Olá Otávio.

 

 

 

Answer 14

Olá Otávio,

 

Considere deixar esse log fora do SQL Server se possível. Se o objetivo é auditoria, você pode gravar em outra fonte (xml, txt, log do IIS, etc) e posteriormente fazer importações para o banco de dados se necessário.

 

Se for deixar no SQL Server, você pode evitar as triggers e utilizar somente procedures. Funcionaria da seguinte forma:

 

Instruções de INSERT

Sua procedure passa os dados e o usuário da aplicação, ela insere os dados na tabela desejada e na tabela de auditoria (nessa última, ela acompanha o usuário da aplicação).

 

Instruções de UPDATE

Sua procedure passa os dados, mas antes de atualizar ela faz um INSERT na tabela de auditoria e posteriormente faz o UPDATE no registro

 

Instruções de DELETE

Sua procedure passa os dados, mas antes de excluir ela faz um INSERT na tabela de auditoria e posteriormente faz o DELETE no registro.

 

[ ]s,

 

Gustavo