In this MariaDB tutorial, we will learn, how to create a trigger in MariaDB. Let us see a few examples of “MariaDB create trigger” and cover the following topics:
- MariaDB create trigger definer
- MariaDB create trigger permission
- MariaDB create trigger if not exists
- MariaDB create trigger begin and delimiter
- MariaDB create trigger after and before insert
- MariaDB create trigger after and before update
- MariaDB create or replace trigger
- MariaDB show or drop trigger
MariaDB create trigger definer
A trigger is connected with a table that is a database object in the MariaDB. Now, as its name specifies, the trigger gets activated when a specific event arises for a table.
A trigger should be associated with the table and this table must not be a temporary table like view or trigger cannot be associated with a virtual or temporary table.
The syntax for the trigger is given below.
CREATE OR REPLACE
{DEFINER = ( user | CURRENT_USER | role | CURRENT_ROLE )}
TRIGGER (IF NOT EXISTS) name_of_trigger time_of_trigger trigger_event
ON tbl_name FOR EACH ROW
{( FOLLOWS | PRECEDES } name_of_other_trigger }
trigger_stmt;
- OR REPLACE: If this option is used then the already existing trigger is deleted and code will be changed using the new trigger.
- DEFINER: It makes sure the security condition should used once verifying the access privileges while the time of trigger activation.
- IF NOT EXIST: It is used to create a trigger when the same kind of trigger doesn’t exist.
- time_of_trigger: It is used to indicate the action time of a trigger. The action time of trigger is BEFORE or AFTER to specify whether the trigger fires before or after each row to be changed.
- trigger_event: It denotes to the statements that will activate the trigger. Below ar given the trigger_event:
- INSERT: When a new rows are added into the table then a trigger gets activated, such as through INSERT, REPLACE, and LOAD DATA statements.
- UPDATE: When a row is changed then the trigger is fired, It is used with UPDATE statements.
- DELETE: When a rows are removed from the table then a trigger will be fired, such as, through REPLACE and DELETE statements.
Read: How to Create Table in MariaDB
MariaDB create trigger if not exists
In MariaDB, before creating the new trigger, we can check whether the same trigger already exists or not using the IF NOT EXIST option. The trigger is created when the same kind of trigger doesn’t exist or is associated with a table.
Let’s create the trigger using the IF NOT EXIST option.
DELIMITER //
CREATE TRIGGER IF NOT EXISTS new_trigger
AFTER INSERT
ON contacts FOR EACH ROW
BEGIN
END; //
DELIMITER ;
SHOW TRIGGERS;

Read: How to Add Column in MariaDB
MariaDB create trigger permission
If the MariaDB database doesn’t have any permission to create a trigger, then any user can’t create the trigger. So if you want to create the trigger in the database, then the database must have trigger privileges.
Let’s create the new trigger on the default database MySQL in MariaDB.
DELIMITER //
CREATE TRIGGER contacts_before
BEFORE INSERT
ON contacts FOR EACH ROW
BEGIN
DECLARE cUser varchar(50);
-- Find username of current person performing INSERT into table
SELECT USER() INTO cUser;
-- It updates the create_date field to current system date
SET NEW.created_date = SYSDATE();
-- It updates the created_by field to the username of the current person performing the INSERT
SET NEW.created_by = cUser;
END; //
DELIMITER ;
When you create the trigger without permission, the error will appear.
Let’s fix the above error by granting the trigger permission to the database MySQL.
GRANT TRIGGER ON mysql.* TO 'root'@localhost;

Again create the trigger using the above code, then you will be able to create the trigger.
Also, check: How to Drop Column from MariaDB Table
MariaDB create trigger begin and delimiter
The trigger gets executed on time like before, after, which means the trigger will activate after and before the statement like INSERT, UPDATE, etc. here whatever the action will trigger perform on the table, that goes into the section of BEGIN END block.
Let’s create a table as contacts.
CREATE TABLE contacts
( contact_id INT(11) NOT NULL AUTO_INCREMENT,
first_name VARCHAR(30),
last_name VARCHAR(30) NOT NULL,
birthday DATE,
created_date DATE,
created_by VARCHAR(30),
CONSTRAINT contacts_pk PRIMARY KEY (contact_id)
);
Now create the trigger.
DELIMITER //
CREATE TRIGGER contacts_before
BEFORE INSERT
ON contacts FOR EACH ROW
BEGIN
DECLARE cUser varchar(50);
-- Find username of current person performing INSERT into table
SELECT USER() INTO cUser;
-- It updates the create_date field to current system date
SET NEW.created_date = SYSDATE();
-- It updates the created_by field to the username of the current person performing the INSERT
SET NEW.created_by = cUser;
END; //
DELIMITER ;
Insert the record to activate the trigger.
INSERT INTO contacts(first_name,last_name,birthday)VALUES('Jhon','M','1999-01-25');

- In the above output, from lines 1 to 9, we are creating the table as contacts. After that, we defined the trigger as contacts_before from lines 11 to 35, which started using the DELIMITER.
- When we create the stored programs in the MariaDB, then delimiters are used to differentiate between the regular delimiter and a delimiter inside a BEGIN END block. DELIMITER is kind of semicolon in MariaDB.
- In lines 34 and 36, we are inserting new records in the table contacts using the INSERT INTO statement and viewing the information of the contacts table.
Read: MariaDB Vs SQL Server
MariaDB create trigger after and before insert
In MariaDB, a trigger will activate after or before insertion operation or insert statement, to achieve this kind of behavior, we have will use the AFTER | BEFORE keyword while creating the new trigger. This ensures that the trigger activates after or before the insert statement is executed.
Let’s create a table as a country.
CREATE TABLE country (id mediumint(9)
NOT NULL AUTO_INCREMENT,
name char(30) NOT NULL,
PRIMARY KEY (`id`));
CREATE TABLE country_count (countries int);
INSERT INTO country_count (countries) VALUES(0);

