Skip to main content
This article describes how to create and use MySQL triggers on your hosting.com 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 hosting.com 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:
You can run the previous SQL command (and the following SQL commands) from the command line using the MySQL tool, or in your web browser using phpMyAdmin.
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:
The following SQL statement adds some sample data to the products table:
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:
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.

​
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:
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:
This statement returns the following results:
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.