Skip Navigation Links



Translate this page now :



»Programação
»Programação.NET
»Banco de Dados
»Webdesign
»Office
» Certificações Microsoft 4
»Treinamentos4
»Programação 4
»Webdesign«
»Office & User Tips«
»Grupos de UsuĆ”rios
»CĆ©lulas AcadĆŖmicas«
intcontpiada : 118
Scaneando
Você já está cadastrado e participa do grupo de usuários de sua cidade ? Se não, comente o porque.
 
 
FaƧa um pequeno teste com 10 questƵes de VB
.:.
Teste seus conhecimentos em Visual Basic, SQL Server e ASP 3.0 com nossas provas on-line
.:.
Aprimore seus conhecimentos em programaĆ§Ć£o com nosso treinamento on-line de lĆ³gica de programaĆ§Ć£o
.:.
Veja nosso calendƔrio de treinamentos
Gostou da PƔgina?
Então

para um amigo!

Pesquisa personalizada
Pesquisar Dicas:

 






Quer saber mais?
Certifique-se como MCP em SQL Server
Faça um treinamento na Búfalo Informática

Criando uma auditoria de dados com SQL Server


É muito comum que um sistema tenha necessidade de criar informações de auditoria, para que se tenha conhecimento de quando foi feita e quem fez a ultima modificação em um determinado registro.

A primeira opção que os programadores utilizam é fazer essa atualização por código. O problema disso é simples de entender : Está sendo mantida uma regra de negócio no client. Assim sendo, fica-se vinculado a todos os problemas de manutenção de uma regra de negócios no client, a saber :

  • Os clients ficam mais complexos. É comum que uma empresa utilize 2 ou 3 clients para a mesma aplicação. A construção de tais clients se torna mais complexa.
  • O sistema fica sujeito a erro. Se um dos clients utilizados pela empresa contiver um erro de programação todo o sistema fica comprometido.
  • Alterações feitas por fora do client não registram os 2 campos adicionais.


Desta forma o ideal é mantermos esta regra de negócios no servidor, para desta forma não estarmos sujeitos a esses 3 problemas. Neste casso essa regra terá que ser mantida em um trigger no servidor de dados. Tal trigger deverá estar ligado tanto ao evento de insert quanto ao evento de update, pois em ambos os casos os 2 campos de auditoria precisam ser atualizados.

Supondo que temos uma tabela chamada "tabteste" com os campos de auditoria criados como "usuario" e "dataAlterado" e um campo chave chamado código. Vejamos um exemplo de uma tabela :


Create table tabteste (
codigo int primary key,
nome varchar(40) not null,
endereco varchar(50) not null,
usuario varchar(30) null,
dataAlterado smalldatetime )


E o código do trigger :

Create Trigger tru_audit on tabteste for insert,update as
update tabteste set usuario=suser_name(),dataAlterado=getdate() where codigo in (select codigo from inserted)


Com apenas uma instrução o trigger resolve o problema : utilizando uma subquery é feito um update na tabela tabteste em todos os registros que acabaram de ser alterados (os registros que estão na tabela inserted) atualizando o usuario e a data de alteração. Assim sendo toda atualização ou inserção nesta tabela terá automaticamente estes dois campos atualizados, sem necessidade de que a aplicação faça nada.

Os mais observadores irão notar que a aplicação ainda terá que lidar com a existência de dois campos adicionais na tabela. Para contornar isso, basta que a aplicação esteja utilizando uma view. Veja :

Create View vwTabTeste as
select codigo, nome,endereco from tabteste


Pode-se fazer um insert, por exemplo, na view, como se fosse uma tabela :

insert into vwTabTeste Values (02,'teste','rua a')


Isso possibilita inclusive que o DBA utilize este recurso com uma aplicação que já está pronta, sem que seja necessário alterar nada no código da aplicação. Se aplicação usa uma tabela TabTeste, pode-se renomear a tabela para TabTesteOriginal, adicionar os 2 campos nesta tabela TabTesteOriginal e criar uma view chamada TabTeste sem estes 2 campos. A aplicação irá chamar a view sem saber que trata-se de uma view, nenhuma diferença será notada na aplicação.

Mas estz solução, comum até a versão 7 do SQL Server, tem um ponto fraco : O Trigger faz uma atualização na própria tabela na qual ele está rodando. Assim sendo, ao fazer essa atualização ele seria chamado novamente, e assim continuamente, entrando em loop.

Será mesmo ?

Entre as opções de um banco de dados no SQL Server vocês encontram a opção recursive triggers. Sabendo o perigo do loop dentro de um trigger a Microsoft transformou isso em uma opção configuravel dentro do banco de dados : Quando recursive triggers está marcado o trigger pode chamar ele mesmo. Quando não está, não pode.

