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.