EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login

MariaDB trigger

Home » Data Science » Data Science Tutorials » Database Management Tutorial » 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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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.

Popular Course in this category
Sale
SQL Training Program (7 Courses, 8+ Projects)7 Online Courses | 8 Hands-on Projects | 73+ Hours | Verifiable Certificate of Completion | Lifetime Access
4.5 (8,933 ratings)
Course Price

View Course

Related Courses
PL SQL Training (4 Courses, 2+ Projects)Oracle Training (14 Courses, 8+ Projects)

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

All in One Data Science Bundle (360+ Courses, 50+ projects)

360+ Online Courses

50+ projects

1500+ Hours

Verifiable Certificates

Lifetime Access

Learn More

0 Shares
Share
Tweet
Share
Primary Sidebar
Database Management 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? 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 List Databases
    • MariaDB Functions
    • MariaDB? TIMESTAMP
    • MariaDB create user
    • MariaDB add user
    • 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
  • DataBase Management
    • Text Data Mining
    • Roles of Database Management System in Industry
    • SQL Server Database Management Tools
    • Database administrator skills
    • Database Management Systems Advantages
    • Database Testing Interview Questions
    • Data Administrator
    • Database Administrator
    • Database Management Software
    • DataStage
    • Types of Database Models
    • Types of Database
    • Hierarchical Database Model
    • Relational Database
    • Relational Database Advantages
    • Operational Database
    • What is RDBMS?
    • What is DB2?
    • Data Masking Tools
    • Database Security
    • Data Replication
    • Bitmap Indexing
    • Second Normal Form
    • Third Normal Form
    • Fourth Normal Form
    • Data Definition Language
    • Data Manipulation Language
    • Data Control Language
    • Transaction Control Language
    • Conceptual Data Model
    • Entity-Relationship Model
    • Relational Database Model
    • Sequential File Organization
    • Checkpoint in DBMS
    • Teradata Create Table
    • Centralized Database
    • Data Storage in Database
    • Thomas write Rule
    • DBA Interview Questions
    • What is JDBC?
    • jdbc hive
    • Apriori Algorithm
    • JDBC Architecture
    • JDBC Interview Questions
    • Wildcard Characters
    • Distributed Database System
    • Multidimensional Database
  • PL/SQL
    • What is PL/SQL?
    • Careers in PL/SQL
    • PLSQL procedure
    • PL/SQL Exception
    • PL/SQL LIKE
    • PL/SQL Raise Exception
    • PLSQL rowtype
    • PLSQL? bind variables
    • PL/SQL Record
    • PL/SQL WITH
    • PL/SQL bulk collect
    • PL/SQL Block Structure
    • PL/SQL else if
    • PL/SQL nvl2
    • PL/SQL Package
    • PL/SQL exists
    • PL/SQL instr
    • PL/SQL listagg
    • PL/ SQL Formatter
    • PLSQLlength
    • PL/SQL Commands
    • PL/SQL Data Types
    • CASE statement in PL/SQL
    • PL/SQL IF Statement
    • Loops in PL/SQL
    • PL/SQL Add Column
    • For Loop in PLSQL
    • PL/SQL Cursor Loop
    • PLSQL Array
    • Cursors in PL/SQL
    • PL/SQL FOR Loop Cursor
    • PL/SQL Queries
    • PL/SQL SELECT INTO
    • PL/SQL TO_CHAR
    • PL/SQL UNION
    • PL/SQL NOT EQUAL
    • PL/SQL varray
    • PL/SQL Concatenate
    • PL/SQL UPDATE
    • PL/SQL TRIM
    • PL/SQL GROUP BY
    • PL/SQL GOTO
    • PL/SQL Date Functions
    • PL/ SQL having
    • PL/SQL to_DATE
    • PL/SQL NVL
    • PLSQL format date
    • PLSQL mod
    • PLSQL round
    • PL/SQL Boolean
    • PL/SQL exit
    • PL/SQL DECODE
    • PL/SQL ROWNUM
    • PLSQL?pivot
    • PLSQL string functions
    • PL/SQL Block
    • PL/SQL Function
    • PL/SQL Unwrapper
    • PL/SQL Table
    • PL/SQL ALTER TABLE
    • PLSQL execute immediate
    • Triggers in PL/SQL
    • PL/SQL Collections
    • PL/SQL stored procedure
    • PL/SQL Anonymous Block
    • PLSQL Interview Questions
  • TSQL Basic
    • TSQL
    • What is T-SQL
    • T-SQL Commands
    • T-SQL String Functions
    • TSQL Interview Questions
  • SQLite
    • What is SQLite
    • SQLite Commands
    • SQLite Data Types
    • SQLite COUNT
    • SQLite Boolean
    • SQLite autoincrement
    • SQLite select
    • SQLite? Bulk Insert
    • SQLite? add column
    • SQLite? concat
    • SQLite BETWEEN
    • SQLite group by
    • SQLite CASE
    • SQLite group_concat
    • SQLite array
    • SQLite? enum
    • SQLite sum
    • SQLite create table
    • SQLite Alter Table
    • SQLite Create Database
    • SQLite Delete
    • SQLite connection string
    • SQLite Database
    • SQLite Describe Table
    • SQLite Show Tables
    • SQLite exit
    • SQLite create index
    • SQLite foreign key
    • SQLite Stored Procedures
    • SQLite Extension
  • DB2
    • DB2? current date
    • DB2 purescale
    • DB2 backup
    • DB2 restore
    • DB2 C Express
    • DB2 Version
    • DB2? Architecture
    • DB2? Data Types
    • DB2? load
    • DB2? order by
    • DB2 date
    • DB2 NVL
    • DB2? update
    • DB2 warehouse
    • DB2 grant
    • DB2 database
    • DB2 VARCHAR
    • DB2? INSERT
    • DB2 LISTAGG
    • DB2 LIKE
    • DB2 TRUNCATE TABLE
    • DB2 LIST TABLES
    • DB2 between
    • DB2? current timestamp
    • DB2? length
    • DB2? bind
    • DB2 limit rows
    • DB2? export
    • DB2 with
    • DB2 Create Table
    • DB2 case statement
    • DB2 CAST
    • DB2 Functions
    • DB2 Date Functions
    • DB2? row_number
    • DB2 trim
    • DB2? Translate
    • DB2 UNION
    • DB2 timestamp
    • DB2? TIMESTAMPDIFF
    • DB2? replace
    • DB2 merge
    • DB2 COALESCE
    • DB2 ISNULL
    • DB2? explain
    • DB2 Join
    • DB2 alter column
    • DB2 rename column
    • DB2? Describe Table
    • DB2? rename table
    • DB2 List Databases
    • DB2 LUW
    • DB2 Query
    • DB2 GROUP BY
    • DB2 TO_DATE
    • View Serializability in DBMS
    • MariaDB Join
    • MariaDB JSON
    • MariaDB? show databases
    • Dataset Normalization
    • MariaDB Max Connections
    • jdbc connection
    • MariaDB GUI
  • DBMS
    • Introduction To DBMS
    • DBMS ER Diagram
    • What is DBMS?
    • DBMS join
    • DBMS Functions
    • Data Administrator in DBMS
    • DBMS Canonical Cover
    • DBMS Log-Based Recovery
    • DBMS Multivalued Dependency
    • Netezza Database
    • DBMS Concepts
    • DBMS Constraints
    • DBMS_Scheduler
    • B+ Tree in DBMS
    • DBMS_LOB
    • dbms entity
    • DBMS Foreign Key
    • DBMS Users
    • DBMS_Metadata.get_ddl
    • Relational Algebra in DBMS
    • DBMS Components
    • DBMS Features
    • DBMS Models
    • DBMS Relational Model
    • Hashing in DBMS
    • DBMS network model
    • Relationship in DBMS
    • ER Model in DBMS
    • Data Models in DBMS
    • Static Hashing in DBMS
    • Advantages of DBMS
    • dbms_output.put_line
    • DBMS Data Dictionary
    • dbms_xplan.display_cursor
    • Normal Forms in DBMS
    • DBMS helps achieve
    • DBMS 3 tier Architecture
    • Relational Calculus in DBMS
    • Serializability in DBMS
    • File Organization in DBMS
    • DBMS Transaction Processing
    • States of Transaction in DBMS
    • Functional Dependency in DBMS
    • Generalization in DBMS
    • Data Independence in DBMS
    • Lock Based Protocols in DBMS
    • Deadlock in DBMS
    • Integrity Constraints in DBMS
    • Concurrency Control in DBMS
    • Validation Based Protocol in DBMS
    • DBMS Locks
    • Normalization in DBMS
    • Transaction Property in DBMS
    • Specialization in DBMS
    • Aggregation in DBMS
    • Types of DBMS

Related Courses

SQL Certification Course

PL/SQL Certification Course

Oracle Certification Course

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
  • Database Management
  • Machine Learning
  • All Tutorials
Certification Courses
  • All Courses
  • Data Science Course - All in One Bundle
  • Machine Learning Course
  • Hadoop Certification Training
  • Cloud Computing Training Course
  • R Programming Course
  • AWS Training Course
  • SAS Training Course

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

EDUCBA
Free Data Science Course

Hadoop, Data Science, Statistics & 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
Free Data Science Course

Hadoop, Data Science, Statistics & 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.

Let’s Get Started

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

Loading . . .
Quiz
Question:

Answer:

Quiz Result
Total QuestionsCorrect AnswersWrong AnswersPercentage

Explore 1000+ varieties of Mock tests View more

EDUCBA Login

Forgot Password?

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

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.

Special Offer - SQL Certification Course Learn More