Knowledgebase

he 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. 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. You can find detailed explanation of the trigger functionality including its syntax in the following article:

http://dev.mysql.com/doc/refman/5.0/en/triggers.html

However, the setup of a MySQL trigger requires the MySQL SUPERUSER privileges. On the servers offered by SiteGround, such privileges can be granted only on dedicated servers or VPS hosting. Granting SUPERUSER privileges to a user hosted on a shared server is a security issue and this is why we don’t do it.

The alternative solution is to manipulate the data inserted using the above MySQL statements through a suitable php code in your scripts.

Here is an example of a MySQL trigger:

• 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)