none
Gerando log de Transações RRS feed

  • Pergunta

  • Pessoal tenho que desenvolver em minha aplicação um conceito de log do sistema ou seja se um usuário modificou, inseriu, deletou em uma tabela eu preciso registrar o login, à tabela, data do evento, campo, valor antigo e valor novo, com isso gostaria de ver com os colegas se existe uma forma de se fazer isso via banco de dados atualmente eu utilizo o SQL Server 2012 em meus clientes, existe uma forma de se capturar às mudanças na base e armazenar isso em uma tabela?
    quarta-feira, 11 de setembro de 2013 14:19

Respostas

Todas as Respostas

  • Eder,

    Voce pode fazer isso de duas maneiras:

    1 - Triggers de auditoria: http://stackoverflow.com/questions/1962398/creating-audit-triggers-in-sql-server

    2 - Change Data Capture: http://technet.microsoft.com/pt-br/library/cc645937.aspx

    Minha recomendacao, utilize a opcao numero 2, triggers tende a ser um veneno para a base de dados em relacao a performance.


    <b>Fabrizzio A. Caputo</b><br/> Certificações:<br/> MCT<br/> MCC<br/> Oracle OCA 11g<br/> MCITP SQL Server 2008 BI<br/> MCITP SQL Server 2008 Implementation and Maintenance<br/> MCITP SQL Server 2008 Developer<br/> ITIL V3 Foundation <br/> Blog Pessoal: <a href="http://fabrizziocaputo.wordpress.com">www.fabrizziocaputo.wordpress.com</a><br/> Email: fabrizzio.antoniaci@gmail.com

    • Sugerido como Resposta Lucas Vandor quarta-feira, 11 de setembro de 2013 16:23
    quarta-feira, 11 de setembro de 2013 14:25
    Moderador
  • Deleted
    • Sugerido como Resposta Lucas Vandor quarta-feira, 11 de setembro de 2013 16:23
    quarta-feira, 11 de setembro de 2013 14:40
  • Pessoal verifiquei o tópico mencionado e acredito que seria interessante para mim essa implementação consegui simular em minha base mas gostaria de melhorar essa trigger por exemplo:

    CREATE TABLE DedoDuro ( Data smalldatetime default GetDate(), Usuário varchar(100) default suser_sname(), Computador varchar(60) default host_name(),
      Aplicativo varchar(60) default app_name(), Tabela varchar(60), Dados xml );

    CREATE TRIGGER Ad_Produto on Produto after delete as
    begin
    declare @Dados xml;
    IF exists (select * from deleted)
      begin
      set @Dados = (select * from deleted for xml auto);
      INSERT into DedoDuro (Tabela, Dados) values
         ('Produto', @Dados);
      end;
    end;

    A tabela do tópico e à trigger funcionam perfeitamente para dele e como eu poderia alterar essa trigger para monitorar o update? pergunto isso pois não entendi somente à parte do select * from deleted de onde vem essa tabela? no caso do update é possivel armazenar o campo alterado e à informação antiga e à nova?

    quarta-feira, 11 de setembro de 2013 18:29
  • Deleted
    quarta-feira, 11 de setembro de 2013 21:11
  • Eder,

    Eu gosto da duas sugestões do Caputo, segue alguns exemplos abaixo:

    If Object_Id ('Auditoria_Logon') Is Not Null
     Drop Table Auditoria_Logon ;
    Go
    
    
    Create Table dbo.Auditoria_Logon
    
     (  id_Auditoria_Logon  Int Identity (1,1),
        [Login]        Varchar(100)           , 
        Data_Login     SmallDatetime          ,
        Usuario        Varchar(100)           , 
        Aplicacao      Varchar(100)           , 
        [Host]         Varchar(100)           , 
        [DataBase]     Varchar(100)           , 
        Evento         Varchar(Max))
    
    
    Go 
    Alter Table Auditoria_Logon Add Constraint Pk_Auditoria_Logon  Primary Key (id_Auditoria_Logon)
    Go
    
    Create NonClustered Index ID_01 On Auditoria_Logon ([Login], Data_Login ) With FillFactor = 80 
    Go 
    
    
    
    If Object_Id ('trg_Auditoria_LOgin') Is Not Null
     Drop  trg_Auditoria_LOgin ;
    Go
    
    CREATE TRIGGER trg_Auditoria_LOgin
              ON ALL SERVER FOR LOGON -- With Execute as 'sa'
              AS
    BEGIN
             
    
    Insert Into Auditoria_Logon ( Login,Data_Login,Usuario,Aplicacao,[Host],[DataBase],Evento ) 
    
      Select Login        = ORIGINAL_LOGIN()  , 
             Data_Login   = Getdate()         , 
             Usuario      = USER_NAME()       , 
             Aplicacao    = APP_NAME()        , 
             Host         = HOST_NAME()       , 
             [DataBase]   = DB_NAME()         ,
    
             Evento       = Convert(Varchar(max),EVENTDATA())
    
    Where Not Exists  ( Select * 
                            From Auditoria_Logon aa
                           Where Usuario = USER_NAME()
                             And Convert(Varchar(10), Data_Login ,113) = Convert(Varchar(10), Getdate()  ,113))
            
       END;
    
    
    
     
    
     

    Na revista SQL Magazine, senão estou enganada na edição 80 ou 82 tem um artigo meu sobre o Change Data Capture, como também, no meu blog:


    Pedro Antonio Galvão Junior [MVP | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | SorBR.Net | Professor Universitário | MSIT.com]


    sábado, 14 de setembro de 2013 12:43
  • Eu executei o exemplo abaixo:

    If Object_Id ('Auditoria_Logon') Is Not Null Drop Table Auditoria_Logon ; Go Create Table dbo.Auditoria_Logon ( id_Auditoria_Logon Int Identity (1,1), [Login] Varchar(100) , Data_Login SmallDatetime , Usuario Varchar(100) , Aplicacao Varchar(100) , [Host] Varchar(100) , [DataBase] Varchar(100) , Evento Varchar(Max)) Go Alter Table Auditoria_Logon Add Constraint Pk_Auditoria_Logon Primary Key (id_Auditoria_Logon) Go Create NonClustered Index ID_01 On Auditoria_Logon ([Login], Data_Login ) With FillFactor = 80 Go If Object_Id ('trg_Auditoria_LOgin') Is Not Null Drop trg_Auditoria_LOgin ; Go CREATE TRIGGER trg_Auditoria_LOgin ON ALL SERVER FOR LOGON -- With Execute as 'sa' AS BEGIN Insert Into Auditoria_Logon ( Login,Data_Login,Usuario,Aplicacao,[Host],[DataBase],Evento ) Select Login = ORIGINAL_LOGIN() , Data_Login = Getdate() , Usuario = USER_NAME() , Aplicacao = APP_NAME() , Host = HOST_NAME() , [DataBase] = DB_NAME() , Evento = Convert(Varchar(max),EVENTDATA()) Where Not Exists ( Select * From Auditoria_Logon aa Where Usuario = USER_NAME() And Convert(Varchar(10), Data_Login ,113) = Convert(Varchar(10), Getdate() ,113)) END;

    e apos isso não consigo conectar mais no banco de dados, da o seguinte erro:

    TÍTULO: Conectar ao Servidor
    ------------------------------

    Não é possível conectar-se a EDER-PC.

    ------------------------------
    INFORMAÇÕES ADICIONAIS:

    Falha no logon para 'sa' devido à execução do gatilho.
    Contexto do banco de dados alterado para 'master'.
    Definição do idioma alterada para Português (Brasil). (Microsoft SQL Server, Erro: 17892)

    Para obter ajuda, clique em: http://go.microsoft.com/fwlink?ProdName=Microsoft%20SQL%20Server&EvtSrc=MSSQLServer&EvtID=17892&LinkId=20476

    ------------------------------
    BOTÕES:

    OK
    ------------------------------

    terça-feira, 17 de setembro de 2013 14:10