🔔 MySQL Triggers – Automate Table Logic with BEFORE/AFTER INSERT, UPDATE, DELETE

---

🧲 Introduction – Why Use MySQL Triggers?

MySQL Triggers are database-level scripts that automatically run in response to INSERT, UPDATE, or DELETE actions on a table. They help you:

• ✅ Enforce data rules
• 🛡️ Maintain audit trails
• 🔄 Synchronize tables
• 📊 Automate calculations

Triggers eliminate the need for repetitive code in your application logic and ensure consistent behavior directly at the database layer.

🎯 In this guide, you’ll learn:

• How to create BEFORE and AFTER triggers
• Trigger syntax and use cases for INSERT, UPDATE, and DELETE
• Examples and best practices
• How to manage and drop triggers

---

🔄 1. Trigger Types in MySQL

Trigger Timing	Trigger Event	Purpose
BEFORE	INSERT	Validate or transform data before insert
AFTER	INSERT	Audit or log inserted data
BEFORE	UPDATE	Restrict or pre-process new values
AFTER	UPDATE	Record changes in audit/history table
BEFORE	DELETE	Capture data before deletion
AFTER	DELETE	Archive deleted rows

✅ You can have one trigger per event per timing on a table.

---

🧱 2. Syntax – CREATE TRIGGER

CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name FOR EACH ROW
BEGIN
  -- SQL logic here
END;

Use OLD.column_name to refer to existing data, and NEW.column_name for new values during insert/update.

---

🔧 3. BEFORE INSERT Trigger Example

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

Explanation:
Automatically sets created_at before inserting a new user.

---

🧾 4. AFTER INSERT Trigger – Logging Example

CREATE TRIGGER after_insert_order
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  INSERT INTO order_log(order_id, action, logged_at)
  VALUES (NEW.id, 'created', NOW());
END;

✅ Used for tracking inserts into a separate audit table.

---

🔄 5. BEFORE UPDATE Trigger – Validate or Normalize

CREATE TRIGGER before_update_product
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
  IF NEW.price < 0 THEN
    SET NEW.price = 0;
  END IF;
END;

✅ Ensures price is never negative before saving changes.

---

📝 6. AFTER UPDATE Trigger – Change Log

CREATE TRIGGER after_update_email
AFTER UPDATE ON users
FOR EACH ROW
BEGIN
  IF OLD.email != NEW.email THEN
    INSERT INTO email_changes(user_id, old_email, new_email, changed_at)
    VALUES (OLD.id, OLD.email, NEW.email, NOW());
  END IF;
END;

✅ Tracks email updates without relying on application code.

---

❌ 7. BEFORE DELETE Trigger – Backup Row

CREATE TRIGGER before_delete_customer
BEFORE DELETE ON customers
FOR EACH ROW
BEGIN
  INSERT INTO customer_backup(id, name, email, deleted_at)
  VALUES (OLD.id, OLD.name, OLD.email, NOW());
END;

✅ Saves a copy of the row before it’s deleted from the main table.

---

🔥 8. AFTER DELETE Trigger – Archive Audit

CREATE TRIGGER after_delete_order
AFTER DELETE ON orders
FOR EACH ROW
BEGIN
  INSERT INTO order_archive(order_id, removed_at)
  VALUES (OLD.id, NOW());
END;

✅ Logs that a row was deleted and when.

---

📋 Trigger Management

🔍 View Triggers

SHOW TRIGGERS;

❌ Drop Trigger

DROP TRIGGER IF EXISTS trigger_name;

---

🧠 Best Practices

✅ Tip	💡 Why It Matters
Keep triggers small and fast	Avoid performance bottlenecks
Use only for consistent, DB-level logic	Prevent logic duplication
Avoid calling external services inside triggers	Can slow down or block transactions
Test trigger behavior with different scenarios	Ensure correctness and avoid unintended loops
Don’t use triggers to enforce complex business rules	Better in app layer or procedures

---

🚀 Real-World Use Cases

Use Case	Trigger Type	Description
Audit data updates	AFTER UPDATE	Save old and new values in audit table
Log order creation	AFTER INSERT	Write to order_log when a new order is made
Archive deleted users	BEFORE DELETE	Copy user data to archive before deletion
Sanitize input on insert	BEFORE INSERT	Format phone numbers or emails
Prevent invalid updates	BEFORE UPDATE	Block changes if new value violates rule

---

📌 Summary – Recap & Next Steps

MySQL triggers let you automate database logic based on table actions like inserts, updates, or deletes. They improve data integrity, reduce duplication, and allow for real-time reactions to changes in your database.

🔍 Key Takeaways

• Use BEFORE triggers to validate or sanitize input
• Use AFTER triggers to log, audit, or notify
• Triggers are per table and per event
• Use NEW and OLD to reference row values
• Keep trigger logic fast and simple

⚙️ Real-World Relevance

Triggers are widely used in audit logging, change tracking, archiving, data consistency enforcement, and cross-table synchronization in ERP, CRM, finance, and analytics platforms.

---

❓ FAQ – MySQL Triggers

---

❓ Can I have multiple triggers for the same event?

❌ No. MySQL supports only one trigger per timing/event combination per table.

---

❓ What’s the difference between NEW and OLD?

• NEW: Refers to the incoming values (INSERT, UPDATE)
• OLD: Refers to the existing values (UPDATE, DELETE)

---

❓ Are triggers transactional?

✅ Yes. If the query fails, the trigger’s actions are rolled back automatically.

---

❓ Can I update the same table inside a trigger?

⚠️ No. MySQL prevents recursive updates (e.g., UPDATE triggering itself).

---

❓ How do I list all triggers?

SHOW TRIGGERS;

---

Share Now :