How To Use Triggers in MySQL?

What are SQL Triggers?

SQL triggers are effective tools that carry out tasks in a database automatically when certain events occur, such as inserting or modifying data. Although most work is carried out using queries such as SELECT or INSERT, triggers can perform actions automatically, for example, monitoring deletions or updating statistics. Using triggers can help you keep data consistent, repetitive tasks, and enhance database performance. This tutorial will teach you how to use triggers in MySQL, including the various types, syntax, and examples to make your database task automated.

How to Use Triggers in MySQL: Trigger Syntax

The SQL trigger format is simple to read and contains a simple template made up of the trigger name, the event to trigger it, the table that it operates on, and the SQL actions detailing what it does.

sql

CREATE TRIGGER trigger_name
[BEFORE | AFTER] [INSERT | UPDATE | DELETE]
ON table_name
FOR EACH ROW
BEGIN
  -- SQL statements
END;

Example: Track changes in the employee's table:

sql

Copy
CREATE TRIGGER log_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  INSERT INTO employees_log (employee_id, name, action)
  VALUES (OLD.employee_id, OLD.name, 'updated');
END;

Operations with SQL Triggers
 

1. Creating Triggers

Create triggers to log data changes automatically. For instance, a trigger to log deleted employee records:

sql

Copy
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW 
BEGIN   
   INSERT INTO employees_log (employee_id, name, action)  
   VALUES (OLD.employee_id, OLD.name, 'deleted');
END;

   2. Modifying Triggers

You cannot directly modify a trigger. Instead, drop the existing trigger and create a new one:

sql

Copy
DROP TRIGGER IF EXISTS log_changes; 
CREATE TRIGGER log_changes
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN    
   INSERT INTO employees_log (employee_id, name, action)  
   VALUES (OLD.employee_id, OLD.name, 'updated');
END;

    3. Deleting Triggers

To remove a trigger, use:

sql

DROP TRIGGER IF EXISTS log_changes;

   4. Displaying Existing Triggers

To view existing triggers:

sql

SHOW TRIGGERS;

Related topics you may enjoy: Stop Using console.log() for Debugging , Do This Instead!

Types of SQL Triggers

1. DML Triggers (Data Manipulation Language)
    

• AFTER: Executes after INSERT, UPDATE, or DELETE.
   
• BEFORE: Executes before INSERT, UPDATE, or DELETE.
   
• INSTEAD OF: Replace the operation with the trigger's actions.
   

   2. DDL Triggers (Data Definition Language)

These respond to schema changes such as CREATE, ALTER, or DROP.
 

   3. Logon Triggers
 

Executed when a user logs into the database, often used for logging or enforcing login policies.
 

Examples of SQL triggers
 

Basic Trigger: Track Deletions

Create the tables:

sql

Copy
CREATE TABLE employees (
   employee_id INT, 
   name VARCHAR(100), 
   department VARCHAR(100)
);

CREATE TABLE employees_log (
   employee_id INT, 
   name VARCHAR(100), 
   action VARCHAR(100)
);

INSERT INTO employees (employee_id, name, department)
VALUES (1, 'Alice', 'HR'), (2, 'Bob', 'IT'), (3, 'Charlie', 'Sales');
 

Create the trigger:

sql

Copy
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW 
BEGIN   
   INSERT INTO employees_log (employee_id, name, action)   
   VALUES (OLD.employee_id, OLD.name, 'deleted');
END;

Logging Updates

Track updates to the employee's table:

sql

Copy
CREATE TRIGGER after_employee_update
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN  
   INSERT INTO employees_log (employee_id, name, action)
   VALUES (OLD.employee_id, OLD.name, 'updated');
END;

maintaining Data Consistency

Update related tables automatically. For instance, update the last order date when a new order is placed:

sql
Copy
CREATE TRIGGER after_order_insert
AFTER INSERT ON orders
FOR EACH ROW
BEGIN   
   UPDATE customers   
   SET last_order_date = NOW()    
   WHERE id = NEW.customer_id;
END;

Advanced SQL Trigger Ideas


Nested Triggers
 

These occur when one trigger causes another to fire.Use with caution to prevent problems with performance.
 

Recursive Triggers
 

These have the ability to call themselves, which could result in endless loops. Careful planning is required to avoid these problems.
 

Handling Errors

SQL triggers can handle errors gracefully by using exception handling, similar to PL/SQL's EXCEPTION blocks.
 

Top Techniques to use triggers in my SQL
 

1. Make Triggers Easy and Effective
    

To prevent triggers from slowing down database processes, steer clear of sophisticated logic.

 

    2. Utilise Triggers for Auditing and Logging
 

Triggers are great for automatically monitoring changes in data, which aids in troubleshooting and compliance.

Overview