MariaDB Triggers

Summary: in this tutorial, you will learn about MariaDB triggers and the main differences between triggers and stored procedures.

What is a trigger

A trigger is a stored database object that is automatically executed in response to a data modification event on a table.

The data modification events include insert, update, and delete caused by the insert, update, and delete statements.

MariaDB stored triggers as named objects in the data dictionary. A trigger is always associated with a table.

MariaDB trigger types

MariaDB supports the following types of triggers:

• The insert trigger is automatically executed when an insert statement adds a new row to a table.
• The update trigger is automatically fired when an update statement modifies the data on a table.
• The delete trigger is automatically invoked when a delete statement removes one or more rows from a table.

MariaDB further classifies each type of trigger by trigger time including before and after:

• An before trigger is fired right before an event occurs. For example, before the insert event occurs, you can validate the values that are being inserted. Typically, you use a before trigger for data cleansing and modification.
• An after trigger is invoked after an event occurs. Generally, you use an after trigger to keep audit trails.

When you combine the trigger time and event, you can have up to six triggers per table:

•  before insert
•  after insert
•  before update
•  after update
•  before delete
•  after delete

Since MariaDB 10.2.3, you can define multiple triggers for the same trigger time (before and after) and trigger events (insert, update, and delete).

MariaDB executes a trigger for each row affected by the insert, update, and delete event. This trigger is called a row-level trigger.

If a trigger is fired solely based on the trigger event regardless of the number of rows affected, the trigger is called the statement-level trigger. Currently, MariaDB does not support statement-level triggers.

Triggers vs. stored procedures

Similar to stored procedures, triggers contain procedural code.

The following table displays the main differences between triggers and stored procedures:

Managing triggers in MariaDB

• Create trigger – create a new trigger by using the create trigger statement.
• Drop trigger – remove a trigger from the database using the drop trigger statement.

In this tutorial, you have learned about MariaDB triggers and the main differences between triggers and stored procedures.