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.
You can create and use triggers on any A2 Hosting server that uses MySQL.
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:
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!
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 ;
The DELIMITER command at the beginning of these statements prevents MySQL from processing the trigger definition too soon. The DELIMITER command at the end of these statements returns processing to normal.
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.