Oracle PL/SQL: Triggers

By Atul Rai | Last Updated: April 11, 2024 Previous

On this page, we’ll see how to create triggers in the Oracle database. Triggers are powerful database objects that automatically perform actions in response to specific database events. They can be used to enforce data integrity, maintain consistency, and automate business logic within your Oracle database.

Understanding Oracle Triggers

Oracle Triggers consist of three main components:

1. Trigger Event

This defines the database operation that activates the trigger. Common trigger events include INSERT, UPDATE, DELETE, or a combination of these operations on a specific table.

2. Trigger Condition

This specifies a condition that must be met for the trigger to execute. If the condition evaluates to true, the trigger fires; otherwise, it remains inactive and this is optional.

3. Trigger Action

This is the set of SQL statements or PL/SQL code that Oracle executes when the trigger fires. It can perform a wide range of actions, such as modifying data, logging information, or enforcing business rules.

Now, let’s proceed to create a basic trigger in Oracle with an example.

Example 1: Creating a Simple Before Insert Trigger

Suppose we have a table named “employees” with columns “emp_id” and “emp_name“, and we want to automatically generate a unique employee ID before inserting a new record into the table.

CREATE OR REPLACE TRIGGER before_insert_employee
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    :NEW.emp_id := 'EMP' || TO_CHAR(sysdate, 'YYYYMMDD') || '_' || 
                   LPAD(employee_seq.NEXTVAL, 3, '0');
END;
/

In this example:

• We create a trigger named “before_insert_employee” that fires before inserting a new row into the “employees” table.
• The “FOR EACH ROW” clause indicates that the trigger fires once for each affected row.
• The trigger action assigns a unique employee ID to the “:NEW.emp_id” column using a combination of the current date and a sequence called “employee_seq.”

Example 2: Creating an After Update Trigger with a Condition

Let’s consider a scenario where we want to log updates to the “salary” column of the “employees” table only if the new salary is greater than the previous salary.

CREATE OR REPLACE TRIGGER after_update_salary
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (new.salary > old.salary)
BEGIN
    INSERT INTO salary_log(emp_id, old_salary, new_salary, change_date)
    VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, SYSDATE);
END;
/

In this example:

• We create a trigger named “after_update_salary” that fires after updating the “salary” column of the “employees” table.
• The “WHEN” clause specifies a condition that must be met for the trigger to execute. Here, the trigger only fires if the new salary is greater than the old salary.
• The trigger action logs the salary change by inserting a record into the “salary_log” table, capturing the employee ID, old salary, new salary, and the change date.

Summary

Oracle Triggers provide a flexible mechanism for automating tasks and enforcing data integrity within your database.

References

1. CREATE TRIGGER Statement
2. Oracle: LIMIT Clause