Objective
Understanding and learning to use MariaDB and MySQL triggers.
Requirements
- No particular requirements needed
Conventions
- # – requires given linux command to be executed with root privileges either
directly as a root user or by use ofsudo
command - $ – given linux command to be executed as a regular non-privileged user
Introduction
MySQL/MariaDB triggers
are stored programs associated with a table in a database, and used to automatically perform some actions when an INSERT
, DELETE
or UPDATE
event is performed on the table. A trigger can be set to perform an action either before or after the event
it is associated to. In this tutorial, we will see how to create and manage a trigger.
A test database
For the sake of this tutorial, we will create a database with just one and very simple table, with a list of books and their respective genres. Let’s proceed:
MariaDB [(none)]> CREATE DATABASE book_test;
MariaDB [(none)]> CREATE TABLE book_test.book (
-> id SMALLINT(1) UNSIGNED NOT NULL AUTO_INCREMENT,
-> name VARCHAR(25) NOT NULL,
-> genre VARCHAR(25) NOT NULL,
-> PRIMARY KEY(id));
We created our trivial table, now we should populated it with some books. Here are some of my favorites:
MariaDB [(none)]> USE book_test;
MariaDB [book_test]> INSERT INTO book (name, genre) VALUES
-> ('1984', 'Dystopian'),
-> ('The Lord Of The Rings', 'Fantasy'),
-> ('On the Genealogy of Morality', 'Philosophy');
That’s enough for our example. Here is the visual representation of our table:
+----+---------------------------+------------+
| id | name | genre |
+----+---------------------------+------------+
| 1 | 1984 | Dystopian |
| 2 | The Lord Of The Rings | Fantasy |
| 3 | On the Genealogy of Moral | Philosophy |
+----+---------------------------+------------+
Now that we prepared our test table, we can see how to create and associate a trigger
to it.
Create a trigger
As said before, by creating a trigger, we can let our database automatically perform a certain action whenever the specified event, which can be one among INSERT
, UPDATE
or DELETE
, is performed on the table. Let’s say, for example, that for some strange reason, we don’t want to allow more than one Philosophy book in our collection, how can we enforce this rule? While the restriction can be implemented at an higher level, we can set it directly in the database, using a trigger. The syntax to create one is very easy:
CREATE TRIGGER trigger_name # Assign a name to the trigger
{BEFORE | AFTER } # Set when the trigger should be executed
{INSERT | DELETE | UPDATE} # Set the statement associated with the trigger
ON table_name # Set the table associated with the trigger
FOR EACH ROW trigger_stmt # Declare the trigger body
Following the above syntax, we can create our trigger:
MariaDB [book_test]> delimiter $
MariaDB [book_test]> CREATE TRIGGER no_more_philosophy BEFORE INSERT ON book_test.book
-> FOR EACH ROW BEGIN
-> IF NEW.genre = "Philosophy" AND (SELECT COUNT(*) FROM book_test.book WHERE genre = "Philosophy") > 0 THEN
-> SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Only one Philosophy book is allowed!';
-> END IF;
-> END$
MariaDB [book_test]> delimiter ;
The first thing we have done in Line 1, is to instruct the database to use the $
character as the statement delimiter instead of the default ;
. This is because the semicolon delimiter will be used inside the trigger body.
We then created the trigger using the CREATE TRIGGER
statement in Line 2, followed by the name
we want to assign to it: “no_more_philosophy” in this case. After that, we specified that the trigger should be executed BEFORE
the INSERT
statement. Immediately after, we associated the trigger with the “book” table.
The body of the trigger begins with FOR EACH ROW
in Line 3: we used BEGIN
to mark the beginning of our compound statements, the ones that should be executed when the trigger is called, and we marked the end of it with END
, just like we do with other procedures.
Once the trigger is associated with the table it will run before each row insertion is performed.
When a trigger is performed, two pseudorecords
are populated: OLD
and NEW
: the values assigned to them varies depending on the event type. For an INSERT
statement, since the row is new, the OLD
pseudorecord will contain no values, while NEW
will contain the values of new row that should be inserted. The opposite will happen for a DELETE
statement: OLD will contain the old values, and NEW will be empty. Finally for UPDATE
statements, both will be populated, since OLD will contain the old values of the row, while NEW will contain the new ones.
Our trigger in Line 4 will check the value of the genre
column for the new row (identified by NEW
): if it is set to “Philosophy”, it will query for books with the ‘Philosophy’ genre, and check if at least one already exists. If it’s the case, it will raise an exception with the message ‘Only one Philosophy book is allowed!’.
As a last thing in Line 8, we set the delimiter back to ;
.
Our trigger in action
Let’s check our trigger in action: we will try to insert a new book with the “Philosophy” genre and see what happens:
MariaDB [book_test]> INSERT INTO book(name, genre) VALUES ('Republic', 'Philosophy');
ERROR 1644 (45000): Only one Philosophy book is allowed!
As you can see, the trigger worked, and the server responded with the error message we set when we tried to add another philosophy book to our collection.
Manage triggers
To check the triggers in a database, all we have to do is to run the SHOW TRIGGERS
command:
MariaDB [book_test]> SHOW TRIGGERS \G;
*************************** 1. row ***************************
Trigger: no_more_philosophy
Event: INSERT
Table: book
Statement: BEGIN IF NEW.genre = "Philosophy" AND (SELECT COUNT(*) FROM book_test.book WHERE genre = "Philosophy") > 0 THEN SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Only one Philosophy book is allowed!'; END IF; END
Timing: BEFORE
Created: NULL
sql_mode: NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
Definer: root@localhost
character_set_client: latin1
collation_connection: latin1_swedish_ci
Database Collation: latin1_swedish_ci
Dropping a trigger it’s just as easy: all we have to do is to reference the trigger by its name. For example, if we would like to remove the “no_more_philosophy” trigger, we should run:
MariaDB [book_test]> DROP TRIGGER no_more_philosophy;
If we know query the database for existing triggers, we receive an empty set:
MariaDB [book_test]> SHOW TRIGGERS; Empty set (0.01 sec)
Conclusions
In this tutorial we learned what a trigger is, and the syntax that should be used to create one. We also created a trivial table, and associated the trigger with it, seeing how it can be used to ensure a specific rule. Finally we saw how we can check the existing triggers in a database, and how we can delete one. Although this should be enough to get you started, you can check the official MariaDB/MySQL documentation for a more in-depth knowledge.