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 11:16:19