Using MySQL triggers

This article describes how to create and use MySQL triggers on your A2 Hosting account. Triggers are predefined rules associated with a table. They can be called ("triggered") before or after a SQL statement inserts, updates, or deletes data from the associated table.

You can use triggers in a wide range of scenarios. For example, you could use a trigger to automatically change certain values when a row is updated. Or you could specify a set of actions to take whenever a row is deleted. The possibilities are almost endless.

Creating and using triggers

You can create and use triggers on any A2 Hosting server that uses MySQL.

Setting up a test database

To demonstrate a basic example of a trigger in action, let's start by creating a database for testing purposes. In the following SQL statement, replace username with your account username:

CREATE DATABASE username_test;

If you are using phpMyAdmin, click the name username_test to select the database. Otherwise, if you are using the mysql command-line program, type the following SQL statement:

USE username_test;

Next, create a table in the username_test database named products. To do this, run the following SQL statement:

CREATE TABLE products (prod_id INT NOT NULL AUTO_INCREMENT, prod_name VARCHAR(20) NOT NULL, prod_cost FLOAT NOT NULL DEFAULT 0.0, prod_price FLOAT NOT NULL DEFAULT 0.0, PRIMARY KEY(prod_id));

The following SQL statement adds some sample data to the products table:

INSERT INTO products (prod_name, prod_cost, prod_price) VALUES ('Basic Widget',5.95,8.35),('Micro Widget',0.95,1.35),('Mega Widget',99.95,140.00);

Now we're ready to create a trigger for our table!

Creating the trigger

Let's create a trigger named updateProductPrice. This particular trigger is activated whenever the products table is updated. When this event occurs, the trigger checks each row to see if the product cost (prod_cost) value is being changed. If it is, then the trigger automatically sets the item's new price (prod_price) to 1.40 times the item's new cost (in other words, a 40% markup).

To create this trigger, run the following MySQL statements:

DELIMITER $$
CREATE TRIGGER `updateProductPrice`
BEFORE UPDATE ON `products`
FOR EACH ROW
BEGIN
  IF NEW.prod_cost <> OLD.prod_cost
    THEN
      SET NEW.prod_price = NEW.prod_cost * 1.40;
  END IF ;
END$$
DELIMITER ;

Using the trigger

The updateProductPrice trigger is now ready to be invoked automatically whenever a row in the products table is updated. For example, run the following SQL statement to change the cost of the Basic Widget:

UPDATE products SET prod_cost = 7.00 WHERE prod_id = 1;

When you run this SQL statement, the trigger activates as well, and automatically updates the Basic Widget's price in proportion to the new cost. To verify this, you can run the following SQL statement:

SELECT * FROM products;

This statement returns the following results:

+---------+--------------+-----------+------------+
| prod_id | prod_name    | prod_cost | prod_price |
+---------+--------------+-----------+------------+
|       1 | Basic Widget |         7 |        9.8 |
|       2 | Micro Widget |      0.95 |       1.35 |
|       3 | Mega Widget  |     99.95 |        140 |
+---------+--------------+-----------+------------+
3 rows in set (0.00 sec)

As you can see, the updateProductPrice trigger has automatically updated the Basic Widget's price (9.80) based on the new cost (7.00). Although this is a simple example of what a trigger can do, you can use the same techniques in your own databases—the possibilities are almost endless.

More Information

• For more information about MySQL triggers, please visit http://dev.mysql.com/doc/refman/5.0/en/triggers.html.
• For more information about the CREATE TRIGGER statement, please visit http://dev.mysql.com/doc/refman/5.0/en/create-trigger.html.