sqlserver建立觸發器，為SQL Server創建基于“智能”觸發器的審核跟蹤

介紹 (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.

在我們的示例中，審核表將具有與審核表列相同的所有列，以及進行更改的用戶（用戶），進行更改的時間（事務插入/更新和刪除的日期），更改的數據以及之前的內容。查看更改后的數據。

sqlserver建立觸發器。 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.

sql2008創建觸發器， 步驟1創建庫存數據庫 ：首先，我們將為庫存表，審核表以及用于插入/更新和刪除的審核觸發器創建庫存數據庫。

?--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
GOUSE InventoryDB
GO

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

步驟2創建庫存表 ：現在，我們創建庫存表

?USE InventoryDB
GO
IF EXISTS ( SELECT [name] FROM sys.tables WHERE [name] = 'InventoryMasters' )
DROP TABLE InventoryMasters
GOCREATE 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

在這里我們可以看到用于庫存表的字段

SQL語句創建觸發器、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]

步驟3創建審核表 ：我們需要創建一個審核表來存儲我們的審核信息。 在這里，我們創建一個審計表名稱，名稱為[Audit_InventoryMasters]

?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.

在這里，您可以看到[Audit_InventoryMasters]表中的每一列以及這些列的詳細信息。

創建觸發器 (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.

sqlserver觸發器， 現在，我們已經創建了用于庫存管理和審計的表。 現在，我們將看到如何在庫存表上創建插入/更新/刪除觸發器以審計交易。

Step 4 Creating Insert Trigger:

步驟4創建插入觸發器：

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:

我們創建了一個名為trInventoryInsert的插入觸發器。 創建此插入觸發器的目的是將每筆交易的進/出數量插入到審計表中。 在此觸發器中，我們具有三個條件：

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.
   如果該物料可用，但不適用于所選日期，則插入一個新記錄，輸入進，出數量并平衡結果數量。

?USE InventoryDB
GO--Script to create DB, table, and sample INSERT datacreate 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=@InventoryDateIF(@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_InventoryMastersSET 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;ENDELSEBEGINSELECT top 1 @Unit_Qty=ResultQty FROM Audit_InventoryMasters WHERE ItemNO=@ItemNO AND AuditDate < @InventoryDate??--AND InventorySeq=@InventorySeq??order by AuditDate desc ENDIF(@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_InventoryMastersSET????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_InventoryMastersSET??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.

運行以下插入腳本以測試我們的插入查詢是否適用于審計表插入。

?
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.

怎么創建觸發器、 當我們查看庫存和審核表的結果時，可以看到2條記錄將被插入到庫存表中，數量為100和40。但是對于審核表，我們只能看到一條記錄，其結果為數量為140，這是兩個交易的數量的總和。

?
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.

sql觸發器編寫， 運行下面的插入查詢以測試我們的插入腳本是否適用于缺貨審核表。

?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.

當我們查看庫存和審計表的結果時，我們可以看到將有4條記錄插入到庫存表中，其中“存貨數量”為100和40，“缺貨數量”為20,10，狀態為I，O。但是對于審計表，我們可以僅看到一條插入的記錄，結果的數量為110，這是所有In事務的數量之和減去所有Out的總和。

?
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.

在這里，我們對同一商品在相同的不同日期進行了2次插入庫存，對于相同產品和序列號又進行了一次庫存插入。

?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.

當我們同時查看庫存表和審計表的結果時，我們會看到4條記錄將被插入到庫存表中，其中物料“ I001”的庫存數量和缺貨數量，庫存日期為“ 20161205、20161206、20161207”，狀態為I， O.但是對于審計表，我們只能看到在日期'20161205，20161206，20161207'中插入的三個記錄，其中每天都有剩余數量，最初的剩余數量為0，而每天的剩余數量是當天結果數量之前。 在得出的數量中，我們得到庫存進出后的實際數量。

?
select * from InventoryMasters
select * from Audit_InventoryMasters

sql建立一個簡單的觸發器，Step 5 Creating Update Trigger:

步驟5創建更新觸發器：

Next, we create our Audit Update Trigger:

接下來，我們創建審計更新觸發器：

We have created an Update trigger named trInventoryUpdate.

創建一個insert觸發器、 我們創建了一個名為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:

詳細的更新觸發器查詢如下：

?
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=@InventoryDateIF(@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=@InventoryDateand InventoryType='I'SELECT @result_OUT_Qty=sum(Qty) FROM InventoryMasters???? WHERE???? ItemNO=@ItemNO and???? InventoryDate=@InventoryDateand InventoryType='O'?? IF(@InventoryType='I')????BEGIN????SELECT @result_IN_Qty_CHK=sum(InQty)FROM Audit_InventoryMastersWHEREItemNO=@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=@InventoryDateand InventoryType='I'IF(@ROWINCOUNT>1)BEGINIF(@qty=0)BEGINUPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty-(@result_IN_Qty_CHK-@result_IN_Qty)??WHERE???? ItemNO=@ItemNO???? and???? AuditDate>@InventoryDate ENDELSEBEGIN????????IF(@result_IN_Qty_CHK>=@result_IN_Qty)BEGINSET @result_IN_Qty_CHK_1=@result_IN_Qty_CHK-@result_IN_Qty;UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty-@result_IN_Qty_CHK_1WHERE???? ItemNO=@ItemNO???? and???? AuditDate>@InventoryDate ENDELSEBEGINSET @result_IN_Qty_CHK_1=@result_IN_Qty-@result_IN_Qty_CHK;UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty+@result_IN_Qty_CHK_1WHERE???? ItemNO=@ItemNO???? and???? AuditDate>@InventoryDate ENDEND??????ENDELSEBEGIN IF(@Qty>=@New_QtyCheck)????BEGINUPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty+(@Qty-@New_QtyCheck)??WHERE???? ItemNO=@ItemNO???? and???? AuditDate>@InventoryDate?? ENDELSEBEGINUPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty-(@New_QtyCheck-@Qty)??WHERE???? ItemNO=@ItemNO???? and???? AuditDate>@InventoryDate?? ENDENDUPDATE 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_InventoryMastersWHEREItemNO=@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=@InventoryDateand InventoryType='O'IF(@ROWINCOUNT>1)BEGINIF(@qty=0)BEGINUPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty+(@result_OUT_Qty_CHK-@result_OUT_Qty)??WHERE???? ItemNO=@ItemNO???? and???? AuditDate>@InventoryDate ENDElseBEGIN????????IF(@result_OUT_Qty_CHK>=@result_OUT_Qty)BEGINSET @result_OUT_Qty_CHK_1=@result_OUT_Qty_CHK-@result_OUT_Qty;UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty+@result_OUT_Qty_CHK_1WHERE???? ItemNO=@ItemNO???? and???? AuditDate>@InventoryDate ENDElseBEGINSET @result_OUT_Qty_CHK_1=@result_OUT_Qty-@result_OUT_Qty_CHK;UPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty+@result_OUT_Qty_CHK_1WHERE???? ItemNO=@ItemNO???? and???? AuditDate>@InventoryDate ENDEND??????ENDELSEBEGIN IF(@Qty>=@New_OUT_QtyCheck)????BEGINUPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty-(@Qty-@New_OUT_QtyCheck)??WHERE???? ItemNO=@ItemNO???? and???? AuditDate>@InventoryDate?? ENDELSEBEGINUPDATE Audit_InventoryMasters SET RemainingQty=RemainingQty+(@New_OUT_QtyCheck-@Qty)??WHERE???? ItemNO=@ItemNO???? and???? AuditDate>@InventoryDate?? ENDENDUPDATE 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.

現在我們將更新日期為'20161205'的項目編號'I001'，對于InventorySeq no = 3，數量為120，現在我們可以從上述結果中看到插入的原始數量為40。

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

運行以下更新查詢以將數量從40更新到120

?update InventoryMasters set Qty=120
whereInventoryDate='20161205'andItemNO='I001'andInventoryType='I'andInventorySeq=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.

我們可以看到庫存和審核表數量信息已更新。 在審核表中，我們可以看到所有剩余數量和結果數量已經更新，具體取決于新的庫存輸入數量更新。

?
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.

在上一個圖像中，我們可以看到InventoryID 3和4，數量分別為20和10，作為缺貨。 現在，我們將此缺貨數量更新為0。

?update InventoryMasters set Qty=0
whereInventoryDate='20161205'andItemNO='I001'andInventoryType='O'andInventorySeq=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.

我們可以看到庫存和審核表數量信息已更新。 在審核表中，我們可以看到所有剩余數量和結果數量已更新，具體取決于新的庫存輸出數量更新。

?
select * from InventoryMasters
select * from Audit_InventoryMasters

Step 5 Creating Delete Trigger:

步驟5創建刪除觸發器：

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

接下來，我們創建名為trInventoryDe??lete的審計更新觸發器。

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.
   首先，我們檢查審核表中已經存在的項目。 如果存在，則將輸入和輸出數量更新為0和結果數量，并更新審核表中的所有剩余數量和結果數量。
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.
   在某些情況下，具有相同日期的同一產品將有多于一行的不同序列號。 在這種情況下，我們需要減去帶有已刪除Seq的相同產品的庫存/庫存數量。 產品數量 我們已在Delete觸發器中應用了這兩種邏輯。

The detailed Delete Trigger query is as below:

詳細的刪除觸發器查詢如下：

?USE InventoryDB
GOcreate 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=@InventoryDateif(@DateCount>0)???? BEGINif(@InventoryType='I')????Begin?? select @ROWINCOUNT=count(*) from InventoryMasters???? where???? ItemNO=@ItemNO and???? InventoryDate=@InventoryDateand InventoryType='I'and InventorySeq=@InventorySeqif(@ROWINCOUNT<=0)BEGINselect @Qty=InQty-(select isnull(sum(Qty),0) from InventoryMasters???? where???? ItemNO=@ItemNO and???? InventoryDate=@InventoryDateand InventoryType='I')from Audit_InventoryMasters???? where???? ItemNO=@ItemNO and???? AuditDate=@InventoryDateENDupdate Audit_InventoryMasters set InQty=InQty-@Qtywhere???? ItemNO=@ItemNO???? and???? AuditDate=@InventoryDate??????update Audit_InventoryMasters set RemainingQty=RemainingQty-@Qtywhere???? ItemNO=@ItemNO???? and???? AuditDate>@InventoryDate update Audit_InventoryMasters set???????????? ResultQty=RemainingQty+InQty-OutQty????where???? ItemNO=@ItemNO???? and???? AuditDate>=@InventoryDate??ENDElseBEGINselect @ROWINCOUNT=count(*) from InventoryMasters???? where???? ItemNO=@ItemNO and???? InventoryDate=@InventoryDateand InventoryType='O'and InventorySeq=@InventorySeqif(@ROWINCOUNT<=0)BEGINselect @Qty=OutQty-(select isnull(sum(Qty),0) from InventoryMasters???? where???? ItemNO=@ItemNO and???? InventoryDate=@InventoryDateand InventoryType='O')from Audit_InventoryMasters???? where???? ItemNO=@ItemNO and???? AuditDate=@InventoryDateENDupdate Audit_InventoryMasters set OutQty=OutQty-@Qty?????????????? where???? ItemNO=@ItemNO???? and???? AuditDate=@InventoryDate??????update Audit_InventoryMasters set RemainingQty=RemainingQty+@Qtywhere???? ItemNO=@ItemNO???? and???? AuditDate>@InventoryDate update Audit_InventoryMasters set???????????? ResultQty=RemainingQty+InQty-OutQty????where???? ItemNO=@ItemNO???? and???? AuditDate>=@InventoryDate??ENDEND?? 
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’

現在，我們要刪除日期“ 20161206”和InventorySeq no = 3的項目編號“ I001”。 運行以下更新查詢，以刪除商品編號為“ I001”的“ 20161206上的庫存70”

?DELETE FROM InventoryMasters WHEREInventoryDate='20161206'andItemNO='I001'andInventoryType='I'andInventorySeq=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.

我們可以看到“ 20161206”日期的項目編號“ I001”和編號為3的日期已從庫存表中刪除，并且在審計表中，我們可以看到庫存數量已更新為0，并且所有剩余數量和結果數量已更新，具體取決于新的庫存輸入數量刪除。

?
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

有關自動創建更多基于通用觸發器的審計跟蹤的工具，請參閱ApexSQL觸發器

有用的鏈接 (Useful links)

• SQL Server Audit (Database Engine) SQL Server審核（數據庫引擎）
• Create Audit Table and Insert\Update\Delete Triggers for a given table 創建審計表并為給定表插入\更新\刪除觸發器
• Track Data Changes (SQL Server) 跟蹤數據更改（SQL Server）

翻譯自: https://www.sqlshack.com/creating-smart-trigger-based-audit-trail-sql-server/