French | Spanish | Portuguese | Italian | German | Japanese | Chinese | Korean | Russian | Arabic

You need to be especially aware that, in some database products, such as SQL Server 2000, a trigger fires once for each statement and not once for each row affected by a statement. We can’t tell you how many times we’ve seen developers make critical coding mistakes based on the very wrong assumption that their trigger code executes once for each row affected by the statement rather than just once for the entire statement. Even Oracle, which enables you to define triggers that fire once for each row affected by a statement, can still have problems modifying data correctly, as you see later. You have several ways around this problem in SQL Server 2000. The following parts take a look at these workarounds. If your application is designed such that you never need to insert more than a single row at a time into a table, you can simply place a restriction on that table to that effect by adding the code to its insert trigger.

IF (@@ROWCOUNT != 1)
   BEGIN
   RAISERROR 50010 ‘You cannot enter more than one row at a time.’
   ROLLBACK TRANSACTION
   RETURN
 END

Of course, you can also place such restrictions on UPDATE and DELETE operations, but always ask yourself twice whether you can really enforce such a restriction and still keep your application operating smoothly. The code, for example, is really designed to fire once for each row affected by a statement, but if you put a single-row restriction in that trigger, it prevents the code from executing across multiple rows. Leaving the single-row restriction off the code would cause it to assign all support calls affected by the triggering statement to the one tech-support person who has the least calls assigned to him at the time that the statement executes. That is acceptable because the system would never have such a function to insert multiple support calls in a single statement because support calls occur one at a time rather than in batches—and even if it did, assigning support calls in such a way would still sufficiently serve your business rules. Single-row restrictions are the easiest but least useful method of ensuring that statementlevel trigger code executes once for every row (in this case, the only row) that’s affected by the triggering SQL statement. In many cases, updating multiple rows by joining them with a pseudotable is better than using a single-row restriction, as you see in the following part. Whenever you insert one or more rows into a table, SQL Server creates a pseudotable in memory that it names Inserted. This pseudotable has the identical structure of the actual table into which the data was inserted, and it contains the exact contents of what you inserted into that table. Similarly, whenever you delete one or more rows from a table, SQL Server creates a pseudotable in memory that it names Deleted. This pseudotable has the identical structure of the actual table from which the data was deleted, and it contains the exact contents of the deleted rows. If you update one or more rows in a table, SQL Server creates both the Inserted and Deleted pseudotables. The Deleted pseudotable contains the rows affected by the UPDATE statement, and these rows reflect the data values that existed before the update occurred. The Inserted pseudotable contains these same rows but with the data values that existed after the update occurred. Technically, both the Inserted and Deleted pseudotables are always created, regardless of which table event occurs. If the event is an INSERT, the Deleted pseudotable contains zero rows; similarly, if the event is a DELETE, the Inserted pseudotable also contains zero rows. These facts are often used to advantage in triggers that execute for a combination of table events. A trigger that executes for the INSERT, UPDATE, and DELETE table events, for example, can always execute two blocks of code—one that affects data joined to the Inserted pseudotable and another block that affects data joined to the Deleted pseudotables—without worry that the wrong data is affected, because any data set joined to an empty set produces another empty set. In other words, on a INSERT, any code that’s designed to affect data joined to the Deleted pseudotable does not affect any data, because zero rows are in the Deleted pseudotable. A pseudotable lives only as long as its underlying trigger executes; then it is automatically destroyed and purged from memory. While it lives, however, a pseudotable can be used by the trigger to do just about anything that a regular table can do. You can even join pseudotables with real tables. This capability comes in handy if you want to apply the trigger code to all rows affected by an update. Take, for example, this code.

CREATE TRIGGER tU_OrderItem
   ON OrderItem
   AFTER Update
   AS
   BEGIN
   /* Declare variables for use by the trigger */
   DECLARE
   @iOldQuantity INTEGER, @iNewQuantity INTEGER,
 @sOldItemNumber VARCHAR(15),

@sNewItemNumber VARCHAR(15)
   /* Get the old ordered quantity and item number */
   SELECT
   @iOldQuantity = Quantity,
   @sOldItemNumber = ItemNumber
   FROM
   Deleted
   /* Get the new ordered quantity and item number */
   SELECT
   @iNewQuantity = Quantity,
   @sNewItemNumber = ItemNumber
   FROM
   Inserted
   /*
   Update the old item number to put the former
   ordered quantity back into inventory
   */
   UPDATE
   InventoryItem
   SET
   AvailableToSell = AvailableToSell + @iOldQuantity
   WHERE
   ItemNumber = @sOldItemNumber
   /*
   Update the new item number (it might have changed)
   to remove the new ordered quantity from inventory
   */
   UPDATE
   InventoryItem
   SET
   AvailableToSell = AvailableToSell - @iOldQuantity
   WHERE
   ItemNumber = @sNewItemNumber
 END