Menos mal. Se recursive triggers estiver desmarcado o trigger acima funciona, se estiver marcado não (entra em loop).

Só isso já manteria o Trigger funcionando, mas sejamos perfeccionistas : Depender de uma configuração do banco que pode ou não estar setada pode nos causar problemas. Se algum desavisado ativar a opção Recursive Triggers, ao rodar um insert equivalente ao que mostramos acima teremos o seguinte erro :

Server: Msg 217, Level 16, State 1, Procedure tru_audit, Line 3
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).


O Trigger ficaria chamando a si mesmo eternamente se não fosse pelo limite máximo de chamadas no sql server (nesting level) que é de 32.

Temos então duas opções :

  • No SQL Server 7.0 utilizar a variável @@NestLevel
  • No SQL Server 2000 usar os triggers do tipo INSTEAD

@@NestLevel

A variável @@Nestlevel contém um número que indica o nível de recursividade que o trigger já sofreu. Se estiver em 0, então ainda não passou por recursividade alguma. Assim sendo, podemos só fazer o update quando o @@Nestlevel for 0. Continuaremos tendo duas chamadas do trigger, mas na segunda ele não irá fazer nada e consequentemente parará a recursividade.

Veja como fica nosso Trigger :

Create Trigger tru_audit on tabteste for insert,update as
If @@NestLevel=0
update tabteste set usuario=suser_name(),dataAlterado=getdate() where codigo in (select codigo from inserted)

Com este trigger não importa mais a configuração da opção Recursive Triggers : Se estiver desmarcada, o trigger roda apenas uma vez

Instead Of

No SQL Server 2000, porém, o problema pode ser resolvido de uma forma melhor. Existe um tipo especial de trigger chamado trigger de Instead. Este trigger não acontece após o evento (insert,update), mas em substituição ao evento. Assim sendo, o insert e o update não serão feitos pelo SQL Server, o trigger é que deverá decidir se irá faze-los ou não. Esse tipo de trigger não sofre recursividade (um update feito pelo trigger não dispara novamente o trigger) e consequentemente resolve nosso problema.

Precisaremos porém de 2 triggers de Instead : Um de Update e outro de Insert, não podemos mais fazer as 2 operações no mesmo código.

Veja o trigger de Instead para Insert :


Create Trigger tru_auditIns on tabteste instead of insert as
insert into tabteste select codigo,nome,endereco,suser_name(),getdate() from inserted


Mais uma vez com uma única instrução, um insert/select, resolvemos nosso problema. Observe a forma como o select foi montado : Ele pega os 3 campos que a tabela tem, mas ao invés de pegar os 2 campos usados na auditoria ele traz o nome do usuário e a data para que o insert já seja feito de forma atualizada na tabela tabteste.

Para o trigger de update teremos que fazer um update relacionando as duas tabelas : A tabela inserted e a tabela tabteste.

Veja como fica :

Create Trigger tru_auditUPD on tabteste instead of update as

update tabteste
set nome=i.nome,
endereco=i.endereco,
usuario=system_user,
dataAlterado=getdate()
from tabteste inner join inserted i on tabteste.codigo=i.codigo


Observe que a chave não sofre atualização e que no momento da atualização aproveitamos para gravar as duas colunas de auditoria.

Desta forma resolvemos de forma simples e confiável o problema de auditoria em alterações de dados utilizando os recursos do servidor.

Dennes Torres
MCSD,MCSE,MCDBA





Envie seus comentįrios sobre este artigo

Nome :

E-mail :

Comentários :


Avise-me quando houverem novos comentįrios nesta pįgina

Veja abaixo os comentários já enviados :

Nome : Rose E-Mail : rmmferreira@hotmail.com
Gostaria de agradecer pelo comentário acima, pois me foi muito útil e esclarecedor.
Nome : umberto E-Mail : umberto@pm.ro.gov.br
OS CONSELHOS DISPONÍVEIS NO ARTIGO SÃO DE GRANDE VALIA PARA QUEM PROCURA SOLUÇÕES PARA ADMINISTRAR SEUS BANCOS DE DADOS. PARABÉNS!
Nome : Maralisa E-Mail : mara_crazy7@hotmail.com
Este artigo é excelente, pois pude passar para meus colegas (estudantes de informática) e com certeza irá engrandecer a nossa célula acadêmica, na qual faço parte e sou lider (moderadora).
Nome : Gabriel Pacheco E-Mail : gabrielpacbr@yahoo.com.br
Boa tarde Dennes,

