Skip to content

PL/SQL Triggers (The Alarm) 🚨

Prerequisites: DML Basics, PL/SQL Basics

Mentor's Note: A Trigger is a program that Starts Automatically when a specific event happens (like someone updating a salary). It is like a "Security Alarm": you don't call the alarm; the alarm calls you when someone breaks in! 💡

🌟 The Scenario: The Audit Log 📜

Imagine you are a bank manager. - You don't trust everyone. 🕵️‍♂️ - Goal: Every time a salary is changed, you want to record "Who changed it" and "When". - The Trigger: You set a "Sensor" on the Salary column. As soon as a change happens, the trigger wakes up and writes a note in the Audit Table. ✅

💻 1. Creating a Trigger

A trigger usually follows this timing: BEFORE or AFTER.

CREATE OR REPLACE TRIGGER trg_salary_audit
BEFORE UPDATE ON employees
FOR EACH ROW -- 🚀 This makes it a "Row-Level" trigger
BEGIN
   -- Record the change
   INSERT INTO audit_log (msg) 
   VALUES ('User ' || USER || ' changed salary of ID ' || :OLD.id);
END;
/

💻 2. The :OLD and :NEW Keywords 🔄

These are magic values only available inside triggers: - :OLD: The value before the change. - :NEW: The value after the change.

IF :NEW.salary < :OLD.salary THEN
   RAISE_APPLICATION_ERROR(-20001, 'You cannot decrease a salary!');
END IF;

🛡️ 3. Statement vs. Row Level (Architect's Note)

  1. Row-Level (FOR EACH ROW): Fires once for every single row affected. Use this for auditing or validation.
  2. Statement-Level: Fires once per command, even if 1,000 rows are updated. Use this for general security checks.
  3. Architect's Warning: Too many triggers will slow down your database "Writes" (Inserts/Updates). Use them only for critical business rules! 🐢

🗑️ 4. Managing Triggers

ALTER TRIGGER trg_salary_audit DISABLE; -- Temporarily stop
ALTER TRIGGER trg_salary_audit ENABLE;  -- Start again
DROP TRIGGER trg_salary_audit;          -- Delete forever

📈 Learning Path