This trigger code executes only once and uses only the values from the first row in the Deleted pseudotable, in turn causing only one row in the InventoryItem table to be updated. Consider the nest code.

CREATE TRIGGER tU_OrderItem
 ON OrderItem

AFTER Update
   AS
   BEGIN
   /* Put the old ordered quantity back into inventory */
   UPDATE
   InventoryItem
   SET
   AvailableToSell = AvailableToSell + d.Quantity
   FROM
   Deleted d INNER JOIN InventoryItem ii
   ON d.ItemNumber = ii.ItemNumber
   /* Remove the new ordered quantity from inventory
   Remember: the item number may have changed! */
   UPDATE
   InventoryItem
   SET
   AvailableToSell = AvailableToSell - i.Quantity
   FROM
   Inserted i INNER JOIN InventoryItem ii
   ON i.ItemNumber = ii.ItemNumber
 END

This trigger code updates all InventoryItem rows that correspond to the OrderItem rows being updated—even if the ItemNumber key changes value. By joining the Inserted and Deleted pseudotables with the InventoryItem table, you can perform the necessary calculations to increase and decrease each inventory item by the old and new quantities ordered, respectively. The code is also much less cumbersome if written correctly. In actual practice, you would probably do three things differently. First, you would have three separate triggers to accomplish this task—one each for the INSERT, UPDATE, and DELETE events—and each would contain the specific logic for adjusting the inventory level according to the database event that occurred. Second, you would place logic in your UPDATE trigger to prevent changing the ItemNumber from its original value. Third, you would consolidate the two UPDATE queries in the trigger into one query joining the InventoryItem table and both pseudotables, as in the code.

CREATE TRIGGER tU_OrderItem
   ON OrderItem
   AFTER Update
   AS
   BEGIN
   IF (UPDATE(ItemNumber))
   BEGIN
   RAISERROR 50010 ‘You cannot change the Item Number.’
 ROLLBACK TRANSACTION

RETURN
   END
   UPDATE
   InventoryItem
   SET
   AvailableToSell = ii.AvailableToSell + d.Quantity - i.Quantity
   FROM
   Inserted i INNER JOIN InventoryItem ii
   ON i.ItemNumber = ii.ItemNumber
   INNER JOIN Deleted d
   ON d.ItemNumber = ii.ItemNumber
 END

Imagine retrieving a bunch of rows into a separate place in memory and then fetching those rows one at a time, looking at the column values in each row and possibly performing some operation based on the values in each row. This is the life of a cursor. Cursors are a last resort because they are cumbersome, slow, and resource intensive, and they destroy the scalability of your database if used on high-transaction throughput areas of your database. Cursors are often used where they shouldn’t be, either as substitutes for good SQL code or to work around poorly designed databases. At times, however, a cursor is the only practical solution to your problem. Say, for example, that you have a stored procedure named sp_ArchiveCall that is used in many places throughout your database, including the Insert trigger shown in the code.

CREATE PROCEDURE sp_ArchiveCall (
   @SupportCallID Integer
   )
   AS
   BEGIN
   UPDATE
   SupportCall
   SET
   Status = 60
   WHERE
   SupportCallID = @SupportCallID
   END
   go
   CREATE TRIGGER tI_SupportCallArchive
   ON SupportCallArchive
 AFTER INSERT

AS
   BEGIN
   DECLARE
   @SupportCallID Integer
   DECLARE curClosedCalls CURSOR FOR
   SELECT
   SupportCallID
   FROM
   Inserted
   OPEN curClosedCalls
   FETCH NEXT FROM curClosedCalls INTO @SupportCallID
   WHILE @@FETCH_STATUS = 0
   BEGIN
   EXECUTE sp_ArchiveCall @SupportCallID
   FETCH NEXT FROM curClosedCalls INTO @SupportCallID
   END
   CLOSE curClosedCalls
   DEALLOCATE curClosedCalls
   END
 go

You use a cursor here because you must call the stored procedure once for every row affected by the statement, and the only way to do so is to place the stored procedure call within each retrieval or fetch of a cursor row. Please treat all cursors with fear and loathing. They are bad, and you’d better have a defensible excuse for using them—or your source-code peer review is likely to embarrass you terribly.