How to Use Triggers in Oracle Database 23ai PL/SQL

Triggers in Oracle Database 23ai are powerful database objects that automatically execute when specific database events occur. They are closely tied to tables, views, or schema-level activities and help enforce rules, maintain audit trails, and automate business logic.

In this Oracle tutorial, you will learn what triggers are, the different types available in Oracle Database 23ai, and how to create, manage, and test them with practical examples. Along the way, you will also see how new features of Oracle 23ai enhance trigger development and management.

Understanding Triggers in Oracle Database 23ai

A trigger is a stored PL/SQL block that executes automatically in response to an event, such as an INSERT, UPDATE, or DELETE statement. Triggers help reduce manual intervention by automating logic at the database layer. Unlike procedures or functions, you do not explicitly call triggers—they are invoked by Oracle when the event occurs.

In Oracle Database 23ai, triggers can be associated with tables, views, schema-level events, or even system-level operations. The new AI-driven enhancements in Oracle 23ai, such as adaptive execution and better diagnostics through AI Vector Search integration, also benefit trigger usage by improving performance insights and enabling hybrid transactional and analytical workloads (HTAP).

Types of Triggers in Oracle Database 23ai

Oracle Database 23ai supports multiple categories of triggers, giving developers fine-grained control over execution timing and scope. The most common categories include:

• Row-level triggers: Fire once for each row affected by the triggering statement.
• Statement-level triggers: Fire once per statement, regardless of how many rows are affected.
• BEFORE triggers: Execute before the triggering statement completes.
• AFTER triggers: Execute after the triggering statement completes.
• INSTEAD OF triggers: Typically used with views to define alternative actions.
• Compound triggers: Allow grouping multiple trigger sections into one object, reducing overhead and improving maintainability.

Oracle 23ai continues to support compound triggers with performance enhancements, making them especially useful for complex business rules where both row-level and statement-level logic is needed in one place.

Preparing Sample Data for Trigger Demonstrations

Before working with triggers, let us create a sample table and populate it with some data. We will also create an audit table to capture trigger actions.

-- Create a table to store employee data
CREATE TABLE employees (
    emp_id       NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    first_name   VARCHAR2(50),
    last_name    VARCHAR2(50),
    salary       NUMBER(10,2),
    department   VARCHAR2(50)
);

-- Insert some sample employees
INSERT INTO employees (first_name, last_name, salary, department)
VALUES ('John', 'Doe', 60000, 'IT');

INSERT INTO employees (first_name, last_name, salary, department)
VALUES ('Jane', 'Smith', 75000, 'HR');

COMMIT;

-- Create an audit table for tracking salary updates
CREATE TABLE employee_audit (
    audit_id     NUMBER GENERATED ALWAYS AS IDENTITY PRIMARY KEY,
    emp_id       NUMBER,
    old_salary   NUMBER(10,2),
    new_salary   NUMBER(10,2),
    change_date  TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    changed_by   VARCHAR2(30)
);

This setup provides a working dataset where we can apply triggers to automate auditing and enforce rules.

Creating a BEFORE Row-Level Trigger

BEFORE triggers are often used to validate data before it is written to the database. For example, let us enforce a business rule that prevents inserting employees with a salary less than 30,000.

CREATE OR REPLACE TRIGGER trg_check_salary
BEFORE INSERT OR UPDATE ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < 30000 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary must be at least 30,000');
    END IF;
END;

Test the Trigger

-- Attempt to insert an employee with a low salary
INSERT INTO employees (first_name, last_name, salary, department)
VALUES ('Alice', 'Brown', 25000, 'Finance');

Result:

ORA-20001: Salary must be at least 30,000

This confirms the trigger is working as expected and prevents invalid data from entering the table.

Creating an AFTER Row-Level Trigger for Auditing

AFTER triggers are useful for logging changes after the modification has occurred. Let us create one that records salary changes in the employee_audit table.

