By: Sergey Gigoyan | Comments | Related: > Auditing and Compliance
Problem
Thanks to their behavior in transactions, table variables can be very useful when needed to do an audit of attempted changes even if these changes are rolled back. Like regular variables, table variables are not affected by a ROLLBACK command and keep changes even if a transaction is rolled back. Using this feature, we can flexibly use them in transactions to keep the data which will be needed even after a ROLLBACK. In this article we are not going to define table variables and explore their features and limitations as we have done it in the article Exploring SQL Server 2014 Table Variables. So, if you need to understand table variables, it is recommended to read that article beforehand. This article is aimed to illustrate the application of table variables in auditing.
Solution
Imagine an application where a lot of changes have been done in the SQL Server database and some changes do not meet the defined criteria or for some other reasons are rolling back. However, for some reasons, some types of these rolled back changes need to be recorded in a table or tables. In other words, sometimes it is necessary to log not only successful changes, but also the failed attempts of changes. According to a companies' policy having these types of recordings is mandatory, others can use this information for catching, preventing or finding change attempts to vulnerable or secure data. Other companies just only want to gather statistics or generate reports and so on.
In order not to make the article more complicated and make it easy to understand, we will discuss a very simple example using simplified tables and transactions. Suppose that we have a database of bank accounts and customers. We need to record any inflow or outflow of money on customers' accounts. Assuming that a customer cannot have a negative balance on an account, so the amount of money must always be 0 or greater. In addition, in case a customer tries to withdraw a greater amount of money that they have and are refused, we must have this fact also recorded.
The script below creates the sample environment for testing:
USE master GO CREATE DATABASE TestDB GO USE TestDB GO --Creating sample tables CREATE TABLE Customer ( CustomerID INT PRIMARY KEY, FirstName NVARCHAR(50), LastName NVARCHAR(50) ) GO CREATE TABLE Account ( AccountID INT PRIMARY KEY, AccountNumber CHAR(16), Amount MONEY, CustomerID INT ) GO ALTER TABLE Account WITH CHECK ADD CONSTRAINT FK_Account_Customer FOREIGN KEY(CustomerID) REFERENCES Customer(CustomerID) GO ALTER TABLE Account CHECK CONSTRAINT FK_Account_Customer GO ALTER TABLE Account WITH CHECK ADD CONSTRAINT CK_Amount CHECK (Amount > 0) GO ALTER TABLE Account CHECK CONSTRAINT CK_Amount GO CREATE UNIQUE INDEX UIX_CustomerID ON Account(CustomerID) GO --Inserting sample data INSERT INTO Customer(CustomerID, FirstName, LastName) VALUES(1, 'John', 'Jones') INSERT INTO Account(AccountID, AccountNumber, Amount, CustomerID) VALUES(1, '0000111122223333', 200, 1) GO
To simplify our example, it is assumed that each customer has only one account. After creating sample tables, we also need to have a table where our audit record will be stored. This table will store the information about customers' transactions - both failed and succeeded. It should contain CustomerID, action type, amount of money, transaction status ('Succeed' or 'Failed') and transaction date:
USE TestDB GO CREATE TABLE TransactionAudit ( TransactionAuditID INT IDENTITY(1,1) PRIMARY KEY, CustomerID INT NOT NULL, ActionType NVARCHAR(8), Amount MONEY, TransactionStatus NCHAR(7) NOT NULL, TransactionDate DATETIME NOT NULL ) GO ALTER TABLE TransactionAudit ADD CONSTRAINT DF_TransactionDate DEFAULT (GETDATE()) FOR TransactionDate GO ALTER TABLE TransactionAudit WITH CHECK ADD CONSTRAINT FK_TransactionAudit_Customer FOREIGN KEY(CustomerID) REFERENCES Customer (CustomerID) GO ALTER TABLE TransactionAudit CHECK CONSTRAINT FK_TransactionAudit_Customer GO ALTER TABLE TransactionAudit WITH CHECK ADD CONSTRAINT CK_TransactionAudit CHECK ((TransactionStatus='Success' OR TransactionStatus='Failed')) GO ALTER TABLE TransactionAudit CHECK CONSTRAINT CK_TransactionAudit GO ALTER TABLE TransactionAudit WITH CHECK ADD CONSTRAINT CK_ActionType CHECK ((ActionType='Withdraw' OR ActionType='AddMoney')) GO ALTER TABLE TransactionAudit CHECK CONSTRAINT CK_ActionType GO
The stored procedure below is used to withdraw money from the customer's account:
USE TestDB
GO
CREATE PROCEDURE uspWithdrawMoney
@CustomerID INT,
@withdrawAmount MONEY
AS
BEGIN
SET NOCOUNT ON
BEGIN TRY
BEGIN TRANSACTION
UPDATE Account
SET Amount=Amount-@withdrawAmount
WHERE CustomerID=@CustomerID
INSERT INTO TransactionAudit(CustomerID, ActionType, Amount, TransactionStatus)
VALUES(@CustomerID, 'Withdraw', @withdrawAmount, 'Success')
IF @@TRANCOUNT > 0
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
SELECT ERROR_MESSAGE()
END CATCH
END
GO
It is clear from the code that the log about a successful withdrawal is being written into the TransactionAudit table:
USE [TestDB]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[uspWithdrawMoney]
@CustomerID = 1,
@withdrawAmount = 50
SELECT 'Return Value' = @return_value
GO
SELECT * FROM TransactionAudit
Thus, now there is one record in TransactionAudit table, showing that a withdrawal has been completed successfully:
In the case of a rollback, however, we do not have a record about the failure in the audit table as the ROLLBACK statement reverts the INSERT statement into that table as well:
USE [TestDB]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[uspWithdrawMoney]
@CustomerID = 1,
@withdrawAmount = 500
SELECT 'Return Value' = @return_value
GO
SELECT * FROM TransactionAudit
GO
In contrast, if we use a table variable inside a transaction to store changes for auditing, we will not lose them even after a ROLLBACK.
Let's modify the procedure to use a table variable and keep the audit data even after a failure:
USE TestDB
GO
ALTER PROCEDURE uspWithdrawMoney
@CustomerID INT,
@withdrawAmount MONEY
AS
BEGIN
SET NOCOUNT ON
DECLARE @TransactionStatus NCHAR(7)
DECLARE @tbvTransactionAudit AS TABLE
(
CustomerID INT NOT NULL,
ActionType NVARCHAR(8),
Amount MONEY,
TransactionDate DATETIME NOT NULL DEFAULT GETDATE()
)
BEGIN TRY
BEGIN TRANSACTION
SET @TransactionStatus = 'Success'
INSERT INTO @tbvTransactionAudit(CustomerID, ActionType, Amount)
VALUES(@CustomerID, 'Withdraw', @withdrawAmount)
UPDATE Account
SET Amount=Amount-@withdrawAmount
WHERE CustomerID=@CustomerID
IF @@TRANCOUNT > 0
COMMIT
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK
SET @TransactionStatus='Failed'
SELECT ERROR_MESSAGE()
END CATCH
--Filling Audit data
INSERT INTO TransactionAudit(CustomerID, ActionType, Amount, TransactionDate, TransactionStatus)
SELECT CustomerID, ActionType, Amount, TransactionDate, @TransactionStatus
FROM @tbvTransactionAudit
END
GO
Now, it's time to illustrate:
USE [TestDB]
GO
DECLARE @return_value int
EXEC @return_value = [dbo].[uspWithdrawMoney]
@CustomerID = 1,
@withdrawAmount = 500
SELECT 'Return Value' = @return_value
GO
SELECT * FROM TransactionAudit
GO
The result shows that despite the fact that the transaction failed, the data inserted into @tbvTransactionAudit table within the transaction was kept. This data was used to fill the regular audit table after the transaction and have audit records of failed attempts:
This is a quite simple example just to show the application of table variables for auditing. In real-world examples, thousands or millions of rows can be modified in one transaction and some of these changes might be needed to be recorded for auditing even if the transaction is rolled back. In these situations, table variables can be very useful due to their ability to preserve changes after a ROLLBACK statement.
Conclusion
To sum up, as table variables remain unaffected after rolling back a transaction, they can be very useful in case of recording failed attempts for auditing. Unlike regular tables, filling data into table variables during a transaction we will not lose this data regardless of the transaction result and, therefore, this information can be used to insert audit records into regular permanent tables after the transaction completes.
Next Steps
For more information review the information below:
- Exploring SQL Server 2014 Table Variables
- https://docs.microsoft.com/en-us/sql/t-sql/data-types/table-transact-sql
- https://docs.microsoft.com/en-us/sql/t-sql/language-elements/declare-local-variable-transact-sql
- https://docs.microsoft.com/en-us/sql/relational-databases/tables/use-table-valued-parameters-database-engine
About the author
Sergey Gigoyan is a database professional with more than 10 years of experience, with a focus on database design, development, performance tuning, optimization, high availability, BI and DW design.This author pledges the content of this article is based on professional experience and not AI generated.
View all my tips
