| New as of MySQL 5.0.2 is the trigger. A trigger is an action that automatically takes place when a specific something happens to a specific table. As triggers are associated with a table, they are therefore particular to that table and database. Furthermore, since they are automatically enacted, there is no way to invoke a trigger (other than taking whatever action invokes it). The general syntax for making a trigger is CREATE TRIGGER trigger_name time event ON tablename FOR EACH ROW statement
The trigger_name value abides by pretty much the same rules as anything you name in MySQLdatabases, tables, column, indexes: use alphanumeric characters, plus the underscore, avoid spaces and existing keywords, and so on. For the time value, this is either BEFORE or AFTER, indicating whether the trigger should run before the event takes place or afterward. For example, say that a trigger updates Table B every time a new record is added to Table A. Should that update happen before the insertion or after? The event value corresponds to a type of action happening to the table. The possible events are listed in Table 11.1. Table 11.1. These three events are used in triggers. Notice that they don't apply to only INSERT, UPDATE, and DELETE queries, but rather when rows are inserted, updated, or deleted by any applicable query.Trigger Events |
|---|
Event | Applies When Rows Are... |
|---|
INSERT | Added to the table, including through LOAD DATA | UPDATE | Updated | DELETE | Deleted |
Finally, the statement part of the trigger is where the real magic happens. The code here will be much like that in a stored routine, using the same control structures, BEGIN...END blocks, and types of variables. Rather than give an example here, I'll show it to you in the following steps. But first, one last concept.... A complication can arise when working with triggers, as you often end up referring to columns whose values change. To remedy this, MySQL added the OLD and NEW keywords (case-insensitive) as a way to distinguish them. Both are used in the form of OLD.columname and NEW.columname. You'll want to use OLD anytime you are referring to an existing column changed by an UPDATE or DELETE. Note that you can use OLD either BEFORE or AFTER the UPDATE or DELETE. You'll want to use NEW to refer to the content used during an INSERT or UPDATE. It's often used to change data that's being entered into the table. For my trigger examples, I'm going to create some new tables in a new database. The premise will be an e-commerce application that sells several types of doodads. Triggers will be used to manage the inventory as doodads are ordered or as orders get canceled. To create a trigger: 1. | Log in to the mysql client as a user that can create new databases.
| 2. | Create a new database called ecommerce (Figure 11.21).
CREATE DATABASE ecommerce; USE ecommerce;
Figure 11.21. A new database will be created and used for the trigger examples. 
| | | 3. | Create the doodads table (Figure 11.22).
CREATE TABLE doodads ( doodad_id INT UNSIGNED NOT NULL AUTO_INCREMENT, doodad_name VARCHAR(40) NOT NULL, doodad_price DECIMAL(10,2) UNSIGNED NOT NULL, doodad_on_hand MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, PRIMARY KEY (doodad_id), INDEX (doodad_name), INDEX (doodad_price), INDEX (doodad_on_hand) );
Figure 11.22. Creating the doodads table. 
This is a very simple table with just four columns. No column can be NULL, and all merit having an index placed on them. A real e-commerce application would probably have descriptions, sizes, weights, and so forth in such a table.
| 4. | Create the orders table (Figure 11.23).
CREATE TABLE orders ( order_id INT UNSIGNED NOT NULL AUTO_INCREMENT, doodad_id INT UNSIGNED NOT NULL, PRIMARY KEY (order_id), INDEX (doodad_id) );
This table is also very simple, too simple really. Obviously in the real world there might be multiple products in an order or multiple quantities of the same product. Orders would also be associated with, you know, clients. But I'm using just the bare-bones stuff here in order to best focus on the triggers themselves.
Figure 11.23. Creating the orders table. 
| 5. | Populate the doodads table (Figure 11.24).
INSERT INTO doodads VALUES (NULL, 'a', 19.95, 20), (NULL, 'b', 15.00, 10), (NULL, 'c', 22.95, 5), (NULL, 'd', 10.00, 15);
That'll be enough to demonstrate the concept. Now you can create the triggers that update the quantities in the doodads table when an order is placed.
Figure 11.24. A few sample doodads are added to the database. 
| 6. | Change the delimiter.
DELIMITER $$
Change the delimiter so that mysql doesn't choke when you use semicolons in the trigger's body.
| | | 7. | Create the INSERT trigger (Figure 11.25).
CREATE TRIGGER update_qty_insert AFTER INSERT ON orders FOR EACH ROW BEGIN UPDATE doodads SET doodad_on_hand=doodad_on_hand-1 WHERE doodads.doodad_id=NEW.doodad_id; END $$
To define the trigger, I start by giving it a unique name. Then I state that the trigger should take effect AFTER a row is INSERTed into the orders table.
The trigger itself runs one UPDATE query. The query subtracts 1 from the current doodad_on_hand value for the item being ordered. You can refer to this new (inserted) value by using NEW.doodad_id.
When you run this query:
INSERT INTO orders (doodad_id) VALUES (3)
That INSERT triggers update_qty_insert.
In the trigger, the UPDATE query uses the submitted value (3) in the WHERE conditional.
Figure 11.25. The first trigger updates a quantity when a new order is submitted. 
| 8. | Create the DELETE trigger (Figure 11.26).
CREATE TRIGGER update_qty_delete BEFORE DELETE ON orders FOR EACH ROW BEGIN UPDATE doodads SET doodad_on_hand=doodad_on_hand+1 WHERE doodads.doodad_id=OLD.doodad_id; END $$
This trigger differs from that in Step 7 in that it takes place BEFORE a DELETE query. (Technically, however, it could take place AFTER and still have the same effect in this particular instance.) The UPDATE query itself uses OLD.doodad_id to know which item was removed.
Note that the delimiter is still $$ until I change it back (in Step 9).
Figure 11.26. The second trigger updates a quantity when an existing order is cancelled (deleted). 
Dropping Triggers To remove an existing trigger, simply use DROP TRIGGER trigger_name
You can also use the more formal DROP TRIGGER database_name.trigger_name
Note that, at the time of this writing, there is no ALTER TRIGGER command. If you need to modify a trigger, you must drop and then re-create it. |
| | | 9. | Change the delimiter back to the semicolon.
DELIMITER ;
| 10. | Add some items to the orders table (Figure 11.27).
INSERT INTO orders (doodad_id) VALUES (1), (2), (3), (1), (1);
Each of these inserted rows will trigger update_qty_insert.
Figure 11.27. Five orders are added to the orders table. Each invokes the trigger, which in turn updates the quantities in the doodads table (see Figure 11.28). 
| 11. | Check the existing quantities (Figure 11.28).
SELECT * FROM doodads;
The results of this query should reveal how the doodad_on_hand values have been altered for each order.
Figure 11.28. The on-hand values have been reduced in conjunction with the added records in the orders table. 
| 12. | Delete an order.
DELETE FROM orders WHERE order_id=4;
This will have the net effect of increasing the doodad_on_hand value of the corresponding doodad by 1.
| 13. | Recheck the existing quantities (Figure 11.29).
SELECT * FROM doodads;
|
Figure 11.29. After deleting order number 4, which was for doodad number 1, the doodad_on_hand value for that doodad is increased. Compare with Figure 11.28. 
 Tips
You cannot create two triggers on the same table with the same time and event. In other words, there cannot be two BEFORE INSERT or AFTER DELETE actions. If you need to do multiple things with the same time and event, just create one trigger with multiple steps in its body. Stored routine, trigger, and view names are not case sensitive on any platform. Triggers can call stored routines.
|
