PL/SQL - Triggers: Your Friendly Guide to Database Automation

Hello there, future database wizards! Today, we're diving into the magical world of PL/SQL triggers. Don't worry if you're new to programming – I'll be your trusty guide on this adventure. By the end of this tutorial, you'll be creating triggers like a pro!

What Are Triggers, Anyway?

Imagine you're a librarian (stick with me here). Every time a book is borrowed, you need to update a log, check if it's overdue, and maybe send a reminder to the borrower. Doing this manually for every book would be exhausting! That's where triggers come in.

In the database world, triggers are like helpful little elves that automatically perform actions when certain events occur. They're pieces of code that "trigger" in response to specific database operations.

Creating Triggers: Let's Get Our Hands Dirty!

Now, let's create our first trigger. We'll start simple and work our way up.

Basic Trigger Structure

Here's the general structure of a trigger:

CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
[FOR EACH ROW]
[WHEN condition]
DECLARE
   -- Variable declarations
BEGIN
   -- Trigger body
EXCEPTION
   -- Exception handling
END;
/

Don't let this scare you! We'll break it down piece by piece.

Example 1: A Simple "Hello, World!" Trigger

Let's create a trigger that says "Hello, World!" whenever a new row is inserted into a table:

CREATE OR REPLACE TRIGGER hello_world_trigger
AFTER INSERT ON employees
BEGIN
    DBMS_OUTPUT.PUT_LINE('Hello, World! A new employee was added!');
END;
/

What's happening here?

• CREATE OR REPLACE TRIGGER: This tells the database we're creating a new trigger (or replacing an existing one).
• AFTER INSERT ON employees: Our trigger will fire after a new row is inserted into the 'employees' table.
• BEGIN...END: This is where we put our trigger logic.
• DBMS_OUTPUT.PUT_LINE: This is just a fancy way to print text.

Example 2: Logging Changes

Let's create a more useful trigger that logs changes to an 'employees' table:

CREATE OR REPLACE TRIGGER log_salary_changes
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO salary_change_log (employee_id, old_salary, new_salary, change_date)
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

What's new here?

• AFTER UPDATE OF salary: This trigger fires only when the 'salary' column is updated.
• FOR EACH ROW: This means the trigger fires for each row affected by the UPDATE.
• :OLD and :NEW: These refer to the old and new values of the updated row.

Triggering a Trigger: Making the Magic Happen

Now that we've created triggers, how do we make them fire? Simple! By performing the database operations they're watching for.

Example 3: Firing Our Triggers

Let's see our triggers in action:

-- This will fire our 'hello_world_trigger'
INSERT INTO employees (employee_id, name, salary)
VALUES (1, 'John Doe', 50000);

-- This will fire our 'log_salary_changes' trigger
UPDATE employees
SET salary = 55000
WHERE employee_id = 1;

When you run these commands, behind the scenes, our triggers spring into action!

Types of Triggers: A Handy Table

Here's a quick reference table of the different types of triggers:

Trigger Type	Description	Example Use Case
BEFORE	Fires before the triggering action	Validate or modify input data before it's inserted
AFTER	Fires after the triggering action	Log changes or update related tables
INSERT	Fires on INSERT operations	Automatically generate related records
UPDATE	Fires on UPDATE operations	Track data changes over time
DELETE	Fires on DELETE operations	Maintain referential integrity
INSTEAD OF	Used with views to define custom insert, update, or delete operations	Implement complex view updates

Wrapping Up: You're Now a Trigger Apprentice!

Congratulations! You've taken your first steps into the world of PL/SQL triggers. We've covered the basics of creating triggers, seen them in action, and even peeked at the different types available.

Remember, triggers are powerful tools, but with great power comes great responsibility. Use them wisely, and they'll make your database work smarter, not harder.

Keep practicing, stay curious, and before you know it, you'll be a trigger maestro! Happy coding, future database wizards!

Credits: Image by storyset