EDUCBA

EDUCBA

MENUMENU
  • Free Tutorials
  • Free Courses
  • Certification Courses
  • 360+ Courses All in One Bundle
  • Login
Home Data Science Data Science Tutorials MySQL Tutorial MySQL Trigger
Secondary Sidebar
MySQL Tutorial
  • Functions
    • MySQL Function
    • MySQL Aggregate Function
    • MySQL String functions
    • MySQL Date Functions
    • MySQL Window Functions
    • MySQL Math Functions
    • MySQL Boolean
    • Cursor in MySQL
    • Condition in MySQL
    • MySQL BETWEEN
    • Insert in MySQL
    • MySQL IFNULL()
    • MySQL TIMESTAMPDIFF()
    • MySQL COALESCE()
    • MySQL count()
    • MIN() in MySQL
    • MySQL Numeric
    • MySQL field()
    • MySQL FIND_IN_SET()
    • MySQL avg()
    • MySQL MAX() Function
    • MySQL BIN()
    • MySQL Concat
    • MySQL DECODE()
    • MySQL REGEXP_REPLACE()
    • MySQL Asynchronous
    • MySQL innodb_buffer_pool_size
    • MySQL key_buffer_size
    • MySQL TRUNCATE()
    • MySQL ROW_NUMBER()
    • NOT in MySQL
    • MySQL IN Operator
    • LIKE in MySQL
    • ANY in MySQL
    • MySQL NOT IN
    • MySQL CHECK Constraint
    • MySQL DISTINCT
    • MySQL ALL
    • MySQL Union
    • MySQL UNION ALL
    • MySQL EXISTS
    • MySQL ON DELETE CASCADE
    • MySQL REGEXP
    • MySQL Index
    • MySQL Add Index
    • MySQL REINDEX
    • MySQL UNIQUE INDEX
    • MySQL Clustered Index
    • MySQL? InnoDB Cluster
    • Table in MySQL
    • ALTER TABLE MySQL
    • MySQL Temporary Table
    • MySQL Clone Table
    • MySQL Repair Table
    • MySQL Lock Table
    • MySQL Optimize Table
    • TRUNCATE TABLE MySQL
    • MySQL Table Dump
    • MySQL Update Set
    • MySQL ALTER TABLE Add Column
    • MySQL RANK()
    • MySQL CTE
    • MySQL LAG()
    • MySQL GROUP_CONCAT()
    • MySQL EXTRACT()
    • MySQL REPLACE
    • MySQL AUTO_INCREMENT
    • MySQL SYSDATE()
    • MySQL NULLIF()
    • MySQL Substring
    • MySQL SUBSTRING_INDEX()
    • MySQL LOWERCASE
    • MySQL Row
    • MySQL NOW
    • MySQL CEIL
    • MySQL Alias
    • MySQL Trigger
    • MySQL SHOW Triggers
    • MySQL UPDATE Trigger
    • MySQL DELETE Trigger
    • MySQL AFTER UPDATE Trigger
    • MySQL Stored Procedure
    • ROLLUP in MySQL
    • MySQL? INSTR()
    • MySQL Subquery
    • MySQL Timestamp
    • MySQL? Hour()
    • MySQL MOD()
    • MySQL DATE_FORMAT()
    • ALTER Column in MySQL
    • MySQL Rename Column
    • MySQL Interval
    • MySQL CURDATE
    • MySQL BIT
    • MySQL Binlog
    • MySQL Average
    • MySQL TEXT
    • MySQL SHOW
    • MySQL Offset
    • MySQL Timezone
    • mysql_real_escape_string
    • MySQL Datetime
    • MySQL DATE_SUB()
    • MySQL FULLTEXT
    • MySQL DATE_ADD()
    • MySQL sum()
    • MySQL Merge
    • MySQL BigInt
    • MySQL ROUND
    • MySQL VARCHAR
    • MySQL Decimal
    • MySQL Limit
    • MySQL today()
    • MySQL WEEKDAY
    • MySQL Split
    • MySQL Create Function
    • MySQL BLOB
    • MySQL encode()
    • MySQL Primary Key
    • MySQL Foreign Key
    • Unique Key in MySQL
    • MySQL Drop Foreign Key
    • MySQL DROP TRIGGER
    • MYSQL Database
    • Delete Database MySQL
    • MySQL Root
    • MySQL Root Password
    • MySQL Client
    • MySQL Users
    • MySQL?User Permissions
    • MySQL add user
    • MySQL List User
    • MySQL Show Users
    • MySQL User Password
    • MySQL?Cardinality
    • MySQL Workbench
    • MySQL Backup
    • MySQL REVOKE
    • MySQL Dump
    • MySQL Cluster
    • MySQL Partitioning
    • MySQL Full Text Search
    • MySQL Admin Tool
    • MySQL Export Database
    • MySQL Export to CSV
  • Basic
    • MySQL floor
    • MySQL DESCRIBE table
    • MySQL encryption
    • Introduction to MySQL
    • Is SQL Server a Database?
    • What is MySQL
    • Is MySQL Programming Language
    • MySQL Server
    • MySQL AB
    • MySQL Community Server
    • How To Install MySQL
    • MySQL Versions
    • MySQL OpenSource
    • MySQL GUI Tools
    • MySQL Grant
    • MySQL Error 1064
    • MySQL Drop Database
    • MySQL not equal
    • MySQL SELECT INTO Variable
    • MySQL Commands
    • MySQL Operators
    • What is MySQL Schema
    • Wildcards in MySQL
    • MySQL Constraints
    • MySQL Administration
    • MySQL Data Type
    • MYSQL COMMIT
    • MySQL FORMAT
    • Timestamp to Date in MySQL
    • MySQL DATEDIFF
    • MySQL?Incremental Backup
    • MySQL JSON Data Type
    • MySQL ENUM
    • MySQL Default Port
    • Cheat Sheet MySQL
  • Queries
    • MySQL Queries
    • MySQL Query Commands
    • SELECT in MySQL
    • MySQL INSERT IGNORE
    • MySQL having
    • ORDER BY in MySQL
    • MySQL Cheat Sheet
    • MySQL ORDER BY Random
    • MySQL ORDER BY DESC
    • MySQL GROUP BY
    • MySQL GROUP BY Count
    • MySQL GROUP BY month
    • MySQL WHERE Clause
    • MySQL WITH
    • MySQL FETCH
    • MySQL DDL
    • MySQL DML
    • MySQL WHERE IN Array
    • MySQL Fetch Array
    • MySQL ISNULL
    • MySQL Index Types
    • Mysql? Export Schema
    • Amazon RDS for MySQL
    • MySQL greatest
  • Database
    • What is Data Modeling
    • What is Data Processing
    • DBMS Architecture
    • DBMS Keys
    • Careers in Database Administration
    • What is MySQL Database
    • MySQL Relational Database
    • How to Connect Database to MySQL
    • MySQL Database Repair
    • RDBMS Interview Questions
    • DBMS Interview Questions
  • Joins
    • Joins in MySQL
    • MySQL Outer Join
    • Left Outer Join in MySQL
    • MySQL Self Join
    • Natural Join in MySQL
    • MySQL DELETE JOIN
    • MySQL Update Join
    • MySQL Cross Join
  • Advanced
    • MySQL Formatter
    • MySQL TINYINT
    • MySQL Grant All Privileges
    • MySQL DROP TABLE
    • MySQL rename database
    • MySQL Flush Privileges
    • MySQL super Privilege
    • MySQL Character Set
    • MySQL Log File
    • MySQL Flush Log
    • Grant Privileges MySQL
    • MySQL WHILE LOOP
    • IF Statement in MySQL
    • MySQL CASE Statement
    • MySQL IF Function
    • MySQL IF EXISTS
    • MySQL UUID
    • Views in MySQL
    • MySQL Replication
    • MySQL Partition
    • Toad for MySQL
    • Navicat for MySQL
    • MySQL AES_Encrypt
    • MySQL Performance Tuning
    • MySQL Transaction
    • MySQL? sort_buffer_size
    • MySQL? Sync
    • MySQL? Query Cache
    • MySQL Collation
    • MySQL ODBC Driver
    • MySQL Partitioning
    • MySQL InnoDB
    • MySQL Float vs Decimal
    • MySQL Union vs Union All
  • Interview Questions
    • MySQL Interview Questions