Create a trigger as a country.
CREATE TRIGGER increment_country
AFTER INSERT ON country
FOR EACH ROW
UPDATE country_count SET country_count.countries = country_count.countries+1;
Now insert the following records and look at the increment_country table.
INSERT INTO country(name)VALUES('United States'),('Canada'),('New Zealand');
View the table to see the inserted records as country names like the United States, Canada, and New Zealand.
SELECT * FROM country;

Three records were inserted into the table country, the trigger we have created above was also activated three times.
SELECT * FROM country_count;

As we have created a trigger that activates after the insert statement, we can also create a trigger that will activate before the insert statement. To achieve this kind of task, we need to specify trigger time as BEFORE in the above code. Let’s see.
CREATE TRIGGER increment_country
BEFORE INSERT ON country
FOR EACH ROW
UPDATE country_count SET country_count.countries = country_count.countries+1;
Now insert the same record again, first please drop the tables country and trigger increment_country. Then again create the tables and trigger ( use the trigger that contains the BEFORE ) using the below code.
CREATE TABLE country (id mediumint(9)
NOT NULL AUTO_INCREMENT,
name char(30) NOT NULL,
PRIMARY KEY (`id`));
CREATE TABLE country_count (countries int);
INSERT INTO country_count (countries) VALUES(0);
CREATE TRIGGER increment_country
BEFORE INSERT ON country
FOR EACH ROW
UPDATE country_count SET country_count.countries = country_count.countries+1;
INSERT INTO country(name)VALUES('United States'),('Canada'),('New Zealand');
SELECT * FROM country_count;

Read: How to Grant User Access to a MariaDB Database
MariaDB create trigger after and before update
The trigger can be activated after or before the update operation, here we are going to use the same syntax for creating the trigger, instead of AFTER | BEFORE INSERT, the AFTER | BEFORE UPDATE statement will be used here.
We are going to use the same table that is contacts which we created in the above section.

Create a new table as contacts_update, this table will contain the information about the user who updated the data in the table contacts.
CREATE TABLE contacts_update(contact_id INT,updated_date DATE,updated_by VARCHAR(30));
Let’s create a trigger contacts_after_update.
DELIMITER //
CREATE TRIGGER contacts_after_update
AFTER UPDATE
ON contacts FOR EACH ROW
BEGIN
DECLARE cUser varchar(50);
-- Find username of currenr person performing the INSERT into table
SELECT USER() INTO cUser;
-- Insert record into contact_update table
INSERT INTO contacts_update
( contact_id,
updated_date,
updated_by)
VALUES
( NEW.contact_id,
SYSDATE(),
cUser );
END; //
DELIMITER ;
Now update the last_name of the person in the table contacts and view the updated data.
UPDATE contacts
SET last_name = 'Mark'
WHERE contact_id =1;
SELECT * FROM contacts;

In the above output, we can see that the last_name update from ‘M’ to ‘Mark’. Let’s see who updated the last_name from the contacts_update table.
SELECT * FROM contacts_update;

Read: How to Create Database in MariaDB
MariaDB create or replace trigger
Suppose in MariaDB we have already created a trigger but after some time we have to update that trigger, so we can’t do that without creating a new trigger of the same kind with updated information. When the same kind of trigger is created then it will show an error because that kind of trigger already exists.
To resolve this error while creating the new trigger, the new option is used with the CREATE TRIGGER statement and that is the OR REPLACE option.
Now create a new trigger with OR REPLACE option using the below code.
DELIMITER //
CREATE OR REPLACE TRIGGER newsame_trigger
AFTER INSERT
ON contacts FOR EACH ROW
BEGIN
END; //
DELIMITER ;
SHOW TRIGGERS;
In the above code, we are creating the trigger as newsame_trigger that will replace the existing trigger of the same name with a new one.

In the above output, the trigger is created as newsame_trigger.
Read: How to create a user in MariaDB
MariaDB show and drop trigger
To drop trigger in MariaDB use the below syntax.
DROP TRIGGER trigger_name;
To show the trigger use the below syntax.
SHOW TRIGGERS;
You may also like to read the following MariaDB tutorials.
- How to Create Function in MariaDB
- MariaDB Update Statement with Examples
- MariaDB Case Statement
- MariaDB Cast
So, in this tutorial, we have learned about the “MariaDB create trigger” and also covered the following topics.
- MariaDB create trigger definer
- MariaDB create trigger permission
- MariaDB create trigger if not exists
- MariaDB create trigger begin and delimitter
- MariaDB create trigger after and before insert
- MariaDB create trigger after and before update
- MariaDB create or replace trigger
- MariaDB show or drop trigger
I am Bijay having more than 15 years of experience in the Software Industry. During this time, I have worked on MariaDB and used it in a lot of projects. Most of our readers are from the United States, Canada, United Kingdom, Australia, New Zealand, etc.
Want to learn MariaDB? Check out all the articles and tutorials that I wrote on MariaDB. Also, I am a Microsoft MVP.