Introduction to Triggers
Triggers are a powerful feature in PL/SQL that allow you to automatically execute a block of code in response to specific events on a table or view. They are commonly used for enforcing business rules, auditing changes, and maintaining complex data integrity.
Key Concepts
- Trigger Event: The event that causes the trigger to fire (e.g., INSERT, UPDATE, DELETE).
- Trigger Timing: Specifies when the trigger should fire (e.g., BEFORE, AFTER).
- Trigger Type: Defines whether the trigger is a row-level or statement-level trigger.
- Trigger Body: The PL/SQL block that contains the code to be executed when the trigger fires.
Types of Triggers
Based on Timing
- BEFORE Trigger: Executes before the triggering event.
- AFTER Trigger: Executes after the triggering event.
Based on Scope
- Row-Level Trigger: Executes once for each row affected by the triggering event.
- Statement-Level Trigger: Executes once for the entire triggering event, regardless of the number of rows affected.
Creating a Trigger
Syntax
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER} {INSERT | UPDATE | DELETE}
ON table_name
[FOR EACH ROW]
DECLARE
-- Variable declarations
BEGIN
-- PL/SQL code
END;Example: Creating a Row-Level Trigger
Let's create a trigger that automatically updates a last_updated column in a employees table whenever an employee's record is updated.
CREATE OR REPLACE TRIGGER update_last_updated BEFORE UPDATE ON employees FOR EACH ROW BEGIN :NEW.last_updated := SYSDATE; END; /
Explanation
CREATE OR REPLACE TRIGGER update_last_updated: Creates a new trigger or replaces an existing one with the same name.BEFORE UPDATE ON employees: Specifies that the trigger should fire before an UPDATE operation on theemployeestable.FOR EACH ROW: Indicates that this is a row-level trigger.:NEW.last_updated := SYSDATE;: Sets thelast_updatedcolumn to the current date and time.
Practical Exercises
Exercise 1: Creating a Trigger for Auditing
Create a trigger that logs changes to the salary column in the employees table into an audit_log table.
Solution
- Create the
audit_logtable:
CREATE TABLE audit_log ( employee_id NUMBER, old_salary NUMBER, new_salary NUMBER, change_date DATE );
- Create the trigger:
CREATE OR REPLACE TRIGGER audit_salary_changes AFTER UPDATE OF salary ON employees FOR EACH ROW BEGIN INSERT INTO audit_log (employee_id, old_salary, new_salary, change_date) VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSDATE); END; /
Explanation
AFTER UPDATE OF salary ON employees: Specifies that the trigger should fire after an UPDATE operation on thesalarycolumn of theemployeestable.INSERT INTO audit_log ...: Inserts a new record into theaudit_logtable with the old and new salary values and the current date.
Exercise 2: Preventing Deletions
Create a trigger that prevents the deletion of records from the employees table if the employee's salary is greater than 100,000.
Solution
CREATE OR REPLACE TRIGGER prevent_high_salary_deletion
BEFORE DELETE ON employees
FOR EACH ROW
BEGIN
IF :OLD.salary > 100000 THEN
RAISE_APPLICATION_ERROR(-20001, 'Cannot delete employees with salary greater than 100,000');
END IF;
END;
/Explanation
BEFORE DELETE ON employees: Specifies that the trigger should fire before a DELETE operation on theemployeestable.IF :OLD.salary > 100000 THEN ...: Checks if the salary of the employee being deleted is greater than 100,000.RAISE_APPLICATION_ERROR(-20001, 'Cannot delete employees with salary greater than 100,000');: Raises an application error to prevent the deletion.
Common Mistakes and Tips
- Mutating Table Error: Avoid writing triggers that query or modify the table that caused the trigger to fire, as this can lead to a mutating table error.
- Performance Impact: Be cautious of the performance impact of triggers, especially row-level triggers on large tables.
- Debugging: Use
DBMS_OUTPUT.PUT_LINEto debug triggers by printing messages to the console.
Conclusion
Triggers are a versatile tool in PL/SQL for automating tasks and enforcing business rules. By understanding the different types of triggers and how to create them, you can enhance the functionality and integrity of your database applications. In the next module, we will delve into PL/SQL optimization and best practices to ensure your code runs efficiently and effectively.