Para demonstrar a utilização da cláusula OUTPUT, vou usar como exemplo a tabela Sales.CreditCard do banco de dados AdventureWorks. Esta será a tabela a ser … vamos dizer auditada e terá como finalidade monitorar os usuários que fizeram alterações nos números dos cartões de crétido. O script abaixo é o script original de criação da tabela:
CREATE TABLE [Sales].[CreditCard](
[CreditCardID] [int] IDENTITY(1,1) NOT NULL,
[CardType] [nvarchar](50) NOT NULL,
[CardNumber] [nvarchar](25) NOT NULL,
[ExpMonth] [tinyint] NOT NULL,
[ExpYear] [smallint] NOT NULL,
[ModifiedDate] [datetime] NOT NULL
CONSTRAINT [DF_CreditCard_ModifiedDate] DEFAULT (getdate()),
CONSTRAINT [PK_CreditCard_CreditCardID] PRIMARY KEY CLUSTERED
([CreditCardID] ASC))
Conhecendo os objetivos e a estrutura da tabela a ser auditada, precisamos então ter uma segunda tabela que será usada para armazenar os dados da auditoria, chamaremos aqui de “tabela espelho”. Uma observação muito importante sobre esta tabela é que para receber as linhas da cláusula OUTPUT ela não pode possuir triggers, check contraints ou qualquer foreign key referenciando suas colunas ou colunas de outras tabelas.
O script apresentado logo abaixo é o script que cria a tabela para auditarmos as operações de INSERT, UPDATE e DELETE sobre a tabela Sales.CreditCard. Notem que esta tabela possui duas entradas para cada coluna da tabela a ser auditada. A coluna com o prefixo INSERT armazenará os dados inseridos e a coluna com o prefixo DELETE os dados excluídos. Outro ponto de observação são as duas últimas colunas do script (INSERT_Usuario e DELETE_Usuario). Estas colunas existem apenas na tabela espelho e serão utilizadas para guardar o nome do usuário que modificou o registro na tabela Sales.CreditCard, para isso usarei a função de sistema SUSER_SNAME() que retorna o nome do usuário que executou a operação.
CREATE TABLE [Sales].[CreditCard_Espelho](
AuditoriaID int IDENTITY(1,1) NOT NULL PRIMARY KEY,
ComandoDML varchar(10),
INSERT_CreditCardID int NULL,
DELETE_CreditCardID int NULL,
INSERT_CardType [nvarchar](50) NULL,
DELETE_CardType [nvarchar](50) NULL,
INSERT_CardNumber [nvarchar](25) NULL,
DELETE_CardNumber [nvarchar](25) NULL,
INSERT_ExpMonth [tinyint] NULL,
DELETE_ExpMonth [tinyint] NULL,
INSERT_ExpYear [smallint] NULL,
DELETE_ExpYear [smallint] NULL,
INSERT_ModifiedDate [datetime] NULL,
DELETE_ModifiedDate [datetime] NULL,
INSERT_Usuario varchar(50) NULL DEFAULT SUSER_SNAME(),
DELETE_Usuario varchar(50) NULL DEFAULT SUSER_SNAME()
)
Agora que você conhece a estrutura da tabela espelho, use o script abaixo para executar um INSERT sobre a tabela Sales.CreditCard.
— INSERT
INSERT Sales.CreditCard (CardType,CardNumber,ExpMonth,ExpYear,ModifiedDate)
OUTPUT ‘INSERT’,
Inserted.CreditCardID,
Inserted.CardType,
Inserted.CardNumber,
Inserted.ExpMonth,
Inserted.ExpYear,
Inserted.ModifiedDate
INTO Sales.CreditCard_Espelho
(ComandoDML,INSERT_CreditCardID,INSERT_CardType,INSERT_CardNumber,INSERT_ExpMonth,INSERT_ExpYear,
INSERT_ModifiedDate)
VALUES (‘Visa’,’11111111111111′,12,2014,’20090305′)
Note que a cláusula OUTPUT esta posicionada entre as palavras chaves INSERT e VALUES (negrito). Em verdade, observando atentamente é possível notar que a instrução de INSERT é muito semelhante a uma instrução de INSERT qualquer, a grande diferença no uso da cláusula OUTPUT está no fato de ter que acrescentar o bloco destacado em vermelho. Tirando este bloco, a instrução de INSERT ficaria como abaixo:
INSERT Sales.CreditCard (CardType,CardNumber,ExpMonth,ExpYear,ModifiedDate)
VALUES (‘Visa’,’11111111111111′,12,2014,’20090305′)
Outro ponto importante, notem que após o INTO Sales.CreditCard_Espelho tem-se então a lista das colunas da CreditCard_Espelho onde os dados devem ser inseridos. Como a operação de INSERT utiliza apenas a tabela virtual Inserted, a lista de colunas possui apenas as colunas correspondentes.
Assim como uma trigger de INSERT, a cláusula OUTPUT também faz uso da tabela virtual Inserted para obter os dados afetados pela operação DML de INSERT. Quando usamos uma trigger, esta faz uso de duas tabelas chamadas INSERTED e DELETED. Estas tabelas são tabelas virtuais que existem apenas durante o tempo de execução da trigger e podem ser usadas para capturar o antes e depois de uma operação DML. As tabelas são afetadas de forma diferente dependendo da operação DML executada. Abaixo a Tabela mostra o que contém em cada tabela de acordo com a operação DML.
Operação | Tabela INSERTED contém | Tabela DELETED contém |
DELETE | Não possui registros | Registros excluídos |
INSERT | Novos registros | Não possui registros |
UPDATE | Novos registros | Registros antigos |
A cláusula OUTPUT faz uso destas mesmas tabelas virtuais, então quando você executa um INSERT a tabela virtual Inserted possui o dados inseridos na tabela auditada, e a cláusula OUTPUT utiliza a tabela virtual para capturar os dados e inserí-los na tabela CreditCard_Espelho.
O resultado da operação de INSERT sobre a tabela Sales.CreditCard pode ser visto na figura abaixo. Este é obtido executando este script…
SELECT TOP 5 * FROM Sales.CreditCard ORDER BY ModifiedDate DESC
SELECT ComandoDML,INSERT_CreditCardID,INSERT_CardType,INSERT_CardNumber,INSERT_ExpMonth,INSERT_ExpYear,
INSERT_ModifiedDate, INSERT_Usuario
FROM Sales.CreditCard_Espelho
Observe que o novo registro foi inserido sobre a tabela Sales.CreditCard e ao mesmo tempo um registro também foi inserido sobre a tabela
Sales.CreditCard_Espelho indicando ainda o usuário que executou a operação.
Para uma operação de UPDATE, o que muda é que devemos pegar os dados não apenas da tabela Inserted, mas também da tabela deleted. Isso porque quando ocorre uma operação de UPDATE o que ocorre na prática é a exclusão do dados antigos e a inclusão dos novos dados, com isso durante uma operação de UPDATE a tabela Inserted armazena os novos dados e a tabela deleted armazena os dados antigos ou excluídos.
Observando o script abaixo você notará que na lista de colunas passada à cláusula OUTPUT estou usando não apenas a tabela Inserted, mas também a Deleted (bloco laranja). No mais, observe que após o INTO Sales.CreditCard_Espelho informo não apenas as colunas de prefixo INSERT, mas também as de prefixo DELETE.
— UPDATE
UPDATE Sales.CreditCard
SET CardNumber= ‘11111111111122’
OUTPUT ‘UPDATE’,
Inserted.CreditCardID,
Deleted.CreditCardID,
Inserted.CardType,
Deleted.CardType,
Inserted.CardNumber,
Deleted.CardNumber,
Inserted.ExpMonth,
Deleted.ExpMonth,
Inserted.ExpYear,
Deleted.ExpYear,
Inserted.ModifiedDate,
Deleted.ModifiedDate
INTO Sales.CreditCard_Espelho
(ComandoDML,INSERT_CreditCardID,DELETE_CreditCardID,INSERT_CardType,DELETE_CardType,
INSERT_CardNumber,DELETE_CardNumber,INSERT_ExpMonth,DELETE_ExpMonth,INSERT_ExpYear,DELETE_ExpYear,
INSERT_ModifiedDate,DELETE_ModifiedDate)
WHERE CreditCardID=19249
O resultado da operação de UPDATE pode ser visto na figura abaixo. Note que na tabela Sales.CreditCard o número do cartão de crédito para o CreditCardID 19249 foi alterado de 11111111111111 para 11111111111122. Na tabela de auditoria CreditCard_Espelho podemos notar que tanto as colunas de prefixo INSERT quanto de prefixo DELETE foram preenchidas. Na coluna DELETE_CreditCrad temos o número antes da alteração e na coluna INSERT_CredtCard o novo número do cartão de crédito.
O resultado e obtido executando este script…
SELECT TOP 5 * FROM Sales.CreditCard ORDER BY ModifiedDate DESC
SELECT ComandoDML,INSERT_CreditCardID,INSERT_CardType, INSERT_CardNumber,INSERT_ExpMonth,INSERT_ExpYear,
INSERT_ModifiedDate, INSERT_Usuario
FROM Sales.CreditCard_Espelho
SELECT ComandoDML,DELETE_CreditCardID,DELETE_CardType, DELETE_CardNumber,DELETE_ExpMonth,DELETE_ExpYear,
DELETE_ModifiedDate, DELETE_Usuario
FROM Sales.CreditCard_Espelho
Para finalizar, um exemplo demonstrando o uso da cláusula OUTPUT em uma operação de DELETE. Como vocês já devem ter notado, para uma operação de DELETE apenas as colunas com prefixo DELETE para a tabela CreditCard_Espelho serão preenchida. O script para a operação de DELETE pode ser visto abaixo e o exemplo exclui registro de CreditCardID 19249 da tabela Sales.CreditCard
— DELETE
DELETE FROM Sales.CreditCard
OUTPUT ‘DELETE’,
Deleted.CreditCardID,
Deleted.CardType,
Deleted.CardNumber,
Deleted.ExpMonth,
Deleted.ExpYear,
Deleted.ModifiedDate
INTO Sales.CreditCard_Espelho
(ComandoDML,DELETE_CreditCardID,DELETE_CardType, DELETE_CardNumber,DELETE_ExpMonth,DELETE_ExpYear,
DELETE_ModifiedDate)
WHERE CreditCardID=19249
Podemos ver o resultado da operação na figura abaixo. Note que o registro de CreditCardID 19249 foi excluído da tabela principal Sales.CreditCard. Para a tabela Sales.CreditCard_Espelho podemos notar também que as colunas de prefixo INSERT estão como NULL, isso ocorre porque para operações de DELETE não há inserção de novos dados. Já para as colunas de prefixo DELETE, temos o registro dos dados excluídos e quem o excluiu.
Dicas para a utilização do OUTPUT
Como vocês devem ter notado, a cláusula OUTPUT pode ser utilizada nas instruções Transact-SQL de INSERT, UPDATE ou DELETE. Isso proporciona uma excelente flexibilidade, pois podemos utilizá-la apenas nas instruções que desejamos monitorar. Desta forma, qualquer processo que tenha permissão para modificar uma tabela pode usar a cláusula OUTPUT para popular a tabela de auditoria. Algumas boas práticas para a utilização da cláusula OUTPUT são as seguintes:
1) Remover qualquer permissão de acesso direto à tabela de auditada;
2) Escrever todas as operações de INSERT, UPDATE e DELETE dentro de stored procedures;
3) Atribuir as permissões apenas nas stored procedures;
Seguir estas boas práticas garantirá que o usuário só conseguirá executar as operações através das stored procedures e consequentemente não executará nenhuma operação sem passar pela auditoria. O ponto de atenção ao trabalhar com a cláusula OUTPUT está em ficar sempre atento para não esquecer e fazer as devidas modificações nas procedures quando houver mudanças na estrutura da tabela auditada ou até mesmo não esquecer da cláusula quando estiver escrevendo novas procedures.
Para evitar este problema, minha sugestão é que sejam criadas regras que definam os passos envolvidos para a modificação ou criação de operações que envolvam tabelas que precisem ser auditadas.
É verdade que ao invéz de trabalhar com a cláusula OUTPUT você também poderia executar esta auditoria usando triggers sobre a tabela a ser auditada, no entando, quando trabalhamos com triggers, além de termos os problemas de overhead sobre as tabelas afetas ainda temos que nos preocupar em lembrar das trigger sempre que modificamos as tabelas ou geramos scripts de criação das tabelas. Este problema não existe com a utilização da cláusula OUTPUT pois tudo está dentro das próprias procedures que executam as operações de INSERT, UPDATE e DELETE. Se você alterar a tabela auditada, certamente terá que alterar também as procedures que executam estas operações e neste momento verá a cláusula OUTPUT. Se você gerar script de criação das tabelas, certamente irá gerar também os scripts de criação das procedures e mais uma vez estará lá a cláusula OUTPUT.
É isso aí pessoal, abraço e até…
Nilton Pinheiro