BlackWaspTM

This web site uses cookies. By using the site you accept the cookie policy.This message is for compliance with the UK ICO law.

SQL Server
SQL 2005+

SQL Updates, Joins and Triggers

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.

Triggers

SQL Server triggers are useful when you wish to add scripts to a database that execute automatically when data is inserted, updated or deleted. For example, you may decide to audit all modifications to a table's data by creating triggers that copy the changed data to a separate table, along with the current date and time and the name of the user performing the action.

When data is modified, each trigger that reacts to the change is executed only once. If you change ten rows with a single UPDATE statement, a linked trigger with run once, not ten times. The full set of updated data will be present in the inserted and deleted temporary tables during the scope of the trigger. If you need to perform an update for each of the changed rows, you must deal with this in the code of the trigger.

A common mistake is to create triggers that are assumed to run once for each changed row. Another error is assuming that only one row will ever be changed in a single update. Both of these can introduce bugs into your software, which might not be noticed immediately.

In this article we'll create a simple database and add an update trigger to one of its tables. We'll create a faulty trigger that fails when used for multiple rows. We'll then fix the trigger and demonstrate its correct operation.

Creating the Database

To follow the examples, create a new test database and run the script shown below to create two tables. These represent parts of a stock control system. The database is much simpler than would be required in a real-world project. However, it is sufficient to demonstrate the trigger problem and its solution.

The StockLevels table is used to hold the current stock levels for a business's stocked items. The StockCode column holds a unique code for each item. The Physical column describes the number of items that are present in a warehouse. Allocated defaults to zero for new products. It specifies the number of items in the warehouse that have been allocated to orders and which, therefore, cannot be sold again. Finally, the Available computed column shows the number of items still available for purchase.

The Orders table holds orders for stock items. Each order is for a single item only, defined by the value in the StockCode column. The Quantity column holds the number of items ordered.

CREATE TABLE StockLevels
(
    StockCode CHAR(4),
    Physical INT NOT NULL,
    Allocated INT NOT NULL CONSTRAINT DF_Allocated DEFAULT 0,
    Available AS Physical - Allocated
)

CREATE TABLE Orders
(
    StockCode CHAR(4),
    Quantity INT
)

Let's add a few test rows to the StockLevels table.

INSERT INTO StockLevels (StockCode, Physical) VALUES('ABC1', 100)
INSERT INTO StockLevels (StockCode, Physical) VALUES('ZYX9', 200)
INSERT INTO StockLevels (StockCode, Physical) VALUES('BWSP', 150)

A Faulty Trigger

In our test database we want to make sure that each time an order is created the appropriate amount of stock is allocated. If too much stock were allocated we might refuse orders that we could fulfil. However, if too little stock were allocated we could take orders that we could not supply.

If we incorrectly assume that only one order will be created at a time, we might write the following trigger on the Orders table. Here we read the stock code and quantity from the inserted table and hold them in variables. This assignment uses values from the first row of the temporary table only.

NB: For a true system we would need similar triggers for updated and deleted orders.

CREATE TRIGGER AutoAllocateOrders
ON Orders
AFTER INSERT
AS

DECLARE @StockCode CHAR(4)
DECLARE @Quantity INT

SELECT @StockCode = StockCode, @Quantity = Quantity FROM inserted

UPDATE StockLevels
SET Allocated = Allocated + @Quantity
WHERE StockCode = @StockCode

The trigger works as desired if a single order is created. Try running the following script. This inserts a row and then queries the StockLevels table.

INSERT INTO Orders
    (StockCode, Quantity)
VALUES
    ('ABC1', 25)

SELECT * FROM StockLevels

The stock levels after ordering twenty-five units of the "ABC1" products show an appropriate allocation and reduced level of available stock.

StockCode Physical    Allocated   Available
--------- ----------- ----------- ---------
ABC1      100         25          75
ZYX9      200         0           200
BWSP      150         0           150

The problem with the trigger is apparent when we insert more than one row at a time. Try executing the following statement. This creates one order for each known stock code. Each of the three orders is for a single item.

INSERT INTO Orders (StockCode, Quantity)
SELECT StockCode, 1 FROM StockLevels

If you re-run the StockLevels query you can see that only one stock level is updated. In the results shown below, the "ZYX9" and "BWSP" stock levels are unchanged.

StockCode Physical    Allocated   Available
--------- ----------- ----------- ---------
ABC1      100         26          74
ZYX9      200         0           200
BWSP      150         0           150
10 March 2013