EDUCBA

EDUCBA

MENUMENU
  • Explore
    • Lifetime Membership
    • All in One Bundles
    • Fresh Entries
    • Finance
    • Data Science
    • Programming and Dev
    • Excel
    • Marketing
    • HR
    • PDP
    • VFX and Design
    • Project Management
    • Exam Prep
    • All Courses
  • Blog
  • Enterprise
  • Free Courses
  • Log in
  • Sign up
Home Software Development Software Development Tutorials MariaDB Tutorial MariaDB trigger

MariaDB trigger

Updated April 12, 2023

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.

ADVERTISEMENT
Popular Course in this category
MARIADB Course Bundle - 5 Courses in 1

Start Your Free Software Development Course

Web development, programming languages, Software testing & others

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.

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

We hope that this EDUCBA information on “MariaDB trigger” was beneficial to you. You can view EDUCBA’s recommended articles for more information.

  1. MariaDB Commands
  2. MariaDB UPDATE
  3. MariaDB MaxScale
  4. MariaDB Timezone
ADVERTISEMENT
MICROSOFT POWER BI Course Bundle - 8 Courses in 1
34+ Hours of HD Videos
8 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
CYBER SECURITY & ETHICAL HACKING Course Bundle - 13 Courses in 1 | 3 Mock Tests
64+ Hours of HD Videos
13 Courses
3 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
MICROSOFT AZURE Course Bundle - 15 Courses in 1 | 12 Mock Tests
63+ Hour of HD Videos
15 Courses
12 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ADVERTISEMENT
KALI LINUX Course Bundle - 6 Courses in 1
20+ Hours of HD Videos
6 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Footer
Follow us!
  • EDUCBA FacebookEDUCBA TwitterEDUCBA LinkedINEDUCBA Instagram
  • EDUCBA YoutubeEDUCBA CourseraEDUCBA Udemy
APPS
EDUCBA Android AppEDUCBA iOS App
Blog
  • Blog
  • Free Tutorials
  • About us
  • Contact us
  • Log in
  • Blog as Guest
Courses
  • Enterprise Solutions
  • Free Courses
  • Explore Programs
  • All Courses
  • All in One Bundles
  • Sign up
Email
  • [email protected]

ISO 10004:2018 & ISO 9001:2015 Certified

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

EDUCBA

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

Let’s Get Started

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

By continuing above step, you agree to our Terms of Use and Privacy Policy.
*Please provide your correct email id. Login details for this Free course will be emailed to you

EDUCBA

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

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

Forgot Password?

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

🚀 Cyber Monday Reloaded Price Drop! All in One Universal Bundle (3700+ Courses) @ 🎁 90% OFF - Ends in ENROLL NOW