Triggers are special stored procedures in databases that are executed automatically when certain events occur, such as inserting, updating, or deleting records. In MySQL, triggers are powerful tools for preserving database integrity, performing data validations, and executing automated data management operations. In this post, we review how triggers work, their types, and best practices for using them.

What is a Trigger?

A trigger is a procedure that is executed automatically when a specified event occurs in a given table. The two main components of triggers are the operation type (INSERT, UPDATE, DELETE) and the timing (BEFORE or AFTER).

Types of Triggers

  1. INSERT Trigger: It runs when a new record is inserted into the table.
  2. UPDATE Trigger: It runs when a record is updated.
  3. DELETE Trigger: It runs when a record is deleted from the table.

According to timing, triggers can be:

  • BEFORE Trigger: It runs before the operation.
  • AFTER Trigger: It runs after the operation.

Creating a Trigger

To create triggers, we use the CREATE TRIGGER command. Below, we present the syntax for creating triggers and some examples.

Syntax

CREATE TRIGGER trigger_nev
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON tabla_nev
FOR EACH ROW
BEGIN
    -- Trigger test
END;

Examples

BEFORE INSERT Trigger

This trigger checks that the salary of the record to be inserted is not negative, and signals an error if the value is negative.

CREATE TRIGGER ellenoriz_fizetes
BEFORE INSERT ON alkalmazottak
FOR EACH ROW
BEGIN
    IF NEW.fizetes < 0 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'The salary cannot be negative!';
    END IF;
END;

AFTER INSERT Trigger

This trigger logs the insertion of new orders into a separate log table.

CREATE TABLE rendeles_naplo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    rendeles_id INT,
    datum TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER naplozza_uj_rendelest
AFTER INSERT ON rendelesek
FOR EACH ROW
BEGIN
    INSERT INTO rendeles_naplo (rendeles_id) VALUES (NEW.id);
END;

BEFORE UPDATE Trigger

This trigger checks that an employee’s salary cannot decrease below a certain level during an update.

CREATE TRIGGER ellenoriz_fizetes_update
BEFORE UPDATE ON alkalmazottak
FOR EACH ROW
BEGIN
    IF NEW.fizetes < OLD.fizetes * 0.9 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary cannot decrease by more than 10%!';
    END IF;
END;

AFTER DELETE Trigger

This trigger logs the deleted records into a deletion log table.

CREATE TABLE torlesi_naplo (
    id INT AUTO_INCREMENT PRIMARY KEY,
    felhasznalo_id INT,
    torles_datuma TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

CREATE TRIGGER naplozza_torleseket
AFTER DELETE ON felhasznalok
FOR EACH ROW
BEGIN
    INSERT INTO torlesi_naplo (felhasznalo_id) VALUES (OLD.id);
END;

Best Practices for Using Triggers

  1. Minimize Trigger Logic: To minimize trigger execution time, keep trigger logic simple and fast.
  2. Avoid Long Queries: Avoid running long queries in triggers to minimize locking issues.
  3. Use Stored Procedures Instead of Triggers When Possible: If the trigger logic is complex, consider using stored procedures.
  4. Document Triggers: Document the operation and purpose of triggers in detail to facilitate future maintenance and troubleshooting.