tailieunhanh - Hướng dẫn học Microsoft SQL Server 2008 part 70

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 WHERE Code 1001 SELECT @LocationID LocationlD FROM WHERE LocationCode CH INSERT ProductID InventoryCode LocationlD VALUES @ProdID A1 @LocationID SELECT FROM AS I INNER JOIN AS P ON 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 AFTER Insert AS UPDATE SET QuantityOnHand FROM AS Inv INNER JOIN Inserted AS i ON Return 652 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 WHERE InventoryCode A1 INSERT InventoryTransaction InventoryID Value SELECT InventoryID -3 FROM WHERE InventoryCode A1 INSERT InventoryTransaction InventoryID Value SELECT InventoryID 7 FROM WHERE InventoryCode A1 The following query views the data within the InventoryTransaction table SELECT FROM AS