Database Reference
In-Depth Information
The CREATE PROCEDURE line in the stored procedure causes MySQL to create a procedure named
Change_Credit. The second line indicates that there are two arguments, CNum and CLimit, with the appro-
priate data types. These values are used in the SQL statement that will be stored. (Although it is not
required, creating aliases for the field names simplifies the command.) When users run this stored procedure,
they furnish values for CNum and CLimit. The stored procedure then updates the customer whose number is
stored in CNum by changing the customer
s credit limit to the value stored in CLimit.
The next line contains the word BEGIN to mark the beginning of the SQL command to be stored. The
next three lines contain the command, including the semicolon. The line that contains the word END marks
the end of the command. The $$ delimiter indicates that the CREATE PROCEDURE command is complete.
MySQL 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
procedure name, the user enters the values of the two arguments in parentheses. For example, the following
command changes the credit limit of customer number 356 to $10,000:
'
143
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.
TRIGGERS
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 three and the number ordered on the other
order line is two, the OnOrder value for that part is five. 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 in MySQL or a data macro in Access 2010. These features are explained in the following sections.
Using Triggers in MySQL
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 new order line. For example, if the value in the OnOrder column for part CD52 is four and the
user adds an order line on which the part number is CD52 and the number of units ordered is two, six units
of part CD52 are 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 new 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
