Auditing Failed SQL Server Transactions Using Table Variables

By:   |   Updated: 2019-06-14   |   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:

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:



sql server categories

sql server webinars

subscribe to mssqltips

sql server tutorials

sql server white papers

next tip



About the author
MSSQLTips author Sergey Gigoyan 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


Article Last Updated: 2019-06-14

Comments For This Article

















get free sql tips
agree to terms