Databases Reference
In-Depth Information
then executes this command and creates the stored procedure. The final line changes the delimiter back to
the semicolon.
To use this stored procedure, a user enters the word
CALL
followed by the procedure name. After the pro-
cedure name, the user enters the values of the two arguments in parentheses. For example, changing the credit
limit of customer number 356 to $10,000, requires the following command:
141
CALL Change_Credit ('356', 10000);
Although Access does not support stored procedures, you can achieve some of the same convenience by
creating a parameter query that prompts the user for the arguments you would otherwise use in a stored
procedure.
A
trigger
is an action that occurs automatically in response to an associated database operation such as an
INSERT, UPDATE, or DELETE command. Like a stored procedure, a trigger is stored and compiled on the
server. Unlike a stored procedure, which is executed in response to a user request, a trigger is executed in
response to a command that causes the associated database operation to occur.
The examples in this section assume there is a new column named OnOrder in the Part table. This col-
umn represents the number of units of a part currently on order. For example, if there are two separate order
lines for a part and the number ordered on one order line is 3 and the number ordered on the other order line
is 2, the OnOrder value for that part will be 5. Adding, changing, or deleting order lines affects the value in
the OnOrder column for the part. To ensure that the value is updated appropriately, you can use a trigger.
When a user adds an order line, the following MySQL trigger, which is named AddOrderLine, is executed.
When a user adds an order line, the trigger must update the OnOrder value for the corresponding part to reflect
the order line. For example, if the value in the OnOrder column for part CD52 is 4 and the user adds an
order line on which the part number is CD52 and the number of units ordered is 2, 6 units of part CD52 will
be on order. When a record is added to the OrderLine table, the AddOrderLine trigger updates the Part table
by adding the number of units ordered on the order line to the previous value in the OnOrder column.
DELIMITER $$
CREATE TRIGGER AddOrderLine
AFTER INSERT ON OrderLine
FOR EACH ROW
BEGIN
UPDATE PART
SET OnOrder = OnOrder + New.NumOrdered
WHERE PartNum = New.PartNum ;
END
$$
DELIMITER ;
Because semicolons cause the same issues with triggers as with stored procedures, the command starts
by changing the delimiter to $$. The second line indicates that the command is creating a trigger named
AddOrderLine. The third line indicates that this trigger will be executed after an order line is inserted. The
fourth line indicates that the SQL command is to occur for each row that is added. Like stored procedures, 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 2,
New.PartNum will be CD52 and New.NumOrdered will be 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 2. (The net change
could also be negative, in which case the OnOrder value decreases.)
