How to Create Trigger in MariaDB

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