Database Triggers in PL/SQL – A Detailed Explanation

What is a Trigger?

A trigger is a special PL/SQL program that automatically executes before or after a specific event (such as INSERT, UPDATE, or DELETE) occurs on a table.

Triggers are used for:
✅ Automatic enforcement of business rules
✅ Maintaining data integrity and consistency
✅ Auditing changes in tables
✅ Executing complex validation before/after DML operations

---

Types of Triggers in PL/SQL

Triggers are classified based on timing, event, and scope.

1. Classification Based on Timing

Trigger Type	Description
BEFORE Trigger	Executes before a DML operation (INSERT, UPDATE, DELETE).
AFTER Trigger	Executes after a DML operation.

2. Classification Based on Event

Trigger Type	Description
INSERT Trigger	Fires when a new record is inserted.
UPDATE Trigger	Fires when a record is updated.
DELETE Trigger	Fires when a record is deleted.

3. Classification Based on Scope

Trigger Type	Description
Row-Level Trigger	Executes once for each row affected by a DML operation.
Statement-Level Trigger	Executes once for the entire statement, regardless of how many rows are affected.

---

Syntax for Creating a Trigger

CREATE OR REPLACE TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}  
ON table_name  
[FOR EACH ROW]  
DECLARE
    -- Optional variable declarations
BEGIN
    -- PL/SQL logic
END trigger_name;

📌 Key Points:

• BEFORE | AFTER → Defines when the trigger fires.
• INSERT | UPDATE | DELETE → Defines which DML event activates the trigger.
• FOR EACH ROW → Specifies if the trigger applies to each row or the entire statement.

---

1. BEFORE INSERT Trigger

A BEFORE INSERT trigger executes before a new record is inserted.

Example: Automatically Set Default Join Date

CREATE OR REPLACE TRIGGER before_insert_student
BEFORE INSERT ON Students
FOR EACH ROW
BEGIN
    IF :NEW.JoinDate IS NULL THEN
        :NEW.JoinDate := SYSDATE;
    END IF;
END before_insert_student;

📌 Explanation:

• :NEW refers to new values being inserted.
• If JoinDate is NULL, the trigger sets it to the current date (SYSDATE).

Testing the Trigger

INSERT INTO Students (StudentID, Name, Age) VALUES (102, 'John Doe', 20);
SELECT * FROM Students WHERE StudentID = 102;

🔹 The JoinDate is automatically set if not provided.

---

2. AFTER UPDATE Trigger

An AFTER UPDATE trigger executes after a record is updated.

Example: Track Last Modification Date

CREATE OR REPLACE TRIGGER after_update_student
AFTER UPDATE ON Students
FOR EACH ROW
BEGIN
    UPDATE Students SET LastModified = SYSDATE WHERE StudentID = :NEW.StudentID;
END after_update_student;

📌 Explanation:

• Updates the LastModified column when any record is updated.
• :NEW refers to the updated values.

Testing the Trigger

UPDATE Students SET Age = 21 WHERE StudentID = 102;
SELECT * FROM Students WHERE StudentID = 102;

🔹 The LastModified column gets updated after every update operation.

---

3. BEFORE DELETE Trigger

A BEFORE DELETE trigger executes before a record is deleted.

Example: Prevent Deletion of Important Data

CREATE OR REPLACE TRIGGER before_delete_student
BEFORE DELETE ON Students
FOR EACH ROW
BEGIN
    IF :OLD.ImportantStudent = 'Y' THEN
        RAISE_APPLICATION_ERROR(-20001, 'Cannot delete an important student.');
    END IF;
END before_delete_student;

📌 Explanation:

• :OLD refers to existing values before deletion.
• If ImportantStudent = 'Y', deletion is prevented.
• RAISE_APPLICATION_ERROR displays a custom error message.

Testing the Trigger

DELETE FROM Students WHERE StudentID = 101;

🔹 If the student is marked as important, deletion is blocked.

---

4. Row-Level vs. Statement-Level Triggers

• Row-Level Trigger → Executes once per row affected.
• Statement-Level Trigger → Executes once per DML statement (regardless of how many rows are affected).

Example of Statement-Level Trigger

CREATE OR REPLACE TRIGGER after_bulk_insert
AFTER INSERT ON Students
BEGIN
    DBMS_OUTPUT.PUT_LINE('New students inserted.');
END after_bulk_insert;

📌 Key Difference:

• This trigger executes once per INSERT statement (not per row).

---

5. Compound Triggers

A compound trigger allows handling different timing events (BEFORE and AFTER) within a single trigger.

Example: Compound Trigger to Log Inserts

CREATE OR REPLACE TRIGGER student_insert_logger
FOR INSERT ON Students
COMPOUND TRIGGER  
    v_count NUMBER;
    
    BEFORE STATEMENT IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Insert operation started.');
    END BEFORE STATEMENT;
    
    AFTER EACH ROW IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Inserted Student ID: ' || :NEW.StudentID);
    END AFTER EACH ROW;
    
    AFTER STATEMENT IS
    BEGIN
        DBMS_OUTPUT.PUT_LINE('Insert operation completed.');
    END AFTER STATEMENT;
END student_insert_logger;

📌 Explanation:

• BEFORE STATEMENT runs before the first row is inserted.
• AFTER EACH ROW logs each new inserted row.
• AFTER STATEMENT runs after all rows are inserted.

---

6. Auditing with Triggers

Triggers are often used for auditing changes in a table.

Example: Log Student Updates

CREATE OR REPLACE TRIGGER log_student_updates
AFTER UPDATE ON Students
FOR EACH ROW
BEGIN
    INSERT INTO Student_Log (StudentID, OldAge, NewAge, ModifiedAt)
    VALUES (:OLD.StudentID, :OLD.Age, :NEW.Age, SYSDATE);
END log_student_updates;

📌 Explanation:

• When a student’s age is updated, the old and new values are logged in the Student_Log table.

---

7. Dropping a Trigger

To remove a trigger:

DROP TRIGGER trigger_name;

📌 Example:

DROP TRIGGER before_insert_student;

---

Advantages of Using Triggers

✅ Automates Business Logic – Automatically enforces rules.
✅ Improves Data Integrity – Prevents invalid data changes.
✅ Enforces Security – Restricts unauthorized actions.
✅ Reduces Application Code – Moves logic to the database.

---

Disadvantages of Using Triggers

❌ Hidden Execution – Triggers run automatically, making debugging difficult.
❌ Performance Overhead – Excessive triggers can slow down transactions.
❌ Complexity – Managing multiple triggers can be difficult.

---

Conclusion

🔹 Triggers enhance data integrity, security, and automation in PL/SQL.
🔹 Use BEFORE triggers to validate or modify data before changes.
🔹 Use AFTER triggers for logging and auditing changes.
🔹 Use Compound Triggers for handling multiple timing events efficiently.