Syed Shanu

Creating a “smart” trigger based audit trail for SQL Server

December 12, 2016 by

Introduction

Audit tables are used to track transactions for a particular table or tables. For every important transaction table, it’s important to create an audit table. Auditing can be helpful if you need to track who is Inserting/Updating and Deleting data on a sensitive table and/or view before and after data change information.

Audit tables will have all the same columns of the audited table columns, in our example, along with who made the changes (user), when the change was made (date of transaction insert/update and delete), what data changed and the before and after views of changed data.

This article explains, step-by-step, how to set up an audit on an example Inventory table. We will create an inventory table, an audit table and triggers for inserting, updating, and deleting of inventory data. In our trigger, we will get all the Inventory transaction and store all the history information in an audit table.

This is a logic intensive “smart” audit that is going to shape our audit data and values, based on the transactions run against the inventory table and logic contained in the triggers. Therefore, there won’t be a one to one relationship between the number of transactions audited and records written to the audit log. The example illustrates the kind of logical processing that can be created within the auditing layer itself

Step 1 Create Inventory Database: First, we will create an inventory database for our Inventory table, audit table and auditing Trigger for Insert/Update and delete.

Step 2 Create Inventory Table: Now we create our Inventory table

Here we can see Field used for Inventory Table

Inventory Table Filed Name Filed Details
InventoryID Identity Field
ItemNO Item No (This is for which item the inventory details are stored)
InventoryDate Inventory stock in/Out Date
InventorySeq Sequence Number for Inventory
InventoryType Inventory Type I/O (Inventory Item Quantity In/Inventory Item Quantity out
Qty In/Out Quantity

Step 3 Create audit table: We need to create an audit table for storing our audit information. Here we create an audit table name with the name [Audit_InventoryMasters]

Here you can see each column on the [Audit_InventoryMasters] table and the purpose of the columns in detail.

Inventory Audit Filed Name Filed Details
Audit_InventoryID Identity Filed
InventoryID Inventory table Id
ItemNO Item No (This is for which item the inventory details are stored)
RemainingQty This is to store the previous date remaining Qty
InQty To store Inventory in Qty
OutQty To store Inventory out Qty
ResultQty This is Transaction result of every record – we will be using this formula to calculate our result quantity (RemainingQty+ InQty – OutQty)
AuditDate Inventory Type I/O (Inventory Item Quantity In/Inventory Item Quantity out
AuditbyUser In/Out Quantity
AuditType This is status character used to store what type of audit information is inserted like Insert/Update or Delete by (I/U or D)

Creating Triggers

We have now created our tables for Inventory Management and auditing. Now we will see how to create Insert/Update/Delete Triggers for auditing transactions on our inventory table.

Step 4 Creating Insert Trigger:

First, we will start with the Insert Trigger:

We have created an Insert Trigger named trInventoryInsert. This Insert Trigger is created to insert the IN/Out quantity of every transaction to the audit table. In this trigger, we have three conditions:

  1. First, we check for the item already existing in the audit table. If not, then insert the In or Out Qty to the audit table.
  2. If data exists, check for the Item available on the same date, then update the input and output Quantity and balance the resulting quantity with the logic implemented in the Insert Trigger.
  3. If the item is available but not for the selected date then insert a new record with In, out quantity and balance the result qty.

Insert Test for Inventory Stock in Quantity

Run the below insert script to test to see if our insert query is working for audit table insert.

When we view result for both Inventory and audit table we can see 2 records will be inserted into the Inventory table with Quantity as 100 and 40. But for the audit table, we can see only one record inserted with the result as quantity as 140, which is an aggregate of the quantity for both transactions.

Insert Test for Inventory Stock out Quantity

Run the below insert query to test our insert script is working for audit table for Out Quantity.

When we view result for both Inventory and audit table we can see 4 records will be inserted into Inventory table with Stock in Quantity as 100 and 40 and Stock Out quantity as 20,10 with status as I, O. But for audit table we can see only one records inserted with result as quantity as 110 which is the sum of the quantity of all In transactions less the sum of all Out.

Inventory Stock in and Stock out with different date:

Now we can do few more insert tests with insert stock in and stock out quantity with the different date for the same item.

Here we do 2 insert for stock In for same different date for the same item and one more insert for Stock Out for same product and sequence no.

When we view result for both Inventory and audit table we can see 4 records will be inserted into Inventory table with Stock in Quantity and Stock Out quantity for Item ‘I001’ and Inventory Date as ‘20161205, 20161206, 20161207’ with status as I, O. But for audit table we can see only three records inserted for dates ‘20161205, 20161206, 20161207’ with each day Remaining quantity, initially the remaining quantity is 0 and each day the remaining quantity was before day result quantity. In the resulting quantity, we have an actual result of stock quantity after Stock in and Stock out.

Step 5 Creating Update Trigger:

Next, we create our Audit Update Trigger:

We have created an Update trigger named trInventoryUpdate.

Using this Update trigger each update on the Inventory Master table result will be updated to the audit table as a result.

  1. First, we check for the item already existing in the audit table. If exists update the input and output Quantity and update all Remaining and Result in the audit table.
  2. In some cases, the same product name with same date and different Seq. number will be updated. We have divided the Stock In Quantity update as based on Seq. number and based on product and date.

The detailed Update trigger query is as follows:

Update Test for Inventory Stock in Quantity

Before the update, we can see the actual data of both Inventory and audit tables.

Now we are going to update Item No ‘I001’ for date ‘20161205’ and for InventorySeq no=3 with quantity as 120 now we can see the inserted original quantity is 40 from above result.

Run the below update query to update quantity from 40 to 120

We can see both Inventory and audit table quantity information has been updated. In the audit table, we can see all Remaining Quantity and Result Quantity has been updated depend on new Stock Input Quantity update.

Update Test for Inventory Stock Out Quantity

In the previous image, we can see InventoryID 3 and 4 the quantity has 20 and 10 as Stock Out. Now let’s update both this Stock Out Quantity as 0.

We can see both Inventory and audit table quantity information has been updated. In the audit table, we can see all Remaining Quantity and Result Quantity has been updated depending on the new Stock output Quantity update.

Step 5 Creating Delete Trigger:

Next, we create our Audit Update Trigger named as trInventoryDelete.

  1. First, we check for the items already existing in the audit table. If exists, update the Input and Output quantity to 0 and result quantity, and update all remaining and Result quantity in the audit table.
  2. In some cases, the same product with the same date will have more than one row with different seq no. In that case, we need to minus the Stock In/Stock Out quantity with the same product with the deleted Seq. product qty. we have applied both the logic in the Delete trigger.

The detailed Delete Trigger query is as below:

Delete Test for Inventory Stock in Quantity

Before the Delete, we can see the actual output of both Inventory and audit table.

Now we are going to Delete Item No ‘I001’ for date ‘20161206’ and for InventorySeq no=3. Run the below update query to delete Stock In Quantity of 70 on 20161206’ for Item No ‘I001’

We can see the Item No’I001’ and Seq no of 3 for ‘20161206’ date Stock has been deleted from Inventory table and in the audit table, we can see the Stock In quantity has been updated to 0 and all Remaining Quantity and Result Quantity has been updated depend on new Stock Input Quantity delete.

I hope you have enjoyed this article demonstrating a “smart” trigger based auditing layer and how this logic can be applied in a real world scenario

For a tool to create more generic trigger based audit trails automatically, see ApexSQL Trigger

Useful links


Syed Shanu
Auditing

About Syed Shanu

Syed Shanu is a Microsoft MVP, two-time CsharpCorner MVP and two-time Code project MVP, Author, Blogger, Speaker and always happy to share what he knows to others. He’s basically from Madurai, Tamil Nadu, India. He’s working as Technical Lead in South Korea with more than 10 years of experience on Microsoft technologies. Shanu is active in the community and always happy to share topics related to ASP.NET, MVC, ASP.NET Core, Web API, SQL Server, UWP, Azure, C#, AngularJs, Angular2. He has written more than 70 articles with various technology’s on ASP.NET, SQL Server, C#, AngularJs, Angular2, ASP.NET Core, Unity 3D, Windows Universal App and Samsung Gear App development. He’s several times TechNet Guru Gold Winner and you can found all his contribution in MSDN and also in TechNet Wiki https://social.technet.microsoft.com/profile/syedshanu/ He is also an Author of the eBook "SQL Queries For Beginners" you can download the eBook from here: http://www.c-sharpcorner.com/ebooks/sql-queries-for-beginners. View all posts by Syed Shanu

168 Views