Triggers

1What is a Trigger?✨

A trigger is a special type of stored procedure in SQL that automatically runs when certain events occur in the database. This can help maintain data integrity and automate tasks without needing manual intervention.

2Why Learn Triggers?🎯

• ✓ Automatically respond to data changes
• ✓ Enforce business rules effortlessly
• ✓ Maintain data integrity and consistency
• ✓ Perform complex actions like logging or updating related tables

3Creating Your First Trigger💡

To create a trigger, you define the event that will fire it. For example, let's create a trigger that logs changes to a `users` table when a record is updated.

CREATE OR REPLACE FUNCTION log_user_update() 
RETURNS TRIGGER AS $$
BEGIN
    INSERT INTO user_logs (user_id, action, changed_at) 
    VALUES (NEW.id, 'Updated', NOW());
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

This function will insert a log entry every time a user is updated.

4Defining a Trigger🚀

Now, we will link our function to a specific event using the following SQL command:

5CREATE TRIGGER after_user_update🔍

• AFTER UPDATE ON users
• FOR EACH ROW
• EXECUTE FUNCTION log_user_update();

This trigger executes our function after each user update.

6Using Triggers for Data Validation⭐

Triggers can also be used to validate data before it is inserted or updated. Here’s an example of a trigger that prevents inserting a user with an invalid email format:

CREATE OR REPLACE FUNCTION validate_email()
RETURNS TRIGGER AS $$
BEGIN
    IF NEW.email !~ '^[^@]+@[^@]+\.[^@]+$' THEN
        RAISE EXCEPTION 'Invalid email format';
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

7CREATE TRIGGER check_email_format🧩

• BEFORE INSERT OR UPDATE ON users
• FOR EACH ROW
• EXECUTE FUNCTION validate_email();

8Capstone Project: Implementing Triggers🎈

For your final project, create a database for a library system. Implement triggers to log actions like adding new books, updating member information, and ensuring that no book can be added without an ISBN. This will showcase your mastery of triggers and their real-world application!