A trigger is a database object that is associated with a table. It will be activated when a defined action is executed for the table.
The trigger can be executed when you run one of the following MySQL statements on the table: INSERT, UPDATE and DELETE. It can be invoked before or after the event.
Triggers are available in MySQL 5.0.2 and later.
However, the setup of a MySQL trigger requires Superuser privileges. On the servers offered by SiteGround, such privileges can be granted only to a user hosted on a dedicated solution. Granting Superuser privileges to a user hosted on a shared server is a security issue.
The alternative solution is to manipulate the data inserted using the above MySQL statements through a suitable php code in your scripts.
An example of the MySQL triggers usage can be found below:
- First we will create the table for which the trigger will be set.
mysql> CREATE TABLE people (age INT, name varchar(150));
- 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 ;
- We will 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
- At the end we will check the result.
mysql> SELECT * FROM people;
+-------+-------+
| age | name |
+-------+-------+
| 0 | Sid |
| 30 | Josh |
+-------+-------+
2 rows in set (0.00 sec)