Oracle PL SQL Tutorial Day 14
Introduction
In Oracle PL/SQL, a trigger is a stored procedure that is automatically invoked (or "triggered") when a specific event occurs in the database. Triggers are one of the most powerful tools available to developers and DBAs for enforcing business rules, auditing changes, and maintaining data integrity.
Unlike procedures and functions that must be explicitly called, triggers are reactive—they respond automatically to DML (INSERT, UPDATE, DELETE) events or system events such as logins or errors.
This blog will explore what triggers are, their use cases, various types of triggers, syntax, and practical examples using Oracle’s HR schema. By the end of this guide, you'll understand how to design, implement, and manage PL/SQL triggers effectively.
What is a Trigger?
A trigger is a named PL/SQL block stored in the database that is executed automatically when a specified database event occurs. The event could be:
- A DML event on a table or view (INSERT, UPDATE, DELETE)
- A system event (startup, shutdown, user login)
- A DDL event (CREATE, DROP, ALTER)
Oracle evaluates trigger conditions before or after these events, depending on the trigger type.
Why Use Triggers?
- Triggers are ideal for:
- Enforcing complex integrity constraints
- Maintaining audit trails
- Automatically generating derived column values
- Preventing invalid transactions
- Enforcing security policies
Basic Trigger Syntax
The general syntax for a DML trigger is:
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER | INSTEAD OF}
{INSERT | DELETE | UPDATE [OF column_name]}
ON table_name
[FOR EACH ROW]
[WHEN (condition)]
BEGIN
-- trigger logic
END;
Types of Triggers in Oracle
Oracle supports several types of triggers:
1. **Row-level vs Statement-level Triggers**
- Row-level: Executes once for each row affected by the triggering statement.
- Statement-level: Executes once per triggering statement, regardless of number of rows affected.
2. **BEFORE vs AFTER Triggers**
- BEFORE: Executes before the DML event.
- AFTER: Executes after the DML event.
3. **INSTEAD OF Triggers**
- Used on views to enable DML operations that would otherwise be illegal.
4. **System Triggers**
- Respond to system events like LOGON, LOGOFF, STARTUP, SHUTDOWN.
Example 1: BEFORE INSERT Trigger
Let’s say we want to automatically convert employee names to uppercase before inserting into the HR.EMPLOYEES table.
CREATE OR REPLACE TRIGGER trg_uppercase_name
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
:NEW.first_name := UPPER(:NEW.first_name);
:NEW.last_name := UPPER(:NEW.last_name);
END;
Explanation:
- FOR EACH ROW means the trigger will fire for each row affected.
- The :NEW pseudo-record allows modifying the new values before insert.
Test:
INSERT INTO employees(employee_id, first_name, last_name, email, hire_date, job_id, salary)
VALUES (210, 'john', 'doe', 'jdoe@example.com', SYSDATE, 'IT_PROG', 6000);
SELECT first_name, last_name FROM employees WHERE employee_id = 210;
-- Output: JOHN DOE
Example 2: AFTER DELETE Trigger (Audit Log)
Let’s log deleted employee details in a separate table.
Step 1: Create audit table:
CREATE TABLE emp_audit (
emp_id NUMBER,
name VARCHAR2(100),
action_date DATE,
action_type VARCHAR2(10)
);
Step 2: Create trigger:
CREATE OR REPLACE TRIGGER trg_emp_audit
AFTER DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO emp_audit(emp_id, name, action_date, action_type)
VALUES (:OLD.employee_id, :OLD.first_name || ' ' || :OLD.last_name, SYSDATE, 'DELETE');
END;
Test:
DELETE FROM employees WHERE employee_id = 210;
SELECT * FROM emp_audit;
Example 3: UPDATE Trigger With Specific Column
We want to fire a trigger only when the salary column changes.
CREATE OR REPLACE TRIGGER trg_salary_update
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
DBMS_OUTPUT.PUT_LINE('Salary changed from ' || :OLD.salary || ' to ' || :NEW.salary);
END;
Test:
UPDATE employees SET salary = salary + 500 WHERE employee_id = 100;
Example 4: Preventing Invalid Transactions
Let’s prevent any employee from having a salary over $20,000.
CREATE OR REPLACE TRIGGER trg_check_salary
BEFORE INSERT OR UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
IF :NEW.salary > 20000 THEN
RAISE_APPLICATION_ERROR(-20001, 'Salary cannot exceed $20,000');
END IF;
END;
Test:
UPDATE employees SET salary = 25000 WHERE employee_id = 101;
-- Error: ORA-20001: Salary cannot exceed $20,000
Example 5: INSTEAD OF Trigger on a View
Views don’t support DML operations unless you use INSTEAD OF triggers.
CREATE OR REPLACE VIEW emp_info_view AS
SELECT employee_id, first_name, last_name, department_id FROM employees;
CREATE OR REPLACE TRIGGER trg_view_insert
INSTEAD OF INSERT ON emp_info_view
FOR EACH ROW
BEGIN
INSERT INTO employees(employee_id, first_name, last_name, department_id, email, hire_date, job_id, salary)
VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name, :NEW.department_id,
'dummy@example.com', SYSDATE, 'IT_PROG', 5000);
END;
Test:
INSERT INTO emp_info_view(employee_id, first_name, last_name, department_id)
VALUES (220, 'Alice', 'Brown', 60);
Example 6: System Trigger on User Login
You can monitor user logins with system-level triggers.
CREATE OR REPLACE TRIGGER trg_login_audit
AFTER LOGON ON DATABASE
BEGIN
INSERT INTO login_audit(username, login_time)
VALUES (USER, SYSDATE);
END;
Note: Requires DBA privileges.
Best Practices for Triggers
- Keep triggers small and fast.
- Avoid complex logic in triggers; offload to procedures if needed.
- Be cautious of mutating table errors—don’t modify the triggering table from a row-level trigger.
- Document the purpose of each trigger clearly.
- Avoid unnecessary triggers that could be implemented using constraints.
Debugging Triggers
You can check trigger errors using:
SHOW ERRORS TRIGGER trigger_name;
Disable or enable triggers:
ALTER TRIGGER trg_salary_update DISABLE;
ALTER TRIGGER trg_salary_update ENABLE;
Drop trigger:
DROP TRIGGER trg_salary_update;
List all triggers:
SELECT trigger_name, table_name, status FROM user_triggers;
Real-World Use Cases
1. **Audit*: Log inserts, updates, deletes.
2. **Security**: Restrict changes to critical data.
3. **Data Transformation**: Automatically adjust data formats.
4. **Derived Columns**: Calculate values such as full name or totals.
5. **Soft Deletes**: Instead of deleting rows, mark them as inactive.
6. **Change Logging**: Track which user made which change and when.
7. **Data Replication**: Propagate changes to other tables.
8. **Workflow Triggers**: Kick off business processes like sending emails.
9. **Validation**: Ensure data meets complex business rules.
10. **Caching and History**: Store snapshots or versions of records.
Common Pitfalls
- **Mutating Table Errors**: Occur when you try to query or modify the table that’s already being modified.
- **Recursive Triggers**: Be careful of a trigger firing another trigger leading to infinite loops.
- **Performance Issues**: Triggers that run for every row can degrade performance.
- **Complex Dependencies**: Difficult to debug chains of triggers.
When Not to Use Triggers
- When you can achieve the same with declarative constraints.
- When external systems are better suited for handling business rules.
- When triggers would significantly increase execution time of DML operations.
Conclusion
Oracle PL/SQL triggers are a powerful mechanism for automating data integrity, enforcing business logic, and reacting to events in real-time. With great power comes great responsibility—triggers should be used judiciously to avoid performance degradation and maintenance complexity.
By mastering different types of triggers—BEFORE, AFTER, INSTEAD OF, and system—you can build robust and intelligent database applications that automatically respond to data changes. Through practical examples like audit logging, validation, and view manipulation, we’ve demonstrated how triggers can streamline business processes within the Oracle environment.
0 Comments