none
Rastreabilidade em Tabela RRS feed

  • Pergunta

  • Bom dia,

    Tenho uma tabela no SQL 2014, (tabela OCTG), gostaria de uma ajuda de como faço a rastreabilidade dos usuários que modificaram o conteúdo da mesma. Obrigado pela ajuda.


    segunda-feira, 20 de fevereiro de 2017 14:42

Respostas

Todas as Respostas

  • se vc quer algo para auditar posteriormente , vc pode criar uma trigger para a tabela , e programa-la para recuperar o usuario logado que está fazerndo a alteração

    seque um artigo bacana sobre o assunto

    Triggers no SQL Server

    CREATE TRIGGER


    Também tem um artigo do DIRCEU Resende muito bom 

    Como criar uma trigger de Auditoria para logar a manipulação de objetos no SQL Server

    Por favor,  marque-o como respondidas se está respondeu a sua pergunta 
    ou marcá-lo como útil se está ajudou a resolver o seu problema 
    Wesley Neves
    MTA-Database Fundamentals 
    Analista Desenvolvedor.NET


    Wesley Neves

    segunda-feira, 20 de fevereiro de 2017 14:59
  • Bulcão,

    Outras duas alternativas interessantes que podem complementar a resposta do Wesley seriam Change Data Capture conhecida como CDC e também a Change Trancking, ambos os recursos adicionados ao SQL Server a partir da versão 2008 que possuem o objetivo de permitir rastear as manipulações de dados ocorridas em um determinada tabela.

    Veja se estes exemplos podem ajudar:

    -- Habilitando o CDC para o Banco de Dados --
    Use SQLMagazine
    Go
    
    Exec sys.sp_cdc_enable_db
    Go
    
    -- Desabilitando o CDC para o Banco de Dados --
    Use SQLMagazine
    Go
    
    Exec sys.sp_cdc_disable_db
    Go
    
    -- Criando a Tabela de Exemplo --
    Create Table Produtos
     (Codigo Int Identity(1,1),
       Descricao VarChar(20))
    Go
    
    -- Adicionando a Chave Primaria --
    Alter Table Produtos
        Add Constraint [PK_Codigo_Produtos] Primary Key (Codigo)
    Go
    
    -- Inserindo a Massa de Registros para Teste --   
    Declare @ContadorRegistros Int
    Set @ContadorRegistros=1
    
    While @ContadorRegistros <=1000
     Begin
     
      If @ContadorRegistros =1
       Insert Into Produtos Values ('Produto Nº: 1')
      Else
       Insert Into Produtos Values ('Produto Nº: '+Convert(VarChar(4),@@Identity+1))
       
       Set @ContadorRegistros += 1;
     End
     
    -- Visualizando os Dados --   
    Select * from Produtos
          
    -- Habilitando o Change Data Capture para trabalhar sobre a table Produtos --
    EXECUTE sys.sp_cdc_enable_table
        @source_schema = N'dbo',
        @source_name = N'Produtos',
        @role_name = N'cdc_Admin';
    GO
    
    -- Retornando todas as linhas capturadas pelo CDC --
    DECLARE @from_lsn binary(10), 
                      @to_lsn binary(10)
    
    SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Produtos')
    SET @to_lsn   = sys.fn_cdc_get_max_lsn()
    
    SELECT * FROM cdc.fn_cdc_get_all_changes_dbo_Produtos
      (@from_lsn, @to_lsn, N'all')
    Go
    
    -- Inserindo novos dados --
    Insert Into Produtos Values ('Produto Nº: '+Convert(VarChar(4),@@Identity+1))
    Go
    
    -- Atualizando dados já existentes --
    Update Produtos
    Set Descricao= Descricao+' - Upd'
    Where Codigo Between 11 And 21
    Go
    
    -- Retornando todas as linhas capturadas pelo CDC com Net Changes--
    DECLARE @from_lsn binary(10), 
                      @to_lsn binary(20)
    
    SET @from_lsn = sys.fn_cdc_get_min_lsn('dbo_Produtos')
    SET @to_lsn   = sys.fn_cdc_get_max_lsn()
    
    SELECT * FROM cdc.fn_cdc_get_net_changes_dbo_Produtos
                                  (@from_lsn, @to_lsn, N'all')
    GO
    
    -- Retornando as colunas utilizadas pelo CDC para Captura --
    Execute sys.sp_cdc_get_captured_columns 
                             @capture_instance = N'dbo_Produtos';
    Go
    
    -- Retornando informações de configuração da captura de dados de alteração de uma tabela específica --
    Execute sys.sp_cdc_help_change_data_capture 
                              @source_schema = N'dbo', 
                              @source_name = N'Produtos';
    Go
    
    -- Retornando informações de configuração da captura de dados de alteração de todas as tabelas --
    EXECUTE sys.sp_cdc_help_change_data_capture;

    --create test DB
    USE master;
    GO
    
    CREATE DATABASE DB_test;
    GO
    
    ALTER DATABASE DB_test SET
    CHANGE_TRACKING = ON
     (AUTO_CLEANUP = ON,          -- automatic tracking table clean up process
      CHANGE_RETENTION = 1 HOURS  -- specify the time frame for which tracked information will be maintained -- 
    );
    GO
    
    --create test table
    USE DB_test;
    GO
    
    CREATE TABLE dbo.tb
    (id int
     CONSTRAINT PK_tb_id PRIMARY KEY,
     col1 int,
     col2 varchar(10),
     col3 nvarchar(max),
     col4 varbinary(max));
    GO
    
    ALTER TABLE dbo.tb
    ENABLE CHANGE_TRACKING
     WITH(TRACK_COLUMNS_UPDATED = ON  -- With this option, you can include columns also whose values were changed
     );
    GO
    
    SELECT CHANGE_TRACKING_CURRENT_VERSION() as Currentversion,
           CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb')) as minvalidversion;
    GO
    
    -- testing
    
    -- a.insert data 
    INSERT dbo.tb(id, col1, col2, col3, col4)
    VALUES(1,1, 'AA', 'AAA', 0x1),
    	  (2,2, 'BB', 'BBB', 0x2),
          (3,3, 'CC', 'CCC', 0x2);
     
    SELECT CHANGE_TRACKING_CURRENT_VERSION() as Currentversion,
           CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb')) as minvalidversion,
           *
    FROM CHANGETABLE(CHANGES dbo.tb, 0) CHG LEFT JOIN dbo.tb DATA
                                             ON DATA.id = CHG.id;
     
    -- b. update data 
    BEGIN TRAN;
    UPDATE dbo.tb SET
    col1 = 11
    WHERE id = 1;
     
    UPDATE dbo.tb SET
    col1 = 111
    WHERE id = 1;
    COMMIT TRAN;
     
    SELECT CHANGE_TRACKING_CURRENT_VERSION() as Currentversion,
           CHANGE_TRACKING_MIN_VALID_VERSION(OBJECT_ID(N'dbo.tb')) as minvalidversion,
           *
    FROM CHANGETABLE(CHANGES dbo.tb, 0) CHG LEFT JOIN dbo.tb DATA
                                             ON DATA.id = CHG.id;


    Pedro Antonio Galvao Junior [MVP | MCC | Microsoft Evangelist | Microsoft Partner | Engenheiro de Softwares | Especialista em Banco de Dados | Professor Universitário | @JuniorGalvaoMVP | http://pedrogalvaojunior.wordpress.com]

    quarta-feira, 22 de fevereiro de 2017 18:28