MySQL Triggers and How to Use Them

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)


Was this article helpful?

mood_bad Dislike 0
mood Like 0
visibility Views: 1701