Planned Improvements in MySQL 5.1 - Trigger Syntax
(Page 2 of 4 )
Triggers are created using the CREATE TRIGGER command, which in standard SQL has the following syntax:
CREATE TRIGGER trigge r
{BEFORE | AFTER} {[DELETE] | [INSERT] | [UPDATE] [OF columns]} ON table [REFERENCING { OLD [ROW] [AS] oldname} | NEW [ROW] [AS] newname }]
FOR EACH {ROW | STATEMENT}
[WHEN conditions]
statement-block
Here, trigger is the name of the trigger, and we assume that you’ll be able to use any valid MySQL identifier, just as you can now for tables, stored procedures, and so on. The BEFORE or AFTER keyword tells MySQL whether the trigger will fire before or after the operation that modifies data has taken place, and the next clause determines what sort of operation will fire the trigger. Note that it is possible to define multiple operations for a single trigger. For instance, a trigger could be set to fire whenever a query either updates current records or inserts data into a given table, but ignore operations that delete records from that table. It may be possible in MySQL to define triggers on individual table columns as well, but it’s not certain at this time whether or not this will be supported, and if so, when.
The FOR EACH clause determines whether the trigger will act at the statement level or the row level, as discussed previously. The WHEN clause may be used to test one or more conditions as part of determining whether or not the trigger should fire. Using the REFERENCING clause, you can assign aliases to the “before” and “after” versions of the table (and columns) with respect to any changes wrought by the data modification statement to which the trigger is bound.
Finally, at the end of this definition comes a block of stored procedure code that is to be executed in the event that the trigger is fired. Here’s a brief example of what you might expect to see in the way of a trigger when triggers become available in MySQL. Bear in mind that this must be regarded for now as pseudocode and that the actual triggers implementation may require something slightly different than what you see here:
CREATE TRIGGER price_increase
AFTER UPDATE ON products
REFERENCING OLD TABLE AS oldproduct NEW TABLE AS newproduct
WHEN newproduct.price > oldproduct.price BEGIN
INSERT INTO price_increases (id, change_date, prodid, oldprice, newprice)
VALUES ('', NOW(), oldproduct.id, oldproduct.price, newproduct.price);
END
This trigger (named price_increase) is fired whenever the price of an existing product is increased. When this happens, it inserts the date or date/time when the increase took place, the former price, and the new, higher price into a table named price_increases.
The stored procedure code in a trigger should work just like that found in any other stored procedure. We expect that you’ll be able to use branching, loops, data definition statements (such as CREATE TABLE and ALTER TABLE ), reference other stored procedures and stored functions, and so on.
NOTE PostgreSQL doesn’t actually support procedural code within a trigger; instead, you must execute a stored procedure that’s defined elsewhere. It’s conceivable that MySQL could go this route as well, although what information we have available suggests that you’ll be able to write a procedure as part of a MySQL trigger when triggers become available, since this capability is part of the SQL standard.
To delete a trigger once it’s no longer needed, the SQL standard provides for a DROP TRIGGER command, whose syntax is simply
DROP TRIGGER trigger;
where trigger is simply the name of the trigger that you wish to drop.
NOTE Oracle and Microsoft’s SQL Server both support an ALTER TRIGGER command that can be used for changing existing triggers. This command is not currently part of the SQL standard, and PostgreSQL supports triggers quite happily without it. It may not be implemented in MySQL, at least initially, although if there’s enough demand for it, it could conceivably be added for compatibility reasons, just as MySQL 5.0.1 supports ALTER VIEW .
Benefits
Triggers can be used for number of purposes, all of which contribute at least indirectly to making database operations more efficient. These include
- Auditing and logging: As shown in the price_increase example, we can use a trigger to track certain types of changes in a database by copying or moving data from the table upon which the trigger was set to other tables. It’s also conceivable that you could use SELECT INTO OUTFILE or SELECT INTO DUMPFILE in order to create files in delimited formats that are compatible with other applications.
- Preserving data integrity: Although it’s preferable to use datatypes and foreign key constraints to keep users and applications from inserting invalid data or updating it to “bad” values, there are times when this isn’t easy to accomplish. For example, suppose you don’t want any products to be listed in the products table if their prices exceed one hundred dollars. You might use a trigger to “catch” these before they’re inserted and cause them to be inserted into an expensive_products table instead.
- Addressing security concerns: Triggers can be used to facilitate the segregation of sensitive data into tables or databases for which access can be restricted. Recalling one of the example scenarios we outlined in the “Taking Derived Tables to the Next Level—Views” section earlier in the chapter: Suppose you have an application that obtains membership data for a corporate organization, and you’d like to make some data such as name and e-mail address available to developers working on a public “address book” interface where employees can look up contact information for co-workers. However, you don’t want the address book to display private data such as Social Security or taxpayer numbers. To keep this from happening, even by accident, you can use a trigger for INSERT and UPDATE operations on a restricted master employees table that copies “public” data such as name, e-mail address, and company telephone extension into an employees_public table, and allow the address book developers access to this table only. While we can accomplish the same task using a view, it’s good to have an additional choice in implementation, especially in a highly complex system such as the personnel database for a large corporation.
Next: Other Expected Improvements >>
More MySQL Articles
More By Apress Publishing
|
 This article is excerpted from chapter eight of Beginning MySQL Database Design and Optimization: From Novice to Professional, written by Jon Stephens and Chad Russell (Apress, ISBN: 1590593324). Check it out today at your favorite bookstore. Buy this book now.
|
|