Related Courses

MS SQL Certification Courses

Oracle Certification Courses

PL/SQL Certification Courses

MySQL Trigger

By Aanchal SharmaAanchal Sharma

MySQL Trigger

Introduction to MySQL Trigger

A Trigger in MySQL is a special kind of stored operation that gets invoked automatically when an event has occurred in the database. It is a database object which is 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.

For example when a row is inserted to a table or when any columns are modified, a trigger can be fired. Mostly, triggers can are made to run whenever any alterations are done to the data of a table. Triggers are simply a 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. So, triggers are stored and managed by DBMS as they event-driven SQL procedures.

Syntax

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

Start Your Free Data Science Course

Hadoop, Data Science, Statistics & others

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 that is 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: It is used to display a statement when the trigger is fired. The compound statement construct 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 if omitted; drops the trigger from the default schema. The DROP TRIGGER statement needs the privilege of the trigger for the table that is associated with the trigger. IF EXISTS is used to check if a trigger exists or not so that it prevents any error to have occurred.

Types of Trigger in MYSQL

Two types of triggers are defined by the SQL standard:

  • Row-Level Trigger: It is executed when each row is affected by insertion, updation and deletion actions on a table. Like if you have a table and 50 rows are inserted, updated or deleted, then automatically the trigger is also invoked for 50 times.
  • Statement-Level Trigger: This trigger is invoked only once for a transaction regardless of the number of rows inserted, updated, or deleted.

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

