Casbay Knowledge Base

Search our articles or browse by category below

What are MySQL triggers and how to use them?

Last updated: September 8, 2022
Estimated reading time: 1 min

What are MySQL triggers and how to use them?

The MySQL trigger is a database object that is associated with a table. It will be activated when a defined action is executed for the table. Then, the trigger can be executed when you run one of the following MySQL statements on the table: INSERTUPDATE and DELETE and it can be invoked before or after the event. 

In addition, you can find detailed explanation of the trigger functionality and syntax in this article.

In fact, the main requirement for running such MySQL Triggers is having MySQL SUPERUSER privileges.

Such privileges can be granted on the VPS and Dedicated Servers. Granting SUPERUSER MySQL privileges to a user hosted on a Shared Server is not possible due to our server setup.

Here is an example of a MySQL trigger:

1. Firstly, we will create the table for which the trigger will be set via SSH.

mysql> CREATE TABLE people (age INT, name varchar(150));

2. Next we will define the trigger. It will be executed before every INSERT statement for the people table.

mysql> delimiter //mysql> CREATE TRIGGER agecheck BEFORE INSERT ON people FOR EACH ROW IF NEW.age < 0 THEN SET NEW.age = 0; END IF;// Query OK, 0 rows affected (0.00 sec)mysql> delimiter ;

3. Then, we will insert two records to check the trigger functionality.

mysql> INSERT INTO people VALUES (-20, ‘Adam’), (30, ‘Mark’);Query OK, 2 rows affected (0.00 sec)Records: 2 Duplicates: 0 Warnings: 0

4. Lastly, we will check the result.

mysql> SELECT * FROM people;+——-+——-+| age | name |+——-+——-+| 0 | Adam || 30 | Mark |+——-+——-+2 rows in set (0.00 sec)

We hope this article helped you to learn about what are MySQL triggers and how to use them. For more articles, kindly visit our Knowledge Base.

Was this article helpful?
Dislike 0
Previous: HOW TO: Edit .htaccess File via the cPanel FileManager
Next: HOW TO: Create email in cPanel
Discover the perfect balance of performance and budget-friendly Dedicated Server plan !
Discover the perfect balance of performance and budget-friendly Dedicated Server plan !
High performance and low cost Dedicated Server plan 128GB from $185 – upgrade today!
High performance and cheap Dedicated Server plan 128GB from $185 – upgrade today!