How to Create Trigger in MariaDB

In this MariaDB tutorial, I am going to walk you through a comprehensive masterclass on how to create MariaDB triggers. Whether you are a lead developer or a systems admin, this guide will provide the authoritative foundation you need.

How to Create Trigger in MariaDB

Step 1: Understanding the Core Syntax

To create a trigger in MariaDB, we use the CREATE TRIGGER statement.

The basic structure looks like this:

SQL

CREATE TRIGGER trigger_name
{ BEFORE | AFTER } { INSERT | UPDATE | DELETE }
ON table_name FOR EACH ROW
trigger_body;

Breaking Down the Syntax

Let’s look at each part of this command.

Component	Description	Professional Tip
trigger_name	A unique identifier for the trigger.	Use a standard naming convention like bi_employees_audit (Before Insert).
timing	BEFORE or AFTER.	Use BEFORE to validate or modify data; use AFTER for logging or syncing.
event	INSERT, UPDATE, or DELETE.	Remember that TRUNCATE does not fire triggers.
FOR EACH ROW	Ensures the trigger runs for every affected row.	MariaDB currently only supports row-level triggers.
trigger_body	The actual SQL code to execute.	Keep this logic as lean as possible to avoid performance bottlenecks.

Step 2: The Critical Role of Delimiters

The number one mistake I see is forgetting to change the delimiter. By default, MariaDB uses the semicolon (;) to signify the end of a command. However, if your trigger contains multiple statements, each of those statements will also end in a semicolon.

If you don’t change the delimiter, MariaDB will see the first semicolon inside your trigger and try to execute it prematurely, resulting in a syntax error.

How to Properly Handle Delimiters

In a professional environment, you wrap your trigger creation in a custom delimiter (usually // or $$):

1. Change the Delimiter: Tell the client to stop looking for ;.
2. Define the Trigger: Write the full SQL block.
3. Reset the Delimiter: Change it back to ; for the rest of your script.

Step 3: Working with NEW and OLD Keywords

To create powerful triggers, you must be able to compare data. MariaDB provides two virtual “row images” that you can reference within the trigger body: NEW and OLD.

• NEW: This refers to the data as it will exist after the operation (available in INSERT and UPDATE triggers).
• OLD: This refers to the data as it existed before the operation (available in UPDATE and DELETE triggers).

Keyword Availability Matrix

Event	OLD Keyword	NEW Keyword
INSERT	No	Yes
UPDATE	Yes	Yes
DELETE	Yes	No

Step 4: Crafting the Trigger Body

The “body” is where the actual magic happens. In my practice, I categorize trigger bodies into two types: Single-statement and Multi-statement.

Single-Statement Triggers

If you are performing a simple action, such as converting a username to lowercase before it’s saved, you can write the logic directly after the FOR EACH ROW clause. It’s elegant, fast, and easy to maintain.

Multi-Statement Triggers

For more complex scenarios—such as checking a condition, updating an audit table, and then sending a signal—you must use the BEGIN ... END block. This is where the delimiter change we discussed earlier becomes mandatory.

Step 5: Implementing Advanced Conditional Logic

As a DBA, you often encounter business requirements that aren’t black and white. For instance, a logistics company in Miami might only want to trigger an audit if the shipping status changes to “Delayed,” but not if it changes from “Pending” to “In Transit.”

Inside the trigger body, you can use IF ... THEN ... ELSE constructs. This allows your database to make intelligent decisions at the row level without requiring the application layer to intervene.

Example 1: Create a 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 EXISTS option. A trigger is created when the same type of trigger doesn’t exist or isn’t 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;

After executing the above queries, I got the expected output as shown in the screenshot below.

Example 2: 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 action will be triggered to 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 MariaDB, then delimiters are used to differentiate between the regular delimiter and a delimiter inside a BEGIN END block. DELIMITER is a 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 in the contacts table.

Example 3: 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;

Example 4: 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;

After executing the above queries, I got the expected output as shown in the screenshot below.

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;

Example 5: 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. After executing the above query, I got the expected output as shown in the screenshot below.

In the above output, the trigger is created as newsame_trigge

Using the SIGNAL Command

A key feature I often use to enforce data integrity is the SIGNAL command. If a trigger detects a violation (e.g., an employee trying to delete a record they don’t own), you can “signal” an error. This effectively rolls back the transaction and sends a specific error message back to the application.

Conclusion

Mastering MariaDB triggers is a transformative step for any database professional. By moving critical logic into the database layer, you ensure that your data remains consistent, auditable, and secure, regardless of how many applications connect to it.

I’ve seen triggers save companies thousands of dollars by preventing data corruption and automating manual audit tasks.

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