MilesWeb
UK Web Hosting Company
Award-winning Web Hosting Company
Everything that you need to build, host and manage a website is available at just 0.75/mo

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:

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:

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

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

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:

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:

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:

You get the following results:

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.

Pallavi Godse

Pallavi is a Digital Marketing Executive at MilesWeb and has an experience of over 4 years in content development. She is interested in writing engaging content on business, technology, web hosting and other topics related to information technology.

Leave a Reply

Your email address will not be published. Required fields are marked *