Day 42: MySQL Triggers - Automating Actions in Databases!

Today, let's explore Triggers in MySQL, a powerful feature that allows us to automate actions in response to specific database events!

? What is a Trigger?

A Trigger is a stored program that is automatically executed before or after an INSERT, UPDATE, or DELETE operation on a table.

Triggers help enforce business rules, audit changes, and maintain data consistency without manual intervention.

1. Types of Triggers in MySQL

Trigger Type Executes Before/After Example Use Case

BEFORE INSERT Before a new row is inserted Validate data before insertion

AFTER INSERT After a new row is inserted Log changes in an audit table

BEFORE UPDATE Before an existing row is updated Prevent unauthorized updates

AFTER UPDATE After an existing row is updated Track modifications in a log table

BEFORE DELETE Before a row is deleted Restrict deletions based on conditions

AFTER DELETE After a row is deleted Archive deleted records

2. Creating a Basic Trigger

Example: Automatically Set Created Date on Insert

DELIMITER //
CREATE TRIGGER before_insert_users
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
    SET NEW.created_at = NOW();
END //
DELIMITER ;        

(Automatically sets the created field to the current timestamp before inserting a new user)

3. AFTER INSERT Trigger - Logging Changes

Example: Track New Orders in an Audit Table

DELIMITER //
CREATE TRIGGER after_insert_orders
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
    INSERT INTO order_audit (order_id, customer_id, action, action_time)
    VALUES (NEW.order_id, NEW.customer_id, 'INSERT', NOW());
END //
DELIMITER ;        

( Logs every new order into the order_audit table for tracking)

4. BEFORE UPDATE Trigger - Restrict Changes

Example: Prevent Price Reduction Below a Certain Limit

DELIMITER //
CREATE TRIGGER before_update_products
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    IF NEW.price < 10 THEN
         SET NEW.price = OLD.price;
    END IF;
END //
DELIMITER;        

(Prevents product prices from being set below 10.)

5. AFTER DELETE Trigger - Archive Deleted Data

Example: Store Deleted Employee Data in an Archive Table

DELIMITER //
CREATE TRIGGER after_delete_employees
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employees_archive (emp_id, name, department, deleted_at)
   VALLUES (OLD.emp_id, OLD.name, OLD.department, NOW());
END //
DELIMITER ;        

(Ensures deleted employee records are stored safely for future reference)

6. Deleting a Trigger

? Remove an Unused Trigger

DROP TRIGGER IF EXISTS after_insert_orders;        

(Deletes the trigger if it exists)

Key Benefits of Triggers

? Automation - Reduces manual work and enforces rules

? Data Integrity - Ensures valid data entry and updates

? Audit Logs - Tracks changes for security and compliance

? Prevents Unauthorized Modifications - Restricts unintended updates/deletions

#100DaysOfCode #MySQL #SQL #Database #Triggers #Automation #Learning #BackendDevelopment