Skip to main content

Creating Triggers in MySQL

Introduction to MySQL Triggers​

A trigger is a stored program in MySQL that is automatically invoked or performed when a particular operation such as INSERT, UPDATE or DELETE is performed on a table. They are used to maintain the integrity of the data in the database. MySQL triggers are very useful when you want certain operations to be performed automatically on the database.

Understanding MySQL Triggers​

MySQL triggers handle different types of operations. They are:

  1. BEFORE INSERT: This trigger is invoked before an INSERT operation on a table.
  2. AFTER INSERT: This trigger is invoked after an INSERT operation on a table.
  3. BEFORE UPDATE: This trigger is invoked before an UPDATE operation on a table.
  4. AFTER UPDATE: This trigger is invoked after an UPDATE operation on a table.
  5. BEFORE DELETE: This trigger is invoked before a DELETE operation on a table.
  6. AFTER DELETE: This trigger is invoked after a DELETE operation on a table.

Creating Triggers in MySQL​

To create a trigger in MySQL, we use the CREATE TRIGGER command. The general syntax is:

CREATE TRIGGER trigger_name 
trigger_time trigger_event 
ON table_name FOR EACH ROW 
trigger_body;

Here,

  • trigger_name is the name of the trigger that you wish to create.
  • trigger_time can be either BEFORE or AFTER depending on when you want the trigger to be invoked.
  • trigger_event can be INSERT, UPDATE, or DELETE.
  • table_name is the name of the table that the trigger is associated with.
  • trigger_body is the statement to be executed when the trigger is invoked.

Let's look at an example where we create a BEFORE INSERT trigger.

CREATE TRIGGER before_employee_insert 
BEFORE INSERT 
ON employees FOR EACH ROW 
SET NEW.creation_date = NOW();

In this example, before_employee_insert is the name of the trigger. It is a BEFORE INSERT trigger associated with the employees table. The trigger sets the creation_date of a new record to the current date and time whenever a new record is inserted into the employees table.

Conclusion​

MySQL triggers are a powerful feature that allow you to automate operations in the database. They help in maintaining data integrity and can make your database more efficient. Remember, triggers should be used judiciously as they can sometimes lead to unexpected results if not properly managed. Always test your triggers thoroughly before deploying them into a production database. Happy learning!