EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 600+ Courses All in One Bundle
  • Login
Home Software Development Software Development Tutorials MariaDB Tutorial MariaDB trigger
Secondary Sidebar
MariaDB Tutorial
  • MariaDB
    • MariaDB Versions
    • MariaDB? list users
    • MariaDB Commands
    • MariaDB odbc
    • MariaDB Workbench
    • MariaDB for windows
    • MariaDB Server
    • MariaDB? Data Types
    • MariaDB? boolean
    • MariaDB phpMyAdmin
    • MariaDB Mysqldump
    • MariaDB Java Connector
    • MariaDB insert
    • MariaDB UPDATE
    • MariaDB? rename column
    • MariaDB AUTO_INCREMENT
    • MariaDB Timezone
    • MariaDB GROUP_CONCAT
    • MariaDB wait_timeout
    • MariaDB MaxScale
    • MariaDB? with
    • MariaDB GUI
    • MariaDB? create?table
    • MariaDB? SHOW TABLES
    • MariaDB alter table
    • MariaDB List Tables
    • MariaDB JSON Functions
    • MariaDB Foreign Key
    • MariaDB? trigger
    • MariaDB Grant All Privileges
    • MariaDB Select Database
    • MariaDB? create database
    • MariaDB Delete Database
    • MariaDB Join
    • MariaDB JSON
    • MariaDB? show databases
    • MariaDB List Databases
    • MariaDB Functions
    • MariaDB? TIMESTAMP
    • MariaDB create user
    • MariaDB add user
    • MariaDB Max Connections
    • MariaDB show users
    • MariaDB Delete User
    • MariaDB? change user password
    • MariaDB? change root password
    • MariaDB reset root password
    • MariaDB IF
    • MariaDB bind-address
    • MariaDB Transaction
    • MariaDB Cluster
    • MariaDB Logs
    • MariaDB Encryption
    • MariaDB? backup
    • MariaDB Replication
    • MariaDB max_allowed_packet
    • MariaDB? performance tuning
    • MariaDB export database
    • MariaDB? import SQL

MariaDB trigger

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

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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.

MariaDB trigger output 1

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.

MariaDB trigger output 2

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.

MariaDB trigger output 3

  1. 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.

output 4

  1. 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.

output 5

  1. The before insert trigger.

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

  1. 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
Popular Course in this category
SQL Training Program (7 Courses, 8+ Projects)
  7 Online Courses |  8 Hands-on Projects |  73+ Hours |  Verifiable Certificate of Completion
4.5
Price

View Course
0 Shares
Share
Tweet
Share
Primary Sidebar
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • Corporate Training
  • Certificate from Top Institutions
  • Contact Us
  • Verifiable Certificate
  • Reviews
  • Terms and Conditions
  • Privacy Policy
  •  
Apps
  • iPhone & iPad
  • Android
Resources
  • Free Courses
  • Java Tutorials
  • Python Tutorials
  • All Tutorials
Certification Courses
  • All Courses
  • Software Development Course - All in One Bundle
  • Become a Python Developer
  • Java Course
  • Become a Selenium Automation Tester
  • Become an IoT Developer
  • ASP.NET Course
  • VB.NET Course
  • PHP Course

ISO 10004:2018 & ISO 9001:2015 Certified

© 2022 - EDUCBA. ALL RIGHTS RESERVED. THE CERTIFICATION NAMES ARE THE TRADEMARKS OF THEIR RESPECTIVE OWNERS.

EDUCBA
Free Software Development Course

C# Programming, Conditional Constructs, Loops, Arrays, OOPS Concept

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA Login

Forgot Password?

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA
Free Software Development Course

Web development, programming languages, Software testing & others

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

EDUCBA

*Please provide your correct email id. Login details for this Free course will be emailed to you

By signing up, you agree to our Terms of Use and Privacy Policy.

Let’s Get Started

By signing up, you agree to our Terms of Use and Privacy Policy.

This website or its third-party tools use cookies, which are necessary to its functioning and required to achieve the purposes illustrated in the cookie policy. By closing this banner, scrolling this page, clicking a link or continuing to browse otherwise, you agree to our Privacy Policy

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more