Auditing Failed SQL Server Transactions Using Table Variables

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:

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
UPDATE statement caused the withdrawal

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 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:

Related Posts

Leave a Reply

Your email address will not be published. Required fields are marked *