SQL 2005+

SQL Updates, Joins and Triggers

by Richard Carr, published at http://www.blackwasp.co.uk/SQLUpdateJoinTrigger.aspx

A common error when writing SQL triggers is to forget that an operation that affects multiple rows will cause a trigger to execute only once. One example of the problem can be solved using UPDATE statements that include joins.

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