Microsoft® SQL Server® 2012 Bible

897

Chapter 36: Creating Triggers

36

The AdventureWorks2012 database includes a simplifi ed inventory system. To demonstrate

transaction-aggregation handling, the following triggers implement the required rules. The

fi rst script creates a sample valid inventory item for test purposes:

USE AdventureWorks2012;

DECLARE

@ProdID INT,

@LocationID INT;

SELECT @ProdID = ProductID

FROM Production.Product

WHERE ProductID = 998;

SELECT @LocationID = LocationID

FROM Production.Location

WHERE LocationID = 50;

INSERT Production.ProductInventory (ProductID, Shelf, Bin, LocationID)

VALUES (@ProdID, 'A', 0, @LocationID);

SELECT P.ProductID, I.Shelf, I.Quantity, I.LocationID

FROM Production.ProductInventory AS I

INNER JOIN Production.Product AS P

ON I.ProductID = P.ProductID

WHERE P.ProductID = 998;

Result:

ProductID Shelf Quantity LocationID

998 N/A 99 7

998 A 0 50

998 N/A 56 60

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 TABLE Production.InventoryTransaction

(InventoryID INT,

VALUE INT);

GO

CREATE TRIGGER InvTrans_Aggregate

ON Production.InventoryTransaction

AFTER Insert

c36.indd 897c36.indd 897 7/31/2012 10:03:09 AM7/31/2012 10:03:09 AM

http://www.it-ebooks.info