Auditoria de dados é um assunto cada vez mais quente. Saber quando, onde e quem alterou um registro é de importância vital para muitos tipos de sistemas.
O SQL Server oferece um recurso bastante simples e prático de manter essa auditoria: o uso de tabelas temporais. Este recurso surgiu com o SQL Server 2016, mas pouca gente conhece e/ou utiliza essa funcionalidade.
Como funcionam
No passado, após criar uma tabela, era necessário definir uma segunda versão da tabela para manter o histórico das mudanças, e então construir uma série de gatilhos (“triggers”) para transferir os dados da tabela principal para a histórica. Isso poderia ser bastante trabalhoso, dependendo da quantidade de tabelas que se precisasse auditar.
Com o uso da tabela temporal, ao se criar uma tabela que precisa de auditoria, é criada automaticamente uma tabela histórica que armazena todas as alterações dos registros e todos os mecanismos que irão movimentar os dados. Tudo controlado pelo próprio SGBD.
Uma vez criada a tabela temporal, todas as alterações de dados, isto é, UPDATEs e DELETEs, serão registradas na tabela histórica.
Sintaxe
A definição de uma tabela temporal exige a inclusão de alguns campos e argumentos específicos. É obrigatório que este tipo de tabela possua uma chave primária e alguns outros requisitos, como mostra o modelo a seguir:
USE [BasePrincipal] GO CREATE TABLE [NomeDaTabela] ( [CampoChave] [TipoDeDados] NOT NULL PRIMARY KEY -- obrigatório , ............ -- lista de campos , SysStartTime DateTime2 GENERATED ALWAYS AS ROW START NOT NULL -- obrigatório , SysEndTime DateTime2 GENERATED ALWAYS AS ROW END NOT NULL -- obrigatório , PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime) -- obrigatório ) WITH (SYSTEM_VERSIONING = ON (HISTORY_TABLE = [TabelaHistorica])); -- obrigatório GO
Os campos SysStartTime e SysEndTime são obrigatórios porque é através deles que identificamos a versão dos dados que buscamos. Observe que ambos são gerados automaticamente quando se insere cada registro novo. O argumento HISTORY_TABLE é opcional.
Caso ele seja omitido, o SGBD criará uma tabela histórica com o nome dbo.MSSQL_TemporalHistoryFor_@ObjectID, onde @ObjectID é o código da tabela origem, conforme registrado no catálogo do banco de dados (sys.objects).
Exemplo prático
Vejamos um exemplo prático. Vamos construir uma tabela temporal e inserir dados, como mostra o quadro a seguir.
--criando tabela temporal CREATE TABLE dbo.MinhaTabelaTemporal (ID int identity(1,1) primary key ,A int ,B int ,C AS A * B ,SysStartTime datetime2 GENERATED ALWAYS AS ROW START NOT NULL ,SysEndTime datetime2 GENERATED ALWAYS AS ROW END NOT NULL ,PERIOD FOR SYSTEM_TIME (SysStartTime,SysEndTime)) WITH(SYSTEM_VERSIONING = ON (HISTORY_TABLE = dbo.MinhaTabelaTemporal_History)) GO -- inserindo dados INSERT [dbo].MinhaTabelaTemporal (A,B) VALUES( 5, 2) INSERT [dbo].MinhaTabelaTemporal (A,B) VALUES( 6, 3) GO
Agora vejamos os conteúdos da tabela temporal e histórica.
-- lendo dados da tabela temporal SELECT * FROM [dbo].MinhaTabelaTemporal
ID | A | B | C | SysStartTime | SysEndTime |
1 | 5 | 2 | 10 | 2018-03-26 20:58:53.5168975 | 9999-12-31 23:59:59.9999999 |
2 | 6 | 3 | 18 | 2018-03-26 20:58:53.5168975 | 9999-12-31 23:59:59.9999999 |
-- lendo dados da tabela histórica SELECT * FROM [dbo]. MinhaTabelaTemporal_History
ID | A | B | C | SysStartTime | SysEndTime |
Como era de se esperar, a tabela histórica se encontra vazia, pois ela não registra operações de INSERT: apenas UPDATEs e DELETEs.
Agora vamos a outro teste, onde alteramos os dados existentes.
--alterando dados UPDATE [dbo].MinhaTabelaTemporal SET A= 15 WHERE ID = 1 DELETE FROM [dbo].MinhaTabelaTemporal WHERE ID = 2 GO
Ao consultarmos novamente as duas tabelas, constatamos que os dados antigos foram gravados na tabela histórica.
-- lendo dados da tabela temporal SELECT * FROM [dbo].MinhaTabelaTemporal
ID | A | B | C | SysStartTime | SysEndTime |
1 | 15 | 2 | 30 | 2018-03-26 21:10:21.7522415 | 9999-12-31 23:59:59.9999999 |
-- lendo dados da tabela histórica SELECT * FROM [dbo]. MinhaTabelaTemporal_History
ID | A | B | C | SysStartTime | SysEndTime |
1 | 5 | 2 | 10 | 2018-03-26 21:03:38.9078863 | 2018-03-26 21:10:21.7522415 |
2 | 6 | 3 | 18 | 2018-03-26 21:03:38.9078863 | 2018-03-26 21:10:21.7526550 |
Excluindo a tabela temporal
Caso precise excluir a tabela temporal ou queira apenas desabilitar o versionamento temporariamente, será necessário alterar o argumento SYSTEM_VERSIONING.
ALTER TABLE dbo.MinhaTabelaTemporal SET (SYSTEM_VERSIONING = OFF) ;
Uma vez desabilitado o versionamento, poderemos excluir ambas as tabelas, temporal e histórica.
--excluindo tabela temporal DROP TABLE [dbo].MinhaTabelaTemporal GO DROP TABLE dbo.MinhaTabelaTemporal_History GO
Caso você mantenha o nome default da tabela histórica, ela será automaticamente excluída quando se apagar a tabela temporal.
Comentários finais
Tabelas temporais são muito úteis e fáceis de implementar, mas você deve ter observado que elas apresentam algumas limitações em termos de auditoria.
Elas registram apenas dados dos campos existentes na tabela. Não se grava, por exemplo, quem foi a pessoa que executou a alteração nos dados. Para contornar essa questão ainda é necessário adaptar a aplicação de modo que ela registre o nome e/ou ID da pessoa que executa as operações auditadas.
De qualquer modo, a facilidade de implementação e facilidade de manutenção, que virtualmente é igual a zero, são grandes atrativos para quem deseja manter um controle detalhado da alteração de dados sensíveis.
Leituras sugeridas
- Tabelas temporais, MICROSOFT.
- Cenários de uso da tabela temporal, MICROSOFT.
- Consultando dados em uma tabela temporal com controle da versão do sistema, por MICROSOFT.
- SQL 2016 – Temporal Tables – Triggers and Historical Data Tidbits por Kevin Conan, MSDN Blogs.