What are MySQL triggers and how to use them?

The trigger can be executed when you run one of the following MySQL statements on the table: INSERT, UPDATE and DELETE and it can be invoked before or after the event.

You can find a detailed explanation of the trigger functionality and syntax in this article.

The main requirement for running such MySQL Triggers is having MySQL SUPERUSER privileges.

Granting SUPERUSER MySQL privileges to a user hosted on a SiteGround server is not possible due to the server setup.

Here is an example of a MySQL trigger:

• First, create the table for which the trigger will be set via SSH:

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

• Next, 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 ;

• Insert two records to check the trigger functionality.

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

• In the end, check the result.

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