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)