Com a expansão das leis de proteção a dados, como GDPR e LGPD, tem se tornado mais frequente a necessidade de guardarmos o histórico completo das informações que armazenamos (também conhecido como “metadados dos nossos dados”, ou “linhagem dos dados”).

Considerando o cadastro de um cliente, temos como exemplo disso o armazenamento de informações como: Quando o cliente foi cadastrado e quais eram os dados originais; Quais alterações foram feitas no cadastro ao longo do tempo; Alguém excluiu o dado? Quem fez isso e quando; Etc…

Em geral, tais controles são feitos através de logs de sua aplicação, processo ETL ou afins, o que se torna caro e trabalhoso, pois demanda analise e codificação manual para cada processo ou aplicativo específico.

Fornecer soluções efetivas, rápidas e baratas para os clientes é uma característica fundamental de técnicos que ganham como diretores, por isso, hoje veremos uma forma de você implementar auditoria e versionamento de dados no SQL sem ter de alterar uma só linha de código das suas aplicações!

Assista aqui ao vídeo de demonstração:

SYSTEM VERSIONED | TEMPORAL TABLES

A indecisão sobre o nome da tecnologia não condiz com o quanto ela é efetiva e simples de implementar!

Como vimos no vídeo, são apenas 2 passos para ativarmos versionamento em qualquer tabela do SQL:

– Passo 1: Adicionamos duas colunas (no exemplo, dt_valido_de, dt_valido_ate) que armazenarão as datas de validade do registro na tabela;

– Passo 2: Incluímos a propriedade “period for system_time”, indicando que o histórico da tabela deverá ser controlado usando as duas colunas que criamos;

– Passo 3: Ativamos o controle usando o comando “system_versioning = ON” e indicando o nome que será usado para a tabela de histórico.

REPLICAÇÃO AUTOMÁTICA DE ALTERAÇÕES DDL

Concluída a configuração, o SQL rastreará todas as alterações de dados que ocorrerem na tabela de forma automática, incluindo mudanças de estrutura (ex: inclusão ou exclusão de colunas) que você faça na tabela de origem, as quais serão replicadas automaticamente para a tabela de histórico!

Quem ai já teve de fazer esse tipo de controle manualmente? Poxa, esse recurso do SQL 2016 é fantástico em!

TIPOS DE PESQUISA EM TEMPORAL TABLES

Ativada a auditoria, as possibilidades de consulta da linhagem de dados são as mais diversas possíveis! No vídeo, vimos alguns dos exemplos mais comuns:

– Identificar dados que sofreram quaisquer alterações ao longo de sua vida;

– Identificar dados que nunca sofreram quaisquer alterações desde sua inserção;

– Identificar registros excluídos;

– Identificar registros alterados;

– Listar a linhagem dos dados (dado atual válido + histórico de vida);

– Consultar uma foto da tabela em uma data específica no passado (a viagem no tempo usando o SQL).

Conclusão

Quem já teve de implementar controles como esse de forma manual, antes do SQL2016, certamente ficou com largo sorriso ao ver essa novidade.

Em tempos de LGPD no Brasil, uma funcionalidade como temporal tables é realmente uma mão na roda, pois nunca foi tão simples fazer auditoria e versionamento de dados no SQL como é agora.

Abraço do seu amigo Josué

Script da aula

----------------------------------------------------------------------------------------------------------------------------
-- Auditoria de dados sem esforço no SQL...
----------------------------------------------------------------------------------------------------------------------------

--------------------------------------------------------------
-- Banco de testes
--------------------------------------------------------------
use master
go
drop database if exists curso
go
create database Curso
go
use Curso

create table pessoas (
	id smallint identity(1,1) not null constraint pk_colaboradores primary key (id),
	nm varchar(100) not null,
	ds_profissao varchar(100) not null,
	vl_salario money not null
)
insert into pessoas (nm, ds_profissao, vl_salario) values 
	('Agricola Terra do Canto', 'Agricultor', 10000),
	('Cesária de Lima', 'Pediatra', 10000),
	('Delícia de Azevedo', 'Chefe de cozinha', 10000),
	('Elias Mattar', 'Soldado', 10000),
	('Feliciano Tristão', 'Psicólogo', 10000),
	('Mainframe da Cunha', 'Programador', 10000),
	('Salvador das Dores Fortes', 'Anestesista', 10000)
