THE RELATIONAL MODEL 3: ADVANCED TOPICS - Concepts of Database Management - page 144

AddOrderLine. The third line indicates that this trigger will be executed after a new order line is inserted.
The fourth line indicates that the SQL command is to occur for each row that is added. Like stored proce-
dures, the SQL command is enclosed between the words BEGIN and END. In this case, the SQL command is
an UPDATE command. The command uses the New qualifier. The New qualifier refers to the row that is
added to the OrderLine table. If an order line is added on which the part number is CD52 and the number
ordered is two, New.PartNum is CD52 and New.NumOrdered is 2.
The following UpdateOrderLine trigger is executed when a user attempts to update an order line. There
are two differences between the UpdateOrderLine trigger and the AddOrderLine trigger. First, the third line of
the UpdateOrderLine trigger indicates that this trigger is executed after an UPDATE of an order line, rather
than an INSERT. Second, the computation to update the OnOrder column includes both New.NumOrdered
and Old.NumOrdered. As with the AddOrderLine trigger, New.NumOrdered refers to the new value. In an
UPDATE command, however, there is also an old value, which is the value before the update takes place. If an
update changes the value for NumOrdered from 1 to 3, Old.NumOrdered is 1 and New.NumOrdered is 3.
Adding New.NumOrdered and subtracting Old.NumOrdered results in a net change of an increase of two. (The
net change could also be negative, in which case the OnOrder value decreases.)

144

DELIMITER $$
CREATE TRIGGER UpdateOrderLine
AFTER UPDATE ON OrderLine
FOR EACH ROW
BEGIN
UPDATE PART
SET OnOrder ¼ OnOrder þ New.NumOrdered Old.NumOrdered
WHERE Part.PartNum ¼ New.PartNum ;
END
$$
DELIMITER ;

The following DeleteOrderLine trigger performs a function similar to the other two triggers. When an
order line is deleted, the OnOrder value for the corresponding part is updated by subtracting
Old.NumOrdered from the current OnOrder value. (In a delete operation, there is no New.NumOrdered.)

DELIMITER $$
CREATE TRIGGER DeleteOrderLine
AFTER DELETE ON OrderLine
FOR EACH ROW
BEGIN
UPDATE PART
SET OnOrder ¼ OnOrder Old.NumOrdered
WHERE PartNum ¼ Old.PartNum ;
END
$$
DELIMITER ;

Using Data Macros in Access 2010
Access does not support triggers. In Access 2010, however, you can gain the same functionality as triggers by
creating a data macro. You can create a data macro using the options on the Table Tools Table tab, as shown
in Figure 4-28.