With the basics covered, we will now look at each of the supported trigger types, and the differences between them.
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:
CREATE TRIGGER neworder AFTER INSERT ON orders FOR EACH ROW SELECT NEW.order_num;
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:
INSERT INTO orders(order_date, cust_id) VALUES(Now(), 10001);
+-----------+ | order_num | +-----------+ | 20010 | +-----------+
The following example demonstrates the use of OLD to save rows about to be deleted into an archive table:
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;
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).
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).
The following example ensures that state abbreviations are always in uppercase (regardless of how they were actually specified in the UPDATE statement):
CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);
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