Triggers

🎯 SQL Triggers: Your Database’s Secret Bodyguards

Imagine having a team of invisible helpers who automatically spring into action whenever something happens in your database. That’s exactly what triggers are!

🌟 The Big Picture: What Are Triggers?

Think of your database like a smart home. You have motion sensors, smoke detectors, and automatic lights. When someone walks in, the lights turn on automatically. When smoke is detected, the alarm goes off by itself.

SQL Triggers work the same way!

They are special instructions that say:

“Hey database, whenever THIS happens, automatically do THAT.”

graph TD
    A["Something Happens"] --> B{Trigger Watching?}
    B -->|Yes| C["Trigger Fires!"]
    C --> D["Automatic Action"]
    B -->|No| E["Nothing Extra"]

🔧 Triggers Fundamentals

What Makes a Trigger Special?

A trigger is like a loyal dog that watches for specific events:

• It waits patiently
• It notices when something happens
• It reacts automatically

Three Things Every Trigger Needs:

Simple Example: The Welcome Greeter

Imagine a hotel register. When a new guest signs in, we want to automatically record the exact time.

-- This trigger is like
-- an automatic timestamp!
CREATE TRIGGER welcome_guest
AFTER INSERT ON guests
FOR EACH ROW
BEGIN
  INSERT INTO log_table
  VALUES (NEW.name, NOW());
END;

What happens:

1. Guest gets added to guests table
2. Trigger sees the INSERT
3. Trigger automatically logs it!

🏗️ CREATE TRIGGER: Building Your First Trigger

The Basic Recipe

Creating a trigger is like writing a recipe card:

CREATE TRIGGER trigger_name
timing event ON table_name
FOR EACH ROW
BEGIN
  -- What to do
END;

Breaking It Down:

Real Example: Automatic Price Logger

CREATE TRIGGER log_price_change
AFTER UPDATE ON products
FOR EACH ROW
BEGIN
  INSERT INTO price_history
  (product_id, old_price,
   new_price, changed_at)
  VALUES
  (OLD.id, OLD.price,
   NEW.price, NOW());
END;

Magic Words:

• NEW = the new data coming in
• OLD = the data that was there before

⏰ BEFORE and AFTER Triggers

BEFORE Triggers: The Bouncer

Like a bouncer at a club, BEFORE triggers check things before they happen.

graph TD
    A["Data Wants In"] --> B["BEFORE Trigger"]
    B --> C{Check OK?}
    C -->|Yes| D["Data Goes In"]
    C -->|No| E["Rejected!"]

Use BEFORE when you want to:

• Validate data
• Modify data before saving
• Block bad data

-- Check age before insert
CREATE TRIGGER check_age
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF NEW.age < 0 THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT =
    'Age cannot be negative!';
  END IF;
END;

AFTER Triggers: The Reporter

Like a news reporter, AFTER triggers tell everyone what just happened.

graph TD
    A["Action Completes"] --> B["AFTER Trigger"]
    B --> C["Log It"]
    B --> D["Notify"]
    B --> E["Update Stats"]

Use AFTER when you want to:

• Log what happened
• Update related tables
• Send notifications

-- Log successful order
CREATE TRIGGER order_complete
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO activity_log
  VALUES (
    'New order placed',
    NEW.order_id,
    NOW()
  );
END;

Quick Comparison

🎬 INSERT, UPDATE, DELETE Triggers

INSERT Triggers: The Welcome Committee

Fires when new data arrives.

-- Auto-set creation date
CREATE TRIGGER set_created
BEFORE INSERT ON posts
FOR EACH ROW
BEGIN
  SET NEW.created_at = NOW();
  SET NEW.status = 'draft';
END;

Access: Only NEW (the incoming data)

UPDATE Triggers: The Change Detective

Fires when data changes.

-- Track who modified what
CREATE TRIGGER track_changes
AFTER UPDATE ON documents
FOR EACH ROW
BEGIN
  INSERT INTO audit_log
  VALUES (
    OLD.id,
    OLD.content,
    NEW.content,
    CURRENT_USER(),
    NOW()
  );
END;

Access: Both OLD and NEW

DELETE Triggers: The Backup Guardian

Fires when data is removed.

-- Backup before delete
CREATE TRIGGER backup_deleted
BEFORE DELETE ON customers
FOR EACH ROW
BEGIN
  INSERT INTO deleted_backup
  VALUES (
    OLD.id,
    OLD.name,
    OLD.email,
    NOW()
  );
END;

Access: Only OLD (the leaving data)

Quick Reference Table

💡 Trigger Use Cases

1. Automatic Timestamps

Never manually set dates again!

CREATE TRIGGER auto_timestamp
BEFORE INSERT ON articles
FOR EACH ROW
BEGIN
  SET NEW.created_at = NOW();
  SET NEW.updated_at = NOW();
END;

CREATE TRIGGER update_timestamp
BEFORE UPDATE ON articles
FOR EACH ROW
BEGIN
  SET NEW.updated_at = NOW();
END;

2. Data Validation

Catch bad data before it enters!

CREATE TRIGGER validate_email
BEFORE INSERT ON users
FOR EACH ROW
BEGIN
  IF NEW.email NOT LIKE '%@%.%'
  THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT =
    'Invalid email format!';
  END IF;
END;

3. Audit Logging

Keep a history of everything!

CREATE TRIGGER audit_salary
AFTER UPDATE ON employees
FOR EACH ROW
BEGIN
  IF OLD.salary != NEW.salary THEN
    INSERT INTO salary_audit
    VALUES (
      NEW.id,
      OLD.salary,
      NEW.salary,
      CURRENT_USER(),
      NOW()
    );
  END IF;
END;

4. Cascading Updates

Update related tables automatically!

CREATE TRIGGER update_stock
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
  UPDATE products
  SET stock = stock - NEW.quantity
  WHERE id = NEW.product_id;
END;

5. Maintaining Summary Tables

Keep totals up to date!

CREATE TRIGGER update_total
AFTER INSERT ON sales
FOR EACH ROW
BEGIN
  UPDATE daily_summary
  SET total_sales =
      total_sales + NEW.amount
  WHERE sale_date =
      DATE(NEW.sale_time);
END;

🎯 Real-World Story: The Online Store

Imagine you run an online bookstore. Here’s how triggers help:

graph TD
    A["Customer Orders Book"] --> B["INSERT Trigger"]
    B --> C["Set Order Time"]
    B --> D["Update Stock Count"]
    B --> E["Log Activity"]
    F["Price Changes"] --> G["UPDATE Trigger"]
    G --> H["Record Old Price"]
    G --> I["Notify Customers"]
    J["Book Discontinued"] --> K["DELETE Trigger"]
    K --> L["Archive Record"]
    K --> M["Remove from Catalog"]

Your triggers work 24/7:

• Automatically timestamps every order
• Keeps stock counts accurate
• Creates audit trails
• Archives important data

✨ Key Takeaways

🚀 You’ve Got This!

Triggers are your database’s silent superheroes. They:

• ✅ Work automatically
• ✅ Never forget
• ✅ Are consistent
• ✅ Save you coding time

Now you understand how to make your database smart and self-managing!

“With triggers, your database doesn’t just store data—it protects, validates, and maintains it automatically!”