MySQL Triggers: What Are They and How to Use Them?

MySQL Trigger Explained

A database object associated with a table is called as MySQL trigger. It gets activated when a defined actions is executed for the table. You can execute the trigger after running the MySQL statements on the table such as INSERT, UPDATE and DELETE and it can be cited either prior to or after the event.

A unique name should be used for each trigger associated with a table. But, you can have the same trigger name for different tables though it’s a good practice.

To run such MySQL Triggers you need to have MySQL SUPERUSER privileges.

MilesWeb doesn’t offer such privileges on the shared and reseller hosting solutions. They offer these privileges on VPS, dedicated and cloud.

Creating and Using Triggers

Setting up a Test Database

For displaying a basic example of a trigger in action, we will need to create a database for testing purposes. In the below SQL statement, replace the username with your account username:

CREATE DATABASE username_test;

Note: It is possible to run the previous SQL command (and the following SQL commands) from the command line with the MySQL tool, or in your web browser with the use of phpMyAdmin.

In case of phpMyAdmin, select the database by clicking the name username_test . Or else if your are using the MySQL command-line program, input the below SQL statement:

USE username_test;

Now, create a table with the name products in the username_test database. Run the following SQL statement for this:

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));

Some sample data is added to the products table by using the below SQL statement:

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 can create a trigger for our table!

Related: How to Import and Export the MySQL Database with phpMyAdmin?

Creating the Trigger

We will create a trigger named as updateProductPrice. This type of trigger is activated every time the products table is updated. During the occurrence of this event, the trigger verifies each row to check if the product cost (prod_cost) value has got changed. If it is changed, then the item’s new price (prod_price) will be automatically set up by the trigger to 1.40 times the item’s new cost (in simple words, a 40% markup).

Run the below MySQL statements to create this trigger:

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 ;

Note: The DELIMITER command used at the start of these statements stops MySQL from processing the trigger definition too early. The DELIMITER command used at the end of these statements returns processing to normal.

Using the Trigger

The updateProductPrice trigger can now be invoked automatically every time a row in the products table is updated.

For example, let’s change the cost of the Basic Widget by running the below SQL statement:

UPDATE products SET prod_cost = 7.00 WHERE prod_id = 1;

After running this SQL statement, the trigger activates and also, automatically updates the Basic Widget’s price in proportion to the new cost. You can check this by running the following SQL statement:

SELECT * FROM products;

You get 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)

You can check that the updateProductPrice trigger automatically updated the Basic Widget’s price (9.80) based on the new cost (7.00). This is just a simple example of what a trigger can do but you use the same techniques in your own databases – you will get endless possibilities.

So, you have learnt about MySQL triggers and their usage now.