Triggers and Event Automation

# CHAPTER 22

Triggers and Event Automation #

1. Introduction #

2. Learning Objectives #

• Understand the concept of database Event Automation.

• Differentiate between BEFORE and AFTER triggers.

• Create a special PL/pgSQL Trigger Function.

• Access the NEW and OLD data payloads.

• Bind the Trigger Function to a physical table.

3. The Two Parts of a PostgreSQL Trigger #

1. 1. The Trigger Function: You write a PL/pgSQL function containing the logic.

1. 2. The Trigger Binding: You attach that function to a specific table event (like "AFTER UPDATE on users").

4. Step 1: The Trigger Function (The Logic) #

• NEW: Contains the brand new data being inserted or updated.

• OLD: Contains the previous data that is about to be overwritten or deleted.

CREATE OR REPLACE FUNCTION log_salary_change()
RETURNS TRIGGER 
LANGUAGE plpgsql
AS $$
BEGIN
    -- Only log it if the salary actually changed!
    IF NEW.salary <> OLD.salary THEN
        INSERT INTO audit_log (emp_id, old_salary, new_salary, changed_at)
        VALUES (OLD.emp_id, OLD.salary, NEW.salary, NOW());
    END IF;
    
    -- Trigger functions MUST return NEW (if continuing) or NULL (if cancelling the action).
    RETURN NEW;
END;
$$;

5. Step 2: The Trigger Binding (The Tripwire) #

CREATE TRIGGER trigger_audit_salary
AFTER UPDATE ON employees
FOR EACH ROW
EXECUTE FUNCTION log_salary_change();

*(Now, no matter who updates the table—PHP, Python, or an admin in pgAdmin—the tripwire fires, and the audit log is perfectly maintained!)*

6. BEFORE vs AFTER Triggers #

• AFTER Triggers: Best for logging and auditing. They fire *after* the data has been safely saved to the hard drive.

• BEFORE Triggers: Best for validation and data modification. They fire *before* the data hits the hard drive. You can actually intercept the NEW data, alter it, and pass it along!

7. Intercepting Data with a BEFORE Trigger #

-- 1. The Logic
CREATE OR REPLACE FUNCTION auto_capitalize_name()
RETURNS TRIGGER AS $$
BEGIN
    -- We intercept the NEW payload and modify it!
    -- INITCAP() is a built-in Postgres function that capitalizes the first letter.
    NEW.first_name := INITCAP(NEW.first_name);
    RETURN NEW; -- Pass the modified payload down to the hard drive!
END;
$$ LANGUAGE plpgsql;

-- 2. The Tripwire
CREATE TRIGGER trigger_capitalize_name
BEFORE INSERT OR UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION auto_capitalize_name();

8. Mini Project: Prevent Accidental Deletions #

CREATE OR REPLACE FUNCTION soft_delete_user()
RETURNS TRIGGER AS $$
BEGIN
    -- Mark the user inactive instead of deleting them
    UPDATE users SET is_active = FALSE WHERE id = OLD.id;
    
    -- Returning NULL cancels the physical DELETE operation!
    RETURN NULL; 
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trigger_prevent_delete
BEFORE DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION soft_delete_user();

9. Common Mistakes #

• Infinite Loops: If you have an AFTER UPDATE trigger on the users table, and the logic inside that trigger executes an UPDATE on the users table... it fires the trigger again, which fires the trigger again! The database will freeze and crash. Never update the table that fired the trigger!

• Forgetting FOR EACH ROW: If you do not specify FOR EACH ROW, the trigger becomes a "Statement-Level" trigger. If an UPDATE modifies 50 rows, the trigger only fires ONE time for the entire batch (and you cannot use the NEW/OLD variables!).

10. Best Practices #

• Triggers are "Hidden Magic": Use them sparingly. If a new developer joins the team and tests an INSERT command, and suddenly 5 other tables are magically updated, they will be very confused because the Trigger is invisible to the application code. Always document your triggers extensively.

11. Exercises #

1. 1. In a Trigger Function, what does the OLD variable contain during an INSERT operation? (Hint: Think logically).

1. 2. Write the syntax to physically remove the triggerpreventdelete trigger from the users table. (Hint: use DROP TRIGGER...)

12. SQL Challenges #

CREATE OR REPLACE FUNCTION enforce_zero_price()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.price < 0 THEN
        NEW.price := 0.00;
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

13. MCQ Quiz with Answers #

14. Interview Questions #

• Q: Explain the fundamental difference between a BEFORE trigger and an AFTER trigger. In what scenario MUST you use a BEFORE trigger?

• Q: What is the difference between a Row-Level trigger (FOR EACH ROW) and a Statement-Level trigger?

15. FAQs #

16. Summary #

17. Next Chapter Recommendation #