EDUCBA

EDUCBA

MENUMENU
  • Blog
  • Free Courses
  • All Courses
  • All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Trigger

MySQL Trigger

Aanchal Sharma
Article byAanchal Sharma
Priya Pedamkar
Reviewed byPriya Pedamkar

Updated May 10, 2023

MySQL Trigger

Introduction to MySQL Trigger

A Trigger in MySQL is a special kind of stored operation that invokes automatically when an event occurs in the database. It is a database object related to a table in the database and becomes active when a defined MySQL statement is initiated on the table. These DML (Data Manipulation Language) execution operations can be INSERT, DELETE, UPDATE, and triggers can be called before or after these events.

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

You can activate triggers when inserting a row into a table or modifying any of its columns. Typically, you design triggers to execute whenever you make any changes to the data within a table. Triggers are simply SQL code to run before or just after any DML action events on a particular table in a database.

MySQL has supported Triggers since version 5.0.2. The DBMS stores and manages triggers as they are event-driven SQL procedures.

Syntax

To create a new trigger in MySQL, we use the statement CREATE TRIGGER:

CREATE
[DEFINER = user]
TRIGGER trigger_name
trigger_time trigger_event
ON tbl_name FOR EACH ROW
trigger_body
trigger_time: { BEFORE | AFTER }
trigger_event: { INSERT | UPDATE | DELETE }

Parameters

  • DEFINER clause: Identifies the MySQL account used for access at trigger initiation time.
  • trigger_name: The name of all triggers should be unique inside a schema.
  • trigger_time: Defines the trigger action time, which can be either before or after any row affected.
  • trigger_event: Specifies the type of operation to activate the trigger.
  • tbl_name: The table name should be of a permanent table, not a temporary or a view table to associate a trigger.
  • trigger_body: When the trigger is fired, it displays a statement. The compound statement constructs BEGIN … END can be used to complete many statements.

This syntax is used to drop a trigger.

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name

The schema name is optional, which drops the trigger from the default schema if omitted. To execute the DROP TRIGGER statement, the user needs to have the privilege of the trigger for the associated table. In addition, the use of IF EXISTS checks for the existence of the trigger before attempting to drop it, thus preventing any errors from occurring.

Types of Trigger in MYSQL

The SQL standard defines two types of triggers:

  • Row-Level Trigger: The trigger executes itself for each row of a table that insertion, update, or deletion actions affect. If you insert, update, or delete 50 rows in a table, the trigger will automatically invoke itself 50 times.
  • Statement-Level Trigger: This trigger invokes only once for a transaction regardless of the number of rows inserted, updated, or deleted.

MySQL supports Row-Level Trigger but not Statement-Level Trigger. So, the following are various types of triggers in MySQL:

1. Data Manipulation Language (DML) Triggers

DML queries like INSERT, UPDATE, or DELETE execute triggers on a table or view.

Handling MySQL Triggers:

BEFORE INSERT trigger: 

CREATE TRIGGER trigger_name
BEFORE INSERT
ON table_name FOR EACH ROW
trigger_body;

AFTER INSERT trigger: Triggers after inserting data into a table.

CREATE TRIGGER trigger_name
AFTER INSERT
ON table_name FOR EACH ROW
trigger_body

BEFORE UPDATE trigger: When you write an update statement, you validate the data before executing the update.

CREATE TRIGGER trigger_name
BEFORE UPDATE
ON table_name FOR EACH ROW
trigger_body

AFTER UPDATE trigger: The trigger is invoked after implementing the update statement.

CREATE TRIGGER trigger_name
AFTER UPDATE
ON table_name FOR EACH ROW
trigger_body

BEFORE DELETE trigger: The instruction specifies triggering before executing any delete statement.

CREATE TRIGGER trigger_name
BEFORE DELETE
ON table_name FOR EACH ROW
trigger_body

AFTER DELETE trigger: The instruction is to specify that the trigger should execute after executing any delete statement.

CREATE TRIGGER trigger_name
AFTER DELETE
ON table_name FOR EACH ROW
trigger_body;

