Triggers in MySQL

How to create Triggers in MySQL?

Triggers in MySQL are programs created on table which are invoked automatically when we perform any operation on that table like INSERT / UPDATE or DELETE.

In some cases in SQL, we need to perform extra operations on other tables before inserting/updating, or deleting from one table. For example, if we have a foreign key reference in the child table, we need to delete the entry from the parent table before deleting the entry from the table for that key. If we try to delete the entry from the parent table without deleting it from the child table’s entry then we will get the error for a foreign key like below:

ERROR 1451: 1451: Cannot delete or update a parent row: a foreign key constraint fails

Let’s see the following two tables: students and student_report where students have roll_num as the primary key and student_report has roll_number as a foreign-key reference.

We have the following data in the above two tables.

Let’s try to delete the data for roll_num 5 from the students’ table. We will get the error as shown in the below image.

The syntax for trigger

To Create a trigger

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE| DELETE }
ON schema.table_name FOR EACH ROW
trigger_body;

To delete a trigger:

DROP TRIGGER [IF EXISTS] schema_name.trigger_name;

Now we will write the trigger for DELETE. Before deleting from the students’ table we will delete the record from the student_report table as below.

Query :

DELIMITER $$
CREATE TRIGGER students_record_delete
      BEFORE DELETE
      ON students FOR EACH ROW
BEGIN
      delete from student_report where roll_num = old.roll_num;
END$$
DELIMITER ;

Once the trigger is created successfully we can try to delete from the students’ table once again as above we should not get any foreign key error since in the trigger before deleting from the parent table we are deleting from the child table.

If we check the data from both the tables we can see even if we are deleting only from the students’ table for roll number 4, student_report table data.