Primeiramente meus parabéns pelo artigo, realmente ficou muito bom mesmo e aproveitanto o comentário gostaria de questionar se teria com fazermos o trigger em várias tabelas, gravando as informações em apenas um tabela, ou seja, isto tem por objetivo fundamentar uma auditoria e disponibilizar as informações em um único local sem mexer em nada na estrutura atual do banco de dados.

Grato,

Gabriel Pacheco
Nome : Gilberto Carneiro E-Mail : webportal@terra.com.br
Muito bem. Bastante esclarecedor.

Nome : Marcos André Maia Ponciano E-Mail : andremamp@yahoo.com.br
Muito bom... procurei varios materiais na net, mas somente este econtrei o exemplo que precisava.. para eu fazer um update no registro atual do proprio update,
Valeu pelo artigo!!!
Nome : sergio E-Mail : sergioviperto@yahoo.com.br
ola

gostaria de saber como faço pra renomear uma tabela no sql server 2005?
sem perder os dados

desde ja abrigado
Nome : Consultor ERP E-Mail : fabioagora@hotmail.com
Pessoal,

estou batendo cabeça a dias no sentido de uma trigger no RM SISTEMAS(RM NUCLEUS) pois ao tentar inserir um codigo em uma tabela titmmov.codtb1flx para que esse codigo insira na titmmovratdep o codigo da titmmov.codtb1flx(que é a mesma mascara e a mesma codificacao da gdepto) porem esta dando erro de transaction roolback, alguem pode me ajudar?

CREATE TRIGGER RATEIO ON TITMMOV
AFTER INSERT AS
INSERT INTO TITMMOVRATDEP SELECT CODCOLIGADA, TITMMOV.IDMOV, TITMMOV.NSEQITMMOV, CODFILIAL,
TITMMOV.CODTB1FLX, TITMMOV.QUANTIDADE*TITMMOV.PRECOUNITARIO, 100 FROM TITMMOV
Desde ja agradeço a todos.


Consultor ERP
Nome : Gladson E-Mail : gladsonleite@yahoo.com.br
Dennes,

Estou com uma dúvida, essa auditoria contenpla apenas uma tabela, no caso o banco de dados que trabalho tem 690 tabelas como fazer uma auditoria pra tanta tabela, haja trigger! Tens alguma idéia?

Por enquanto prefiro a implementação via programação...

Um abraço,

Gladson Leite Ponciano
Analista de Suporte
Nome : Ademilso Peres E-Mail : ademilsoperes@uol.com.br
Mas como o trigger pode saber o nome do usuario em uma aplicãção web se estiver
usando o usuário asp.net ?
Nome : dennes torres E-Mail : dennes@bufaloinfo.com.br

Nesse caso não pode, você precisa criar soluções alternativas com stored procedures e transmitindo o nome do usuário web como parâmetro.

[]s

Dennes
Nome : Andre-MAMP E-Mail : andremamp@fake.com
Achei muito bom a trigger e ate deixei um comentario... Mas seria legal se o e-mail da gente naum ficasse exposto ao publico NET eheh.
Caso contrario gostaria de deleter meu posto e postaria novamente, mas nao com email verdadeiro...
boa sorte e valeu pela dica. muito show!
Nome : Andremamp E-Mail : andremamp@andre.com
oi tudo bem.
poderia vc por favor deleter ou mudar apenas o meu email (andremamp@yahoo.com.br) no post anterior q eu fiz?
Nome : chJtjQXx E-Mail : greg@zigzag.co.za
??UBUNTU7.04?apt-get???bzflag,,?????????:X Error of failed rseuqet: BadValue (integer parameter out of range for operation) Major opcode of failed rseuqet: 134 (XFree86-VidModeExtension) Minor opcode of failed rseuqet: 10 (XF86VidModeSwitchToMode) Value in failed rseuqet: 0xc1 Serial number of failed rseuqet: 144 Current serial number in output stream: 146???????,????????????~~
Nome : 1 E-Mail : 1
-1'
Nome : OzlsOehF E-Mail : pxiptafdlqb@yahoo.com
sehr schön deine Tasche ,auch die Koiestmktasche gefällt mir sehr, muss jetzt auch versuchen Taschen zu nähen, habt ihr auch so kühles Wetter wie wir? lg

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::
Conheça mais sobre o nosso site :

::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::::



Quer saber mais?
Faça um curso na Búfalo Informática, Treinamento e Consultoria e
Prepare-se para o Mercado!
Veja o que a Búfalo tem para você.

ļæ½ BĆŗfalo InformĆ”tica, Treinamento e Consultoria - Rua Ɓlvaro Alvim, 37 Sala 920 - CinelĆ¢ndia - Rio de Janeiro / RJ
Tel.: (21)2262-1368 (21) 9240-5134 (21) 9240-7281 e-Mail:
contato@bufaloinfo.com.br