Introduction to MariaDB and MySQL triggers

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 of sudo 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.



Comments and Discussions
Linux Forum