Home
/
Database Support
/
What are MySQL triggers and how to use them?

What are MySQL triggers and how to use them?

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

Share This Article