go


--------------------------------------------------------------
-- Versionando uma tabela existente
--------------------------------------------------------------

-- #1: Incluir colunas obrigatórias para versionamento
alter table pessoas add
	dt_valido_de datetime2 generated always as row start constraint df_de  default '2021-01-01', -- data base dos registros (quando você implantou o controle)
	dt_valido_ate datetime2 generated always as row end  constraint df_ate default '9999-12-31 23:59:59.9999999', -- maior data/hora do SQL (quando o registro expirará se não houver alterações)
	period for system_time(dt_valido_de, dt_valido_ate)
go

-- #2: Ativar versionamento (a tabela histórica é gerida automaticamente) 
alter table pessoas set (system_versioning = ON (history_table = dbo.pessoas_hist))
go


-- #3: Visualizer tabelas 
-- Icone da tabela no SSMS é alterado para um "reloginho"
-- Tabela histórica aparece como sub-item da tabela monitorada na arvore do SSMS
select * from pessoas
select * from pessoas_hist
go


--------------------------------------------------------------
-- Alterações de dados para teste do versionamento
--------------------------------------------------------------
-- Inserir pessoas
insert into pessoas (nm, ds_profissao, vl_salario) values 
	('Um Dois Três de Oliveira Quatro', 'Matemático', 10000),
	('Amaro Miakasa', 'Arquiteto', 10000)
go

-- Atualizar o salário de 3 pessoas
update pessoas set vl_salario = 20000 where nm in ('Mainframe da Cunha', 'Salvador das Dores Fortes', 'Delícia de Azevedo')
go

-- Atualizar a profissão de 1 pessoa
update pessoas set ds_profissao = 'Programador' where nm = 'Salvador das Dores Fortes'
go

-- Excluir 1 pessoa
delete from pessoas where nm = 'Elias Mattar'
go
 

--------------------------------------------------------------
-- Testes de consulta aos dados versionados
--------------------------------------------------------------
-- Pessoas que não sofreram alterações desde a implantação do controle (data de validade inicial = data de implantação do controle):
select 
	p.*
from pessoas as p  
left join pessoas_hist as ph on p.id = ph.id
where p.dt_valido_de = '2021-01-01'
go


-- Pessoas que não sofreram alterações desde o insert original (existem na tabela principal, mas não na tabela de histórico):
select 
	p.*
from pessoas as p  
left join pessoas_hist as ph on p.id = ph.id
where ph.id is null
go


-- Pessoas excluídas (existem na tabela de histórico e não existem na tabela principal):
select 
	*
from pessoas_hist as ph 
left join pessoas as p on p.id = ph.id
where p.id is null
go


-- Pessoas ativas que sofreram ao menos 1 alteração (existem em ambas as tabelas)
select 
	p.*, ph.*
from pessoas as p  
inner join pessoas_hist as ph on p.id = ph.id
go


-- Dados vigentes + seus históricos completos em um mesmo resultset:
select * from pessoas 
FOR SYSTEM_TIME ALL 
order by id, dt_valido_ate
go


-- A "viagem no tempo"... Consultar a "foto da tabela" exatamente como estava em datas passadas!
declare @ontem datetime2 = dateadd(dd, -1, sysutcdatetime()) 
select * from pessoas
FOR SYSTEM_TIME AS OF @ontem
order by id
go


--------------------------------------------------------------
-- Alterações estruturais na tabela de origem são replicadas automáticamente!
--------------------------------------------------------------
-- Inclusão de coluna
alter table pessoas add dt_nascimento date
go

-- Coluna incluida em ambas as tabelas
select * from pessoas
select * from pessoas_hist
go