- Trending Categories
Data Structure
Networking
RDBMS
Operating System
Java
iOS
HTML
CSS
Android
Python
C Programming
C++
C#
MongoDB
MySQL
Javascript
PHP
Physics
Chemistry
Biology
Mathematics
English
Economics
Psychology
Social Studies
Fashion Studies
Legal Studies
- Selected Reading
- UPSC IAS Exams Notes
- Developer's Best Practices
- Questions and Answers
- Effective Resume Writing
- HR Interview Questions
- Computer Glossary
- Who is Who
How can we create and use a MySQL trigger?
For creating a new trigger, we need to use the CREATE TRIGGER statement. Its syntax is as follows −
CREATE TRIGGER trigger_name trigger_time trigger_event ON table_name FOR EACH ROW BEGIN ... END;
Here,
- Trigger_name is the name of the trigger which must be put after the CREATE TRIGGER statement. The naming convention for trigger_name can be like [trigger time]_[table name]_[trigger event]. For example, before_student_update or after_student_insert can be the name of the trigger.
- Trigger_time is the time of trigger activation and it can be BEFORE or AFTER. We must have to specify the activation time while defining a trigger. We must use BEFORE if we want to process action prior to the change made on the table and AFTER if we want to process action post to the change made on the table.
- Trigger_event can be INSERT, UPDATE, or DELETE. This event causes the trigger to be invoked. A trigger only can be invoked by one event. To define a trigger that is invoked by multiple events, we have to define multiple triggers, one for each event.
- Table_name is the name of the table. Actually, a trigger is always associated with a specific table. Without a table, a trigger would not exist hence we have to specify the table name after the ‘ON’ keyword.
- BEGIN…END is the block in which we will define the logic for the trigger.
Example
Suppose we want to apply trigger on the table Student_age which is created as follows −
mysql> Create table Student_age(age INT, Name Varchar(35)); Query OK, 0 rows affected (0.80 sec)
Now, the following trigger will automatically insert the age = 0 if someone tries to insert age < 0.
mysql> DELIMITER // mysql> Create Trigger before_inser_studentage BEFORE INSERT ON student_age FOR EACH ROW BEGIN IF NEW.age < 0 THEN SET NEW.age = 0; END IF; END // Query OK, 0 rows affected (0.30 sec)
Now, for invoking this trigger, we can use the following statements −
mysql> INSERT INTO Student_age(age, Name) values(30, 'Rahul'); Query OK, 1 row affected (0.14 sec) mysql> INSERT INTO Student_age(age, Name) values(-10, 'Harshit'); Query OK, 1 row affected (0.11 sec) mysql> Select * from Student_age; +------+---------+ | age | Name | +------+---------+ | 30 | Rahul | | 0 | Harshit | +------+---------+ 2 rows in set (0.00 sec)
The above result set shows that on inserting the negative value in the table will lead to insert 0 by a trigger.
The above was the example of a trigger with trigger_event as INSERT and trigger_time are BEFORE.
- Related Articles
- How can we create multiple MySQL triggers for the same trigger event and action time?
- How can we create and use ENUM columns in MySQL?
- How can we destroy a trigger?
- How can we create MySQL views?
- How can we use MySQL SUM() function?
- How can we create a MySQL view with a subquery?
- How can we create a MySQL view with LEFT JOIN?
- How can we create a MySQL view with INNER JOIN?
- How can we create a MySQL view with RIGHT JOIN?
- How can we use a MySQL subquery with INSERT statement?
- How can we use a MySQL subquery with FROM clause?
- How can we use prepared statements in MySQL?
- How can we use nested transactions in MySQL?
- How can we create MySQL stored procedures without ‘BEGIN’ and ‘END’?
- What are MySQL stored functions and how can we create them?

Advertisements