Using JOINs in UPDATE Statements
To fix the trigger problem we need to be able to update one stock level row for each newly created order. We could create a cursor over the inserted table and loop through each row. Each iteration would update the stock level that corresponds to the cursor's current inserted Order. This would potentially be inefficient.
A better solution is to use an UPDATE statement that includes a JOIN. Such a statement allows you to update the rows in only one of the tables. However, the data used to control the updates can come from any of the joined tables.
The syntax for such an UPDATE statement is shown below.
UPDATE
table-to-update-alias
SET
table-to-update-alias.column-1 = source-table-alias.column-1,
table-to-update-alias.column-2 = source-table-alias.column-2,
.
.
.
table-to-update-alias.column-X = source-table-alias.column-X
FROM
table-to-update table-to-update-alias
INNER JOIN
source-table source-table-alias
ON
table-to-update-alias.join-column source-table.join-column
Correcting the Trigger
Using the above style of UPDATE, we can fix the faulty trigger. All that we need to do is join the StockLevels table to the inserted temporary table so that we can adjust all of the affected stock levels at once. The updated trigger is shown below.
You can see that we are updating the data in the StockLevels table, accessed via an alias of "S". This is joined to the inserted table, with the "I" alias, where the stock codes match. The new Allocated value is calculated by summing the current value and the quantity from each matching order.
ALTER TRIGGER AutoAllocateOrders
ON Orders
AFTER INSERT
AS
UPDATE
S
SET
S.Allocated = S.Allocated + I.Quantity
FROM
StockLevels S
INNER JOIN
inserted I
ON
S.StockCode = I.StockCode
Before we demonstrate the new trigger, let's delete all of the orders and reset the allocations:
DELETE Orders
UPDATE StockLevels SET Allocated = 0
Now re-run the INSERT statement to create three new orders.
INSERT INTO Orders (StockCode, Quantity)
SELECT StockCode, 1 FROM StockLevels
After the insert, the data in the StockLevels table is updated correctly and matches the new orders.
StockCode Physical Allocated Available
--------- ----------- ----------- ---------
ABC1 100 1 99
ZYX9 200 1 199
BWSP 150 1 149
10 March 2013