Previous Page
Next Page

Using Triggers

With the basics covered, we will now look at each of the supported trigger types, and the differences between them.

INSERT TRiggers

INSERT triggers are executed before or after an INSERT statement is executed. Be aware of the following:

  • Within INSERT TRigger code, you can refer to a virtual table named NEW to access the rows being inserted.

  • In a BEFORE INSERT trigger, the values in NEW may also be updated (allowing you to change values about to be inserted).

  • For AUTO_INCREMENT columns, NEW will contain 0 before and the new automatically generated value after.

Here's an example (a really useful one, actually). AUTO_INCREMENT columns have values that are automatically assigned by MySQL. Chapter 21, "Creating and Manipulating Tables," suggested several ways to determine the newly generated value, but here is an even better solution:

• Input

CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

• Analysis

The code creates a trigger named neworder that is executed by AFTER INSERT ON orders. When a new order is saved in orders, MySQL generates a new order number and saves it in order_num.

This trigger simply obtains this value from NEW.order_num and returns it. This trigger must be executed by AFTER INSERT because before the BEFORE INSERT statement is executed, the new order_num has not been generated yet. Using this trigger for every insertion into orders will always return the new order number.

To test this trigger, try inserting a new order, like this:

• Input

INSERT INTO orders(order_date, cust_id)
VALUES(Now(), 10001);

• Output

+-----------+
| order_num |
+-----------+
|     20010 |
+-----------+

• Analysis

orders contains three columns. order_date and cust_id must be specified, order_num is automatically generated by MySQL, and order_num is also now returned automatically.

Tip

BEFORE or AFTER? As a rule, use BEFORE for any data validation and cleanup (you'd want to make sure that the data inserted into the table was exactly as needed). This applies to UPDATE triggers, too.


DELETE triggers

DELETE triggers are executed before or after a DELETE statement is executed. Be aware of the following:

  • Within DELETE trigger code, you can refer to a virtual table named OLD to access the rows being deleted.

  • The values in OLD are all read-only and cannot be updated.

The following example demonstrates the use of OLD to save rows about to be deleted into an archive table:

• Input

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
   INSERT INTO archive_orders(order_num, order_date, cust_id)
   VALUES(OLD.order_num, OLD.order_date, OLD.cust_id);
END;

• Analysis

Before any order is deleted this trigger will be executed. It used an INSERT statement to save the values in OLD (the order about to be deleted) into an archive table named archive_orders. (To actually use this example you'll need to create a table named archive_orders with the same columns as orders).

The advantage of using a BEFORE DELETE TRigger (as opposed to an AFTER DELETE TRigger) is that if, for some reason, the order could not be archived, the DELETE itself will be aborted.

Note

Multi-Statement Triggers You'll notice that trigger deleteorder uses BEGIN and END statements to mark the trigger body. This is actually not necessary in this example, although it does no harm being there. The advantage of using a BEGIN END block is that the trigger would then be able to accommodate multiple SQL statements (one after the other within the BEGIN END block).


UPDATE triggers

UPDATE triggers are executed before or after an UPDATE statement is executed. Be aware of the following:

  • Within UPDATE trigger code, you can refer to a virtual table named OLD to access the previous (pre-UPDATE statement) values and NEW to access the new updated values.

  • In a BEFORE UPDATE trigger, the values in NEW may also be updated (allowing you to change values about to be used in the UPDATE statement).

  • The values in OLD are all read-only and cannot be updated.

The following example ensures that state abbreviations are always in uppercase (regardless of how they were actually specified in the UPDATE statement):

• Input

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

• Analysis

Obviously, any data cleanup needs to occur in the BEFORE UPDATE statement as it does in this example. Each time a row is updated, the value in NEW.vend_state (the value that will be used to update table rows) is replaced with Upper(NEW.vend_state).

More on Triggers

Before wrapping this chapter, here are some important points to keep in mind when using triggers:

  • Trigger support in MySQL 5 is rather rudimentary at best when compared to other DBMSs. There are plans to improve and enhance trigger support in future versions of MySQL.

  • Creating triggers might require special security access. However, trigger execution is automatic. If an INSERT, UPDATE, or DELETE statement may be executed, any associated triggers will be executed, too.

  • Triggers should be used to ensure data consistency (case, formatting, and so on). The advantage of performing this type of processing in a trigger is that it always happens, and happens transparently, regardless of client application.

  • One very interesting use for triggers is in creating an audit trail. Using triggers it would be very easy to log changes (even before and after states if needed) to another table.

  • Unfortunately the CALL statement is not supported in MySQL triggers. This means that stored procedures cannot be invoked from within triggers. Any needed stored procedure code would need to be replicated within the trigger itself.


Previous Page
Next Page