MariaDB trigger

Introduction to MariaDB trigger

MariaDB provides trigger functionality to the user, in which we create trigger, drop trigger, update trigger etc. Normally trigger is specially used for stored procedure and trigger automatically runs when any event occurs on the MariaDB server. When users try to modify data by using the data manipulation language event at that time DML runs triggers. Under the data manipulation language we can perform different operations such as INSERT, UPDATE or DELETE statement on view or table. Triggers only fire when any valid event execution that is row is affected or not. The DDL trigger runs only in response to a variety of data definition language events. Basically it is used for transactions.

Syntax

create trigger specified name for trigger
{Before or after} {Different operation like insert or update or delete }
on specified table for each row
trigger body;

Explanation

In the above syntax we use create trigger statement, here we first specified trigger name that we need to create and trigger name used after create trigger keyword and name of trigger must be distinct within the database. Second we define the action that the trigger is invoked, the action can be before or after that dependent row is modified, the trigger action follows the specified event and MariaDB supports insert, update and delete events. Next line indicates the name of the table on which the trigger belongs and finally we specify the statement for which the trigger is invoked. If we need to execute the multiple statement then we can use begin and end.

How does trigger work in MariaDB?

Let’s see how triggers work in MariaDB as follows.

Basically trigger event may be insert, update or delete and trigger can be executed before or after the event. Now let’s see the workflow of triggers as follows.

If we executed the replace statement then workflows as follows.

1. Replace statement executed before insert operation.
2. Then before the update operation.
3. Finally execute after update operation.
4. Otherwise it works normally like an insert statement.

Triggers and errors

When we execute triggers with non transactional storage engines id before statement generates the error and statement will not executed. If a warning is generated with a signal or resignal statement is considered as an error.

Types of triggers in MariaDB with Example

Normally MariaDB triggers have six types as follows.

Example #1 – The before update trigger

In this type trigger invoke before the update statement that means if we execute update statement then action of trigger will be executed before update statement.

Example:

CREATE TABLE customer_info
( customer_id INT(25) NOT NULL AUTO_INCREMENT,
Customer_last_name VARCHAR(30) NOT NULL,
Customer_first_name VARCHAR(25),
Customer_birthday DATE,
created_date DATE,
created_by VARCHAR(30),
CONSTRAINT customer_info_pk PRIMARY KEY (customer_id)
);

Explanation

In the above example we created a table name as customer_info with different attributes with different constraints as shown in the above example. The final output of the show databases query we illustrate by using the following snapshot.

Now let’s create the before update trigger by using the create trigger statement as follows.

DELIMITER //
CREATE TRIGGER customer_info_before_update
BEFORE UPDATE
ON customer_info FOR EACH ROW
BEGIN
DECLARE User_name varchar(70);
-- Find username to execute the INSERT operation into table
SELECT USER() INTO User_name;
-- Insert record into audit table
INSERT INTO customer_info
( customer_id,
Customer_birthday_date,
updated_by)
VALUES
( NEW.customer_id,
SYSDATE(),
User_name );
END; //
DELIMITER ;

Explanation

In the above example we use a delimiter to create before the update trigger. The final output of the show databases query we illustrate by using the following snapshot.

Example #2 – The after update trigger

Example:

Now we have the same table that was already created in the previous example that is customer_info table.

Let’s create after update trigger by using the create trigger statement as follows.

DELIMITER //
CREATE TRIGGER customer_info_after_update
AFTER UPDATE
ON customer_info FOR EACH ROW
BEGIN
DECLARE User_name varchar(70);
-- Find username to execute the INSERT operation into table
SELECT USER() INTO User_name;
-- Insert record into audit table
INSERT INTO customer_info
( customer_id,
Customer_birthday_date,
updated_by)
VALUES
( NEW.customer_id,
SYSDATE(),
User_name );
END; //
DELIMITER ;

Explanation

In the above example we use a delimiter to create an after update trigger. The final output of the show databases query we illustrate by using the following snapshot.

3. The before delete trigger:

In this type trigger will be invoked before delete operation execution.

Example

In this type we also use an already created table that is customer_info.

Now create before delete trigger as follows.

DELIMITER //
CREATE TRIGGER customer_info_before_delete
BEFORE DELETE
ON customer_info FOR EACH ROW
BEGIN
DECLARE User_name varchar(70);
-- Find username to execute the INSERT operation into table
SELECT USER() INTO User_name;
-- Insert record into audit table
INSERT INTO customer_info
( customer_id,
delete_date,
delete_by)
VALUES
( OLD.customer_id,
SYSDATE(),
User_name );
END; //
DELIMITER ;

Explanation

In the above example we use a delimiter to create before the delete trigger. The final output of the show databases query we illustrate by using the following snapshot.

4. The after delete trigger

In this type trigger will be invoked after delete operation execution.

Example

Here also we use an already created table, now directly we create triggers as follows.

DELIMITER //
CREATE TRIGGER customer_info_after_delete
AFTER DELETE
ON customer_info FOR EACH ROW
BEGIN
DECLARE User_name varchar(70);
-- Find username to execute the INSERT operation into table
SELECT USER() INTO User_name;
-- Insert record into audit table
INSERT INTO customer_info
( customer_id,
delete_date,
delete_by)
VALUES
( OLD.customer_id,
SYSDATE(),
User_name );
END; //
DELIMITER ;

Explanation

In the above example we use a delimiter to create after delete trigger. The final output of the show databases query we illustrate by using the following snapshot.

5. The before insert trigger.

In this type trigger will be invoked before insert operation execution.

6. The after insert trigger.

In this type trigger will be invoked after insert operation execution.

In the mentioned last type of trigger we can implement similarly like above type of trigger.

Conclusion

We hope from this article you have understood about the MariaDB Trigger. From this article we have learned the basic syntax of MariaDB Trigger and we also see different examples of MariaDB Trigger. From this article we learned how and when we use MariaDB Trigger.

Recommended Articles

This is a guide to MariaDB trigger. Here we discuss the basic syntax of MariaDB Trigger along with the different examples. You may also have a look at the following articles to learn more –

1. MariaDB Commands
2. MariaDB UPDATE
3. MariaDB MaxScale
4. MariaDB Timezone