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.

Arjun Thakur

Published on 22-Feb-2018 06:46:19

• Related Questions & Answers
• How can we destroy a trigger?
• How can we create and use ENUM columns in MySQL?
• How can we create multiple MySQL triggers for the same trigger event and action time?
• How can we create MySQL views?
• How can we use MySQL SUM() function?
• How can we create a MySQL view with a subquery?
• Can we use LIKE and OR together in MySql?
• Can we use {} while creating a MySQL table?
• Can we use semicolon as a MySQL DEMILITER?
• Can we use str_replace in MySQL?
• 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?
• What are MySQL stored functions and how can we create them?