1. Data Manipulation Language (DML) Triggers

These triggers are called when any DML queries like INSERT; UPDATE OR DELETE is executed on a Table or View.

Handling MySQL Triggers:

BEFORE INSERT trigger: Specifies to keep a summary table from a table before insertion is done.

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 an update statement is written, it validates data before the update is executed.

CREATE TRIGGER trigger_name
BEFORE UPDATE
ON table_name FOR EACH ROW
trigger_body

AFTER UPDATE trigger: Trigger is invoked after the update statement is implemented.

CREATE TRIGGER trigger_name
AFTER UPDATE
ON table_name FOR EACH ROW
trigger_body

BEFORE DELETE trigger: Specifies trigger before any delete statement is executed.

CREATE TRIGGER trigger_name
BEFORE DELETE
ON table_name FOR EACH ROW
trigger_body

AFTER DELETE trigger: Specifies trigger after any delete statement is executed.

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

Create multiple triggers for a table that have the same trigger event and time: A trigger that is invoked before or after a current trigger having 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

These triggers are invoked when any DDL operations like CREATE, DROP, ALTER, DENY, GRANT, UPDATE STATISTICS and REVOKE statements are called.

3. LOGON Triggers

These triggers get fired automatically to respond to a LOGON event. Suppose before a user session is established, the trigger is invoked after successful authentication only but if authentication is failed then the trigger will not be executed.

4. CLR Triggers

These Triggers are useful if a heavy computation is required in the trigger or may refer to object outside SQL. The trigger is built on the SQL CLR. The supported .NET CLR languages like C#, VB.NET, etc. can be used to write DML and DDL triggers .

For example, lets’ add a trigger on the Products table below:

MySQL Trigger - 1

Here, 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 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 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 which releases a large amount of energy. However, a trigger may increase the server workload but it helps in the field of Data integrity with referential constraints and check constraints. Also, it is capable to handle any sort of error on the database layers. The Trigger in MYSQL is also useful to run a scheduled task automatically.

Recommended Articles

This is a guide to MySQL Trigger. Here we discuss the introduction to Trigger in MYSQL along with the types of triggers and respective Syntax, Parameters & examples. You can also go through our other related articles to learn more –

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

Related Courses

MS SQL Training (16 Courses, 11+ Projects)4.9
Oracle Training (14 Courses, 8+ Projects)4.8
PL SQL Training (4 Courses, 2+ Projects)4.7
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
  • 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

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

EDUCBA
Free Data Science Course

SPSS, Data visualization with Python, Matplotlib Library, Seaborn Package

*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 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

*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