?️ MySQL Triggers

? Quick Overview

A trigger in MySQL is a stored set of SQL statements executed automatically in response to events on a table (INSERT, UPDATE, DELETE). Triggers help enforce data integrity, perform auditing, or modify data automatically without changing application code.

? Key Concepts

Timing: BEFORE or AFTER an event.
Events: INSERT, UPDATE, DELETE.
FOR EACH ROW: Trigger fires per affected row.
NEW / OLD: Access row data - NEW (after), OLD (before).
DELIMITER: Change delimiter when creating triggers in SQL clients.

? Syntax / Theory

? View Code Example

-- Trigger skeleton: timing, event, table and per-row block
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
-- trigger logic goes here
END;

? Code Example

? View Code Example

-- Use DELIMITER when creating multi-statement triggers in many clients
DELIMITER $$

CREATE TRIGGER before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
-- Set created_at field to current timestamp before inserting
SET NEW.created_at = NOW();
END$$

DELIMITER ;

-- Test the trigger by inserting a new employee
INSERT INTO employees (name, position) VALUES ('John Doe', 'Manager');

? View Code Example

-- Example: log deleted rows into audit table
DELIMITER $$
CREATE TRIGGER after_employee_delete
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
-- Insert deleted row details into employees_backup table
INSERT INTO employees_backup (employee_id, name, position, deleted_at)
VALUES (OLD.id, OLD.name, OLD.position, NOW());
END$$
DELIMITER ;

? Interactive Simulator

Simulating a BEFORE INSERT trigger. The trigger will force the Name to Uppercase and add a Timestamp.

CREATE TRIGGER before_insert_demo BEFORE INSERT ON employees FOR EACH ROW
BEGIN
  SET NEW.name = UPPER(NEW.name); -- Force Uppercase
  SET NEW.joined_at = NOW();      -- Add missing Timestamp
END;

Waiting for user input...

ID	Name	Position	Joined_At (Triggered)

? Live Output / Explanation

How it behaves

The before_employee_insert trigger runs automatically before each row is inserted into employees. It sets the created_at column to the current timestamp, ensuring the value is populated even when the INSERT omits it.

The after_employee_delete trigger records deleted rows into a backup table for audit/history.

? Tips & Best Practices

Use triggers for auditing changes or enforcing data integrity when appropriate.
Give descriptive names to triggers (e.g., before_insert_employees).
Keep trigger logic small and fast — complex triggers can hurt write performance.
Test triggers carefully; use transactions and rollback when experimenting.
Avoid triggers that update the same table in ways that may cause recursive loops.

? Try It Yourself

Create an AFTER INSERT trigger on an orders table to deduct stock from products.
Write a trigger that logs deleted records into a backup table with user and timestamp.
Experiment with a BEFORE UPDATE trigger to normalize text fields (e.g., trim and lowercase).