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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 |
--Script to create DB, table, and sample Insert data USE MASTER GO -- 1) Check for the Database Exists .If the database is exist then drop and create new DB IF not EXISTS (SELECT [name] FROM sys.databases WHERE [name] = 'InventoryDB' ) CREATE DATABASE InventoryDB GO USE InventoryDB GO |
Step 2 Create Inventory Table: Now we create our Inventory table
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
USE InventoryDB GO IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'InventoryMasters' ) DROP TABLE InventoryMasters GO CREATE TABLE [dbo].[InventoryMasters]( [InventoryID] [int] IDENTITY(1,1) NOT NULL, ItemNO varchar(10), InventoryDate Datetime null, InventorySeq int, InventoryType char(1), Qty int ) |
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]
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
CREATE TABLE [dbo].[Audit_InventoryMasters]( [Audit_InventoryID] [int] IDENTITY(1,1) NOT NULL, [InventoryID] [int] , ItemNO varchar(10), RemainingQty int, InQty int, OutQty int, ResultQty int, AuditDate Datetime null, AuditbyUser int, AuditType char(1) CONSTRAINT [PK_Audit_InventoryMasters] PRIMARY KEY CLUSTERED ( [Audit_InventoryID] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] |
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:
- 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.
- 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.
- 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.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 |
USE InventoryDB GO --Script to create DB, table, and sample INSERT data create TRIGGER trInventoryInsert ON [dbo].[InventoryMasters] FOR INSERT AS BEGIN SET NOCOUNT ON; declare @Count int=0; declare @DateCount int=0; declare @InventoryID int=0; declare @InventoryDate datetime; declare @InventorySeq int; declare @InventoryType char(1); declare @ItemNO varchar(10); declare @Qty int; declare @Result_Qty int=0; declare @FinalInventoryType char(1); declare @Unit_Qty int=0; declare @New_QtyCheck int=0; declare @New_OUT_QtyCheck int=0; SELECT @InventoryID=i.InventoryID FROM inserted i; SELECT @InventoryDate=i.InventoryDate FROM inserted i; SELECT @InventorySeq=i.InventorySeq FROM inserted i; SELECT @InventoryType=i.InventoryType FROM inserted i; SELECT @ItemNO=i.ItemNO FROM inserted i; SELECT @Qty=i.Qty FROM inserted i; SELECT @Count=COUNT(*) FROM Audit_InventoryMasters WHERE ItemNO=@ItemNO; IF(@Count>0) BEGIN SELECT @DateCount=COUNT(*) FROM Audit_InventoryMasters WHERE ItemNO=@ItemNO AND AuditDate=@InventoryDate IF(@DateCount>0) BEGIN -- UPDATE AND loop for upadte all qty SELECT top 1 @New_QtyCheck=InQty FROM Audit_InventoryMasters WHERE ItemNO=@ItemNO AND AuditDate=@InventoryDate SELECT top 1 @New_OUT_QtyCheck=OutQty FROM Audit_InventoryMasters WHERE ItemNO=@ItemNO AND AuditDate=@InventoryDate IF(@InventoryType='I') BEGIN UPDATE Audit_InventoryMasters SET InQty=@Qty+@New_QtyCheck WHERE ItemNO=@ItemNO AND AuditDate=@InventoryDate UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty+@Qty WHERE ItemNO=@ItemNO AND AuditDate>@InventoryDate UPDATE Audit_InventoryMasters SET ResultQty=RemainingQty+InQty-OutQty WHERE ItemNO=@ItemNO AND AuditDate>=@InventoryDate END ELSE BEGIN UPDATE Audit_InventoryMasters SET OutQty=@Qty+@New_OUT_QtyCheck WHERE ItemNO=@ItemNO AND AuditDate=@InventoryDate UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty-@Qty WHERE ItemNO=@ItemNO AND AuditDate>@InventoryDate UPDATE Audit_InventoryMasters SET ResultQty=RemainingQty+InQty-OutQty WHERE ItemNO=@ItemNO AND AuditDate>=@InventoryDate END END ELSE BEGIN IF not exists (SELECT * FROM Audit_InventoryMasters WHERE AuditDate<@InventoryDate) BEGIN SET @Unit_Qty=0; -- SET @Result_Qty=0; END ELSE BEGIN SELECT top 1 @Unit_Qty=ResultQty FROM Audit_InventoryMasters WHERE ItemNO=@ItemNO AND AuditDate < @InventoryDate --AND InventorySeq=@InventorySeq order by AuditDate desc END IF(@InventoryType='I') BEGIN SET @Result_Qty=@Unit_Qty+@Qty INSERT INTO Audit_InventoryMasters (InventoryID,ItemNO,RemainingQty,InQty,OutQty,ResultQty,AuditDate,AuditbyUser,AuditType) VALUES(@InventoryID,@ItemNO,@Unit_Qty,@Qty,0,@Result_Qty,@InventoryDate,101,'I'); UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty+@Qty WHERE ItemNO=@ItemNO AND AuditDate>@InventoryDate UPDATE Audit_InventoryMasters SET ResultQty=RemainingQty+InQty-OutQty WHERE ItemNO=@ItemNO AND AuditDate>=@InventoryDate END ELSE BEGIN SET @Result_Qty=@Unit_Qty-@Qty INSERT INTO Audit_InventoryMasters (InventoryID,ItemNO,RemainingQty,InQty,OutQty,ResultQty,AuditDate,AuditbyUser,AuditType) VALUES(@InventoryID,@ItemNO,@Unit_Qty,0,@Qty,@Result_Qty,@InventoryDate,101,'I'); UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty-@Qty WHERE ItemNO=@ItemNO AND AuditDate>@InventoryDate UPDATE Audit_InventoryMasters SET ResultQty=RemainingQty+InQty-OutQty WHERE ItemNO=@ItemNO AND AuditDate>=@InventoryDate END END END ELSE BEGIN IF(@InventoryType='I') BEGIN INSERT INTO Audit_InventoryMasters (InventoryID,ItemNO,RemainingQty,InQty,OutQty,ResultQty,AuditDate,AuditbyUser,AuditType) VALUES(@InventoryID,@ItemNO,0,@Qty,0,@Qty,@InventoryDate,101,'I'); END ELSE BEGIN INSERT INTO Audit_InventoryMasters (InventoryID,ItemNO,RemainingQty,InQty,OutQty,ResultQty,AuditDate,AuditbyUser,AuditType) VALUES(@InventoryID,@ItemNO,0,0,@Qty,-@Qty,@InventoryDate,101,'I'); END END END |
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.
|
1 2 3 4 5 6 7 8 9 |
insert into InventoryMasters (ItemNO,InventoryDate,InventorySeq,InventoryType,Qty) values('I001','20161205',1,'I',100); insert into InventoryMasters (ItemNO,InventoryDate,InventorySeq,InventoryType,Qty) values('I001','20161205',2,'I',40); |
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.
|
1 2 3 4 |
select * from InventoryMasters select * from Audit_InventoryMasters |
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.
|
1 2 3 4 5 6 7 8 9 |
insert into InventoryMasters (ItemNO,InventoryDate,InventorySeq,InventoryType,Qty) values('I001','20161205',2,'O',20); insert into InventoryMasters (ItemNO,InventoryDate,InventorySeq,InventoryType,Qty) values('I001','20161205',2,'O',10); |
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.
|
1 2 3 4 |
select * from InventoryMasters select * from Audit_InventoryMasters |
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.
|
1 2 3 4 5 6 7 8 9 10 11 12 |
insert into InventoryMasters (ItemNO,InventoryDate,InventorySeq,InventoryType,Qty) values('I001','20161206',3,'I',70); insert into InventoryMasters (ItemNO,InventoryDate,InventorySeq,InventoryType,Qty) values('I001','20161207',4,'I',30); insert into InventoryMasters (ItemNO,InventoryDate,InventorySeq,InventoryType,Qty) values('I001','20161207',4,'O',10); |
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.
|
1 2 3 4 |
select * from InventoryMasters select * from Audit_InventoryMasters |
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.
- 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.
- 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139 140 141 142 143 144 145 146 147 148 149 150 151 152 153 154 155 156 157 158 159 160 161 162 163 164 165 166 167 168 169 170 171 172 173 174 175 176 177 178 179 180 181 182 183 184 185 186 187 188 189 190 191 192 193 194 195 196 197 198 199 200 201 202 203 204 205 206 207 208 209 210 211 212 213 214 215 216 217 218 219 220 221 222 223 224 225 226 227 228 229 230 231 232 233 234 235 236 237 238 239 240 241 242 243 244 245 246 247 248 249 250 251 252 253 254 255 256 257 258 259 260 261 262 263 264 265 |
USE inventoryDB GO --Script to create DB,Table AND sample INSERT data create TRIGGER trInventoryUpdate ON [dbo].[InventoryMasters] FOR UPDATE AS BEGIN SET NOCOUNT ON; Declare @Count int=0; Declare @DateCount int=0; Declare @InventoryID int=0; Declare @ROWINCOUNT int=0; Declare @InventoryDate datetime; Declare @InventorySeq int; Declare @InventoryType char(1); Declare @ItemNO varchar(10); Declare @Qty int; Declare @Result_Qty int=0; Declare @FinalInventoryType char(1); Declare @Unit_Qty int=0; Declare @New_QtyCheck int=0; Declare @New_OUT_QtyCheck int=0; Declare @result_IN_Qty int=0; Declare @result_OUT_Qty int=0; Declare @result_IN_Qty_CHK int=0; Declare @result_IN_Qty_CHK_1 int=0; Declare @result_OUT_Qty_CHK int=0; Declare @result_OUT_Qty_CHK_1 int=0; SELECT @InventoryID=i.InventoryID FROM inserted i; SELECT @InventoryDate=i.InventoryDate FROM inserted i; SELECT @InventorySeq=i.InventorySeq FROM inserted i; SELECT @InventoryType=i.InventoryType FROM inserted i; SELECT @ItemNO=i.ItemNO FROM inserted i; SELECT @Qty=i.Qty FROM inserted i; SELECT @DateCount=count(*) FROM Audit_InventoryMasters WHERE ItemNO=@ItemNO and AuditDate=@InventoryDate IF(@DateCount>0) BEGIN -- UPDATE and loop for upadte all qty SELECT top 1 @New_QtyCheck=InQty FROM Audit_InventoryMasters WHERE ItemNO=@ItemNO and AuditDate=@InventoryDate SELECT top 1 @New_OUT_QtyCheck=OutQty FROM Audit_InventoryMasters WHERE ItemNO=@ItemNO and AuditDate=@InventoryDate SELECT @result_IN_Qty=sum(Qty) FROM InventoryMasters WHERE ItemNO=@ItemNO and InventoryDate=@InventoryDate and InventoryType='I' SELECT @result_OUT_Qty=sum(Qty) FROM InventoryMasters WHERE ItemNO=@ItemNO and InventoryDate=@InventoryDate and InventoryType='O' IF(@InventoryType='I') BEGIN SELECT @result_IN_Qty_CHK=sum(InQty) FROM Audit_InventoryMasters WHERE ItemNO=@ItemNO and AuditDate=@InventoryDate UPDATE Audit_InventoryMasters SET InQty=@result_IN_Qty WHERE ItemNO=@ItemNO and AuditDate=@InventoryDate SELECT @ROWINCOUNT=count(*) FROM InventoryMasters WHERE ItemNO=@ItemNO and InventoryDate=@InventoryDate and InventoryType='I' IF(@ROWINCOUNT>1) BEGIN IF(@qty=0) BEGIN UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty-(@result_IN_Qty_CHK-@result_IN_Qty) WHERE ItemNO=@ItemNO and AuditDate>@InventoryDate END ELSE BEGIN IF(@result_IN_Qty_CHK>=@result_IN_Qty) BEGIN SET @result_IN_Qty_CHK_1=@result_IN_Qty_CHK-@result_IN_Qty; UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty-@result_IN_Qty_CHK_1 WHERE ItemNO=@ItemNO and AuditDate>@InventoryDate END ELSE BEGIN SET @result_IN_Qty_CHK_1=@result_IN_Qty-@result_IN_Qty_CHK; UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty+@result_IN_Qty_CHK_1 WHERE ItemNO=@ItemNO and AuditDate>@InventoryDate END END END ELSE BEGIN IF(@Qty>=@New_QtyCheck) BEGIN UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty+(@Qty-@New_QtyCheck) WHERE ItemNO=@ItemNO and AuditDate>@InventoryDate END ELSE BEGIN UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty-(@New_QtyCheck-@Qty) WHERE ItemNO=@ItemNO and AuditDate>@InventoryDate END END UPDATE Audit_InventoryMasters SET ResultQty=RemainingQty+InQty-OutQty WHERE ItemNO=@ItemNO and AuditDate>=@InventoryDate End ELSE BEGIN SELECT @result_OUT_Qty_CHK=sum(OutQty) FROM Audit_InventoryMasters WHERE ItemNO=@ItemNO and AuditDate=@InventoryDate UPDATE Audit_InventoryMasters SET OutQty=@result_OUT_Qty WHERE ItemNO=@ItemNO and AuditDate=@InventoryDate SELECT @ROWINCOUNT=count(*) FROM InventoryMasters WHERE ItemNO=@ItemNO and InventoryDate=@InventoryDate and InventoryType='O' IF(@ROWINCOUNT>1) BEGIN IF(@qty=0) BEGIN UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty+(@result_OUT_Qty_CHK-@result_OUT_Qty) WHERE ItemNO=@ItemNO and AuditDate>@InventoryDate END Else BEGIN IF(@result_OUT_Qty_CHK>=@result_OUT_Qty) BEGIN SET @result_OUT_Qty_CHK_1=@result_OUT_Qty_CHK-@result_OUT_Qty; UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty+@result_OUT_Qty_CHK_1 WHERE ItemNO=@ItemNO and AuditDate>@InventoryDate END Else BEGIN SET @result_OUT_Qty_CHK_1=@result_OUT_Qty-@result_OUT_Qty_CHK; UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty+@result_OUT_Qty_CHK_1 WHERE ItemNO=@ItemNO and AuditDate>@InventoryDate END END END ELSE BEGIN IF(@Qty>=@New_OUT_QtyCheck) BEGIN UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty-(@Qty-@New_OUT_QtyCheck) WHERE ItemNO=@ItemNO and AuditDate>@InventoryDate END ELSE BEGIN UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty+(@New_OUT_QtyCheck-@Qty) WHERE ItemNO=@ItemNO and AuditDate>@InventoryDate END END UPDATE Audit_InventoryMasters SET ResultQty=RemainingQty+InQty-OutQty WHERE ItemNO=@ItemNO and AuditDate>=@InventoryDate End END END |
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
|
1 2 3 4 5 6 7 8 9 10 11 |
update InventoryMasters set Qty=120 where InventoryDate='20161205' and ItemNO='I001' and InventoryType='I' and InventorySeq=2 |
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.
|
1 2 3 4 |
select * from InventoryMasters select * from Audit_InventoryMasters |
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.
|
1 2 3 4 5 6 7 8 9 10 11 |
update InventoryMasters set Qty=0 where InventoryDate='20161205' and ItemNO='I001' and InventoryType='O' and InventorySeq=2 |
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.
|
1 2 3 4 |
select * from InventoryMasters select * from Audit_InventoryMasters |
Step 5 Creating Delete Trigger:
Next, we create our Audit Update Trigger named as trInventoryDelete.
- 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.
- 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:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 |
USE InventoryDB GO create TRIGGER trInventoryDelete ON [dbo].[InventoryMasters] FOR Delete AS BEGIN SET NOCOUNT ON; declare @Count int=0; declare @DateCount int=0; Declare @InventoryID int=0; declare @ROWINCOUNT int=0; Declare @InventoryDate datetime; Declare @InventorySeq int; Declare @InventoryType char(1); Declare @ItemNO varchar(10); declare @Qty int; declare @Result_Qty int=0; Declare @FinalInventoryType char(1); declare @Unit_Qty int=0; declare @New_QtyCheck int=0; declare @New_OUT_QtyCheck int=0; declare @result_IN_Qty int=0; declare @result_OUT_Qty int=0; declare @result_IN_Qty_CHK int=0; declare @result_IN_Qty_CHK_1 int=0; declare @result_OUT_Qty_CHK int=0; declare @result_OUT_Qty_CHK_1 int=0; SELECT @InventoryID=d.InventoryID FROM deleted d; SELECT @InventoryDate=d.InventoryDate FROM deleted d; SELECT @InventorySeq=d.InventorySeq FROM deleted d; SELECT @InventoryType=d.InventoryType FROM deleted d; SELECT @ItemNO=d.ItemNO FROM deleted d; SELECT @Qty=d.Qty FROM deleted d; select @DateCount=count(*) from Audit_InventoryMasters where ItemNO=@ItemNO and AuditDate=@InventoryDate if(@DateCount>0) BEGIN if(@InventoryType='I') Begin select @ROWINCOUNT=count(*) from InventoryMasters where ItemNO=@ItemNO and InventoryDate=@InventoryDate and InventoryType='I' and InventorySeq=@InventorySeq if(@ROWINCOUNT<=0) BEGIN select @Qty=InQty-(select isnull(sum(Qty),0) from InventoryMasters where ItemNO=@ItemNO and InventoryDate=@InventoryDate and InventoryType='I')from Audit_InventoryMasters where ItemNO=@ItemNO and AuditDate=@InventoryDate END update Audit_InventoryMasters set InQty=InQty-@Qty where ItemNO=@ItemNO and AuditDate=@InventoryDate update Audit_InventoryMasters set RemainingQty=RemainingQty-@Qty where ItemNO=@ItemNO and AuditDate>@InventoryDate update Audit_InventoryMasters set ResultQty=RemainingQty+InQty-OutQty where ItemNO=@ItemNO and AuditDate>=@InventoryDate END Else BEGIN select @ROWINCOUNT=count(*) from InventoryMasters where ItemNO=@ItemNO and InventoryDate=@InventoryDate and InventoryType='O' and InventorySeq=@InventorySeq if(@ROWINCOUNT<=0) BEGIN select @Qty=OutQty-(select isnull(sum(Qty),0) from InventoryMasters where ItemNO=@ItemNO and InventoryDate=@InventoryDate and InventoryType='O')from Audit_InventoryMasters where ItemNO=@ItemNO and AuditDate=@InventoryDate END update Audit_InventoryMasters set OutQty=OutQty-@Qty where ItemNO=@ItemNO and AuditDate=@InventoryDate update Audit_InventoryMasters set RemainingQty=RemainingQty+@Qty where ItemNO=@ItemNO and AuditDate>@InventoryDate update Audit_InventoryMasters set ResultQty=RemainingQty+InQty-OutQty where ItemNO=@ItemNO and AuditDate>=@InventoryDate END END END |
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’
|
1 2 3 4 5 6 7 8 9 10 11 |
DELETE FROM InventoryMasters WHERE InventoryDate='20161206' and ItemNO='I001' and InventoryType='I' and InventorySeq=3 |
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.
|
1 2 3 4 |
select * from InventoryMasters select * from Audit_InventoryMasters |
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
- SQL Server Audit (Database Engine)
- Create Audit Table and Insert\Update\Delete Triggers for a given table
- Track Data Changes (SQL Server)
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
- SQL Server Common Table Expressions (CTE) - February 23, 2017
- Creating a “smart” trigger based audit trail for SQL Server - December 12, 2016