Đang chuẩn bị nút TẢI XUỐNG, xin hãy chờ
Tải xuống
Việc kích hoạt giao dịch hàng tồn kho Việc kích hoạt kiểm kê giao dịch thực hiện chức năng tổng hợp của việc duy trì giá trị quantityon tay hiện trong bảng tồn kho. | Part IV Developing with SQL Server The OBXKites database includes a simplified inventory system. To demonstrate transaction-aggregation handling the following triggers implement the required rules. The first script creates a sample valid inventory item for test purposes USE OBXKites DECLARE @ProdID UniqueIdentifier @LocationID Uniqueldentifier SELECT @ProdID ProductID FROM dbo.Product WHERE Code 1001 SELECT @LocationID LocationlD FROM dbo.Location WHERE LocationCode CH INSERT dbo.Inventory ProductID InventoryCode LocationlD VALUES @ProdID A1 @LocationID SELECT P.Code I.InventoryCode I.QuantityOnHand FROM dbo.Inventory AS I INNER JOIN dbo.Product AS P ON I.ProductID P.ProductID Result Code InventoryCode QuantityOnHand 1001 A1 0 The inventory-transaction trigger The inventory-transaction trigger performs the aggregate function of maintaining the current quantity-on-hand value in the Inventory table. With each row inserted into the InventoryTransaction table the trigger updates the Inventory table. The JOIN between the Inserted image table and the Inventory table enables the trigger to handle multiple-row inserts CREATE TRIGGER InvTrans_Aggregate ON dbo.InventoryTransaction AFTER Insert AS UPDATE dbo.Inventory SET QuantityOnHand i.Value FROM dbo.Inventory AS Inv INNER JOIN Inserted AS i ON Inv.InventoryID i.InventoryID Return 652 www.getcoolebook.com Creating DML Triggers 26 The next batch tests the InvTrans_Aggregate trigger by inserting a transaction and observing the InventoryTransaction and Inventory tables INSERT InventoryTransaction InventorylD Value SELECT InventoryID 5 FROM dbo.Inventory WHERE InventoryCode A1 INSERT InventoryTransaction InventoryID Value SELECT InventoryID -3 FROM dbo.Inventory WHERE InventoryCode A1 INSERT InventoryTransaction InventoryID Value SELECT InventoryID 7 FROM dbo.Inventory WHERE InventoryCode A1 The following query views the data within the InventoryTransaction table SELECT i.InventoryCode it.Value FROM dbo.InventoryTransaction AS