CREATE OR REPLACE TRIGGER trg_audit_salary
AFTER UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    INSERT INTO employee_audit (emp_id, old_salary, new_salary, changed_by)
    VALUES (:OLD.emp_id, :OLD.salary, :NEW.salary, SYS_CONTEXT('USERENV', 'SESSION_USER'));
END;

Test the Trigger

-- Update an employee's salary
UPDATE employees
SET salary = 80000
WHERE emp_id = 1;

COMMIT;

-- Check audit records
SELECT * FROM employee_audit;

Result:

The audit trail is maintained automatically by the trigger.

Using INSTEAD OF Triggers with Views

Sometimes, updates on complex views are not directly possible. INSTEAD OF triggers let you define alternative actions.

-- Create a view on employees
CREATE OR REPLACE VIEW emp_view AS
SELECT emp_id, first_name || ' ' || last_name AS full_name, salary
FROM employees;

-- Create INSTEAD OF trigger
CREATE OR REPLACE TRIGGER trg_instead_of
INSTEAD OF UPDATE ON emp_view
FOR EACH ROW
BEGIN
    UPDATE employees
    SET salary = :NEW.salary
    WHERE emp_id = :OLD.emp_id;
END;

Now you can update the view directly:

UPDATE emp_view
SET salary = 90000
WHERE emp_id = 2;

The trigger ensures the underlying employees table is updated correctly.

Compound Triggers in Oracle 23ai

Compound triggers reduce overhead by combining multiple timing points (BEFORE, AFTER, etc.) in one object. This is especially useful when maintaining state across row-level and statement-level events.

CREATE OR REPLACE TRIGGER trg_compound_example
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER

    TYPE t_emp_ids IS TABLE OF employees.emp_id%TYPE;
    l_emp_ids t_emp_ids := t_emp_ids();

BEFORE STATEMENT IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Salary update process started');
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
    IF :NEW.salary < :OLD.salary THEN
        RAISE_APPLICATION_ERROR(-20002, 'Salary cannot be decreased');
    END IF;
    l_emp_ids.EXTEND;
    l_emp_ids(l_emp_ids.LAST) := :NEW.emp_id;
END BEFORE EACH ROW;

AFTER STATEMENT IS
BEGIN
    DBMS_OUTPUT.PUT_LINE('Updated employees: ' || l_emp_ids.COUNT);
END AFTER STATEMENT;

END trg_compound_example;

This compound trigger validates that salaries are not reduced and also provides feedback about how many employees were updated in a single operation.

Managing and Disabling Triggers

There may be times when you need to disable or enable triggers, for example during bulk data loads. Oracle 23ai makes it easy with these commands:

-- Disable trigger
ALTER TRIGGER trg_audit_salary DISABLE;

-- Enable trigger
ALTER TRIGGER trg_audit_salary ENABLE;

-- Drop trigger
DROP TRIGGER trg_instead_of;

Disabling triggers temporarily can improve performance during large operations, but care should be taken to re-enable them afterward to maintain data integrity.

Performance Considerations in Oracle 23ai

Triggers add overhead since they execute additional logic whenever a triggering event occurs. Oracle Database 23ai helps optimize this by:

• Enhancing compound trigger execution efficiency.
• Offering better diagnostic views (DBA_TRIGGERS, ALL_TRIGGERS) with AI-driven insights.
• Improved scalability in hybrid transactional and analytical processing scenarios where triggers are used for auditing or maintaining derived data.

To avoid unnecessary overhead, only use triggers when business logic cannot be easily enforced through constraints or application logic.

Conclusion

Triggers in Oracle Database 23ai remain a powerful tool for automating database logic, enforcing rules, and maintaining audit trails. With support for BEFORE, AFTER, INSTEAD OF, and compound triggers, developers have flexible options for tailoring behavior to business requirements.

See also:

• How to Install Oracle Database 23ai on Windows
• Installing Oracle SQL Developer 24 on Windows
• How to Install Oracle APEX 24.2 on CDB in Windows