MariaDB Create Trigger

Summary: in this tutorial, you will learn how to use the MariaDB create trigger statement to create a new trigger in a database.

Introduction to MariaDB create trigger statement

The create trigger statement allows you to create a new trigger in a database. The following illustrates the basic syntax of the create trigger statement:

create trigger trigger_name
{before | after} {insert | update | delete }
on table_name for each row
trigger_body;
Code language: SQL (Structured Query Language) (sql)

In this syntax:

• First, specify the name of the trigger that you want to create after the create trigger keywords. The trigger name must be distinct within a database.
• Second, specify the action time that the trigger is invoked. The action time can be either before or after a row is modified
• Third, specify an event that activates the trigger. MariaDB supports insert, update, and delete events.
• Fourth, indicate the name of the table to which the trigger belongs after the on keyword.
• Finally, specify a statement or statements to execute when the trigger is invoked.

If you want to execute multiple statements, you place them within the BEGIN END compound statement.

Inside the trigger body, you can access values of columns that are affected by an insert, update or delete statement. The old and new modifiers allow you to access values of the columns before and after the triggering event.

For instance, if you update the column gdp, inside the body of the update trigger, you can access the value of the old and new values of the gdp column using these syntaxes: old.gdp and new.gdp.

This table shows the availability of the old and new modifiers in each trigger:

MariaDB create trigger statement example

First, create a copy of the country_stats table:

create table country_reports
select * 
from country_stats;
Code language: SQL (Structured Query Language) (sql)

Second, create a table called population_logs to log the changes in the population column of the country_reports table:

create table population_logs(
    log_id int auto_increment,
    country_id int not null,
    year int not null,
    old_population int not null,
    new_population int not null,
    updated_at timestamp default current_timestamp,
    primary key(log_id)
);
Code language: SQL (Structured Query Language) (sql)

Third, create a trigger invoked before a change is made to the country_reports table.

create trigger before_country_reports_update 
    before update on country_reports
    for each row
    insert into population_logs(
        country_id, 
        year, 
        old_population, 
        new_population
    )
    values(
        old.country_id,
        old.year,
        old.population,
        new.population
    );
Code language: SQL (Structured Query Language) (sql)

Fourth, select data from the country id 100 and in the year of 2018 from country_reports table:

select * 
from country_reports
where 
    country_id = 100 and 
    year = 2018;
Code language: SQL (Structured Query Language) (sql)

Fifth, update the population for country id 100 in the year of 2018:

update 
    country_reports
set 
    population = 1352617399
where 
    country_id = 100 and 
    year = 2018;
Code language: SQL (Structured Query Language) (sql)

Sixth, query data from the population_logs table:

select * 
from population_logs;
Code language: SQL (Structured Query Language) (sql)

As seen clearly from the output, the trigger was automatically fired and inserted a new row into the population_logs table.

In this tutorial, you learned how to use the MariaDB create trigger statement to create a new trigger.