01 Creating Triggers

⚡ Creating Triggers - Automating Event Responses

🌟 Overview

Creating Triggers involves defining a special stored procedure that automatically executes in response to specific database events, such as INSERT, UPDATE, or DELETE on a table. Written using Data Definition Language (DDL), triggers enforce data integrity, automate tasks, or log changes, firing either BEFORE or AFTER the event.

In AI/ML, creating triggers is crucial for real-time data validation, like ensuring training_data quality, or logging predictions for audits. For freshers, it’s a core interview topic, often tested in questions about automation, data consistency, and production-grade SQL.

---

📜 Syntax

-- Generic SQL (varies by database)
CREATE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
FOR EACH ROW
BEGIN
    -- Trigger logic
END;

• Basic Example (MySQL syntax):

  DELIMITER //
  CREATE TRIGGER LogNewPrediction
  AFTER INSERT ON predictions
  FOR EACH ROW
  BEGIN
      INSERT INTO prediction_logs (model_id, score, log_time)
      VALUES (NEW.model_id, NEW.score, NOW());
  END //
  DELIMITER ;

• Validation Example (PostgreSQL-style):

  CREATE TRIGGER ValidateTrainingData
  BEFORE INSERT ON training_data
  FOR EACH ROW
  WHEN (NEW.feature1 IS NULL)
  BEGIN
      RAISE EXCEPTION 'Feature1 cannot be NULL';
  END;

---

💡 Use Cases in AI/ML

• Data Validation: Reject invalid training_data inserts (e.g., null features).
• Audit Logging: Log predictions inserts to prediction_logs for tracking.
• Pipeline Sync: Update metadata on staging_table changes for ETL.
• Experiment Tracking: Record experiments updates for reproducibility.
• Feature Enforcement: Ensure features table meets ML model requirements.

---

🔑 Key Features

• Event-Driven: Triggers on INSERT, UPDATE, or DELETE events.
• Row-Level: Executes FOR EACH ROW affected by the event.
• Timing Options: Supports BEFORE (pre-event) or AFTER (post-event).
• Automatic Execution: No manual invocation required.

---

✅ Best Practices

• Name Descriptively: Use names like LogNewPrediction to clarify purpose.
• Keep Lightweight: Avoid complex logic to prevent performance issues.
• Test Thoroughly: Create in a sandbox to verify behavior.
• Document Logic: Comment trigger code to explain event and actions.

---

⚠️ Common Pitfalls

• Syntax Variance: Database-specific syntax (e.g., MySQL vs. PostgreSQL) causes errors.
• Performance Drag: Heavy trigger logic slows large transactions.
• Infinite Loops: Triggers updating their own table risk recursion.
• Missing Privileges: Lack of CREATE TRIGGER permissions blocks creation.

---

📝 Additional Notes

• Database Variations:
  • MySQL: Uses DELIMITER and NEW/OLD for row data.
  • PostgreSQL: Supports WHEN clauses and RAISE for errors.
  • SQL Server: Uses CREATE TRIGGER ... ON ... FOR/AFTER with inserted/deleted.
  • Oracle: Employs :NEW/:OLD in PL/SQL blocks.
• Scope: Triggers are table-specific; multiple triggers per event need prioritization (database-dependent).
• Debugging: Enable logging to trace trigger execution.