MySQL Triggers

  • Trigger = A stored procedure that automatically runs
  • Activated by INSERT, UPDATE, and DELETE

A trigger in MySQL is a special type of stored program that automatically runs (or “fires”) when a certain event happens in a table. Triggers are used to automate actions such as logging, validating data, or updating related tables.

Photo by Growtika on Unsplash

When Do Triggers Run?

  • BEFORE INSERT
  • AFTER INSERT
  • BEFORE UPDATE
  • AFTER UPDATE
  • BEFORE DELETE
  • AFTER DELETE

Not a member yet? CLICK HERE to read the full article for FREE!!

Why Use Triggers?

  • To automatically update audit logs.
  • To enforce business rules (e.g., no negative balance).
  • To keep data consistent across multiple tables.
  • To prevent invalid changes.

Syntax of a Trigger

CREATE TRIGGER trigger_name
timing EVENT ON table_name
FOR EACH ROW
BEGIN
   -- trigger logic here(sql statement)
END;

Here,

trigger_name: Name of the Trigger.

timing: BEFORE or AFTER.

EVENT: INSERT, UPDATE or DELETE.

table_name: Table to attach the trigger to.

MySQL Triggers

https://medium.com/media/d9027c68307daafcd501f739553c683a/href

MySQL Triggers Example

https://medium.com/media/ac00e50304399cb535a496d0d45efdc6/href

Example: Logging Inserted Records

Let’s say we have two tables:

  1. employees – stores employee details.
  2. employee_log – stores log info whenever a new employee is added.

Step 1: Create the Tables

CREATE TABLE employees (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    department VARCHAR(100)
);

CREATE TABLE employee_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    emp_name VARCHAR(100),
    action_time DATETIME
);

Step 2: Create the Trigger

CREATE TRIGGER after_employee_insert
AFTER INSERT ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_log(emp_name, action_time)
    VALUES (NEW.name, NOW());
END;

Testing the Trigger

INSERT INTO employees (name, department) 
VALUES ('Liza Johnson', 'HR');

After running the above insert:

  • A new row will be added to employees.
  • The trigger will automatically insert a row into employee_log with “Liza Johnson” and the current timestamp.

Things to Remember

  • You cannot call a trigger manually, it runs automatically.
  • Triggers work per row, not per statement.
  • Use triggers wisely to avoid performance issues.
  • MySQL does not support SELECT statements inside triggers.

MySQL triggers are powerful tools that help automate tasks like logging, data validation, or syncing tables.

If you like my content, give me a few claps and follow for more!

Thanks for your time

MySQL Triggers was originally published in Javarevisited on Medium, where people are continuing the conversation by highlighting and responding to this story.

This post first appeared on Read More

Related Posts