Create multiple triggers for a table with the same trigger event and time: A trigger invoked before or after a current trigger with the same event and action time.

DELIMITER $$
CREATE TRIGGER trigger_name
{BEFORE|AFTER}{INSERT|UPDATE|DELETE}
ON table_name FOR EACH ROW
{FOLLOWS|PRECEDES} existing_trigger_name
BEGIN
-- statements
END$$
DELIMITER ;

Show triggers: Show all triggers in a particular database or table using FROM, IN keyword, or any pattern matching clause.

SHOW TRIGGERS
[{FROM | IN} databasename]
[LIKE 'pattern' | WHERE searchcondition];

2. Data Definition Language (DDL) Triggers

When you execute DDL operations such as CREATE, DROP, ALTER, DENY, GRANT, UPDATE STATISTICS, and REVOKE statements, the system invokes these triggers.

3. LOGON Triggers

These triggers automatically activate in response to a LOGON event. Triggers invoke during the process of establishing a user session after successful authentication. However, if the authentication process fails, the trigger will not be executed.

4. CLR Triggers

The SQL CLR builds the trigger, which can be helpful if it requires heavy computation or refers to an object outside of SQL. The supported .NET CLR languages, like C#, VB.NET, etc., can be used to write DML and DDL triggers.

For example, let’s add a trigger to the Products table below:

MySQL Trigger - 1

The trigger applied will insert Price = 10 automatically when we try to insert Price < 10.

DELIMITER //
Create Trigger before_inser_product_price BEFORE INSERT ON Products FOR EACH ROW
BEGIN
IF NEW.Price < 10 THEN SET NEW.Price = 10;
END IF;
END //

Now, for testing the trigger, we can execute the following statements:

INSERT INTO Products(ProductName, SupplierID, CategoryID, Unit, Price) values('Teatime
Chocolate Biscuits',8,3,'10 boxes x 12 pieces',9.20);

And then we display the product list:

Select * from Products;

Output:

MySQL Trigger - 2

We can see that when we insert a product with a price value less than 10 in the table, the trigger will automatically insert 10 to its price, i.e.

7          Teatime Chocolate Biscuits     8          3          10 boxes x 12 pieces   10.00

This is an example of a trigger with a combination of the trigger_event INSERT and the trigger _time BEFORE.

Conclusion

Yes, here we can say that a trigger is a small amount of relative energy that releases a large amount. However, a trigger may increase the server workload but helps in the field of Data integrity with referential and check constraints. Also, it can handle any error on the database layers. The Trigger in MYSQL is also helpful in running a scheduled task automatically.

Recommended Articles

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

  1. PL/SQL Data Types
  2. Complete Guide to Triggers in SQL
  3. MySQL IN Operator | Examples
  4. Guide to MySQL Timestamp
  5. IF Statement in MySQL
C++ PROGRAMMING Course Bundle - 9 Courses in 1 | 5 Mock Tests
37+ Hour of HD Videos
9 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
ASP.NET Course Bundle - 28 Courses in 1 | 5 Mock Tests
123+ Hours of HD Videos
28 Courses
5 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SQL Course Bundle - 51 Courses in 1 | 6 Mock Tests
204+ Hours of HD Videos
51 Courses
6 Mock Tests & Quizzes
Verifiable Certificate of Completion
Lifetime Access
4.5
SOFTWARE TESTING Course Bundle - 13 Courses in 1
53+ Hour of HD Videos
13 Courses
Verifiable Certificate of Completion
Lifetime Access
4.5
Primary Sidebar
Popular Course in this category
MYSQL Course Bundle - 18 Courses in 1 | 3 Mock Tests
 93+ Hour of HD Videos
18 Courses
3 Mock Tests & Quizzes
  Verifiable Certificate of Completion
  Lifetime Access
4.5
Price

View Course
Footer
About Us
  • Blog
  • Who is EDUCBA?
  • Sign Up
  • Live Classes
  • 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

ISO 10004:2018 & ISO 9001:2015 Certified

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

Let’s Get Started

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

EDUCBA
Free Data Science Course

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

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