Menu
MySQL

Triggers in MySQL

A trigger in MySQL is a database object that is automatically executed, or "triggered," when specific changes occur in a table. Triggers allow you to enforce rules, automate processes, and maintain data integrity within the database by reacting to changes like INSERT, UPDATE, or DELETE.

 

 

1. Key Characteristics of Triggers

  • Automatic Execution: A trigger is automatically executed when the associated event (such as INSERT, UPDATE, or DELETE) occurs.
  • Table-Level: Triggers are associated with specific tables, not the entire database.
  • Timing: Triggers can be set to execute before or after a data modification event.
  • Event-Specific: A trigger can be created for the INSERT, UPDATE, or DELETE event.
  • Non-Transactional: Once a trigger is fired, its changes cannot be rolled back within the same transaction, so they must be carefully designed.

 

 

 

2. Types of Triggers in MySQL

MySQL supports two main types of triggers for each event (INSERT, UPDATE, or DELETE):

  • BEFORE Trigger: Executes before the triggering event is completed.
  • AFTER Trigger: Executes after the triggering event is completed.

Supported Events:

  • INSERT: Triggered when a new row is inserted into the table.
  • UPDATE: Triggered when a row in the table is updated.
  • DELETE: Triggered when a row is deleted from the table.

 

 

 

3. Syntax for Creating a Trigger

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
   -- SQL statements go here
END;
 
  • BEFORE/AFTER: Specifies when the trigger should fire (before or after the event).
  • INSERT/UPDATE/DELETE: The event that activates the trigger.
  • table_name: The table on which the trigger is created.
  • FOR EACH ROW: Indicates that the trigger will execute once for each row affected by the event.

 

 

 

4. Using Triggers

4.1 BEFORE INSERT Trigger

A BEFORE INSERT trigger is used to modify or validate data before it is inserted into the table. For example, you can use it to automatically set default values or validate the data being inserted.

Example:

Automatically set the created_at column to the current date if no value is provided.

DELIMITER //
CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
   IF NEW.created_at IS NULL THEN
       SET NEW.created_at = NOW();
   END IF;
END //
DELIMITER ;

 

In this example:

  • NEW refers to the new row being inserted.
  • The trigger checks if the created_at field is null, and if it is, it sets the field to the current timestamp.

 

4.2 AFTER INSERT Trigger

An AFTER INSERT trigger can be used to perform actions after a new row has been successfully inserted. For example, you can use it to log the insertion or update another table.

Example:

Automatically insert a record into a log table after an employee is added.

DELIMITER //
CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
   INSERT INTO employee_log (employee_id, log_message, log_time)
   VALUES (NEW.employee_id, 'New employee added', NOW());
END //
DELIMITER ;

 

In this example:

  • After a new employee is inserted, a log entry is created with the employee’s ID, a log message, and the current timestamp.

 

 

4.3 BEFORE UPDATE Trigger

A BEFORE UPDATE trigger is fired before a row is updated, and it can be used to validate or modify the updated values.

Example:

Automatically update the last_modified timestamp when a row is updated.

DELIMITER //
CREATE TRIGGER before_employee_update
BEFORE UPDATE ON employees
FOR EACH ROW
BEGIN
   SET NEW.last_modified = NOW();
END //
DELIMITER ;

 

In this example:

  • NEW refers to the updated version of the row, while OLD refers to the original version of the row before the update.
  • The trigger automatically updates the last_modified field to the current timestamp whenever an employee record is updated.

 

 

4.4 AFTER UPDATE Trigger

An AFTER UPDATE trigger can be used to take action after a row has been updated. This can be useful for logging changes or updating related tables.

Example:

Log the change when an employee’s salary is updated.

DELIMITER //
CREATE TRIGGER after_employee_salary_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
   IF OLD.salary <> NEW.salary THEN
       INSERT INTO salary_log (employee_id, old_salary, new_salary, change_time)
       VALUES (NEW.employee_id, OLD.salary, NEW.salary, NOW());
   END IF;
END //
DELIMITER ;

 

In this example:

  • The trigger checks if the salary value has changed.
  • If it has, the trigger inserts a log entry into the salary_log table with the old and new salary values and the time of the change.

 

 

 

5. Limitations of Triggers

  • Cannot Call Triggers Recursively: A trigger cannot call another trigger or itself recursively, which prevents unintended loops.
  • Limited Error Handling: Triggers don’t have robust error-handling capabilities, though you can use the SIGNAL statement to raise exceptions.
  • Cannot Change Tables Other Than the Triggered Table in BEFORE Triggers: In BEFORE triggers, you cannot change data in other tables.
  • Performance Overhead: If not used carefully, triggers can add performance overhead, especially in tables with frequent INSERT, UPDATE, or DELETE operations.

 

 

 

 

MySQL