Triggers are stored PL/SQL blocks associated with a table, schema, or database. They activate implicitly when a particular event occurs, such as a DML (Data Manipulation Language) statement, DDL (Data Definition Language) statement, or a database event.

Types of Triggers (DML, DDL, Database Event)

Oracle supports various types of triggers based on the events they respond to:

DML Triggers: These are the most common type and fire in response to INSERT, UPDATE, or DELETE statements on a table. They are crucial for implementing complex data validation rules or maintaining referential integrity beyond standard foreign key constraints.

DDL Triggers: These triggers activate in response to DDL statements like CREATE, ALTER, or DROP on database objects (tables, views, procedures, etc.). They are often used for auditing schema changes, preventing unauthorized modifications, or enforcing naming conventions.

Database Event Triggers: These triggers respond to database-level events, such as STARTUP, SHUTDOWN, LOGON, LOGOFF, or SERVERERROR. They can be used for tasks like logging session information, managing resource allocation, or handling specific error conditions.

Trigger Timing (BEFORE vs. AFTER)

Triggers can be set to fire either BEFORE or AFTER the triggering event:

BEFORE Triggers: These execute before the DML or DDL statement is applied to the database. They are ideal for data validation, manipulating data before it's committed, or generating primary keys. If a BEFORE trigger raises an error, the triggering statement is rolled back.

AFTER Triggers: These execute after the DML or DDL statement has been applied to the database. They are suitable for auditing changes, cascading updates, or performing actions that depend on the final state of the data after the triggering event.

Row-Level vs. Statement-Level Triggers

Triggers can also be classified by the granularity at which they fire:

Row-Level Triggers: These triggers fire once for each row affected by the DML statement. They are identified by the FOR EACH ROW clause in their definition. Row-level triggers are perfect for operations that depend on the individual data values of the affected rows, such as data validation or auditing specific column changes.

Statement-Level Triggers: These triggers fire only once per DML statement, regardless of how many rows are affected. They do not use the FOR EACH ROW clause. Statement-level triggers are suitable for tasks that need to be performed once per operation, such as logging the fact that an UPDATE statement occurred, or controlling access to a table based on certain conditions.

Using :OLD and :NEW

Within row-level DML triggers, Oracle provides two pseudo-records, :OLD and :NEW, which allow you to access the data values before and after the triggering event:

:OLD: Represents the values of the columns in a row before the INSERT, UPDATE, or DELETE operation. For INSERT operations, :OLD values are null. For DELETE operations, :NEW values are null.

:NEW: Represents the values of the columns in a row after the INSERT or UPDATE operation. For DELETE operations, :NEW values are null.

These pseudo-records are incredibly powerful for implementing complex business logic, such as tracking changes, validating data transitions, or enforcing conditional updates.

Example 1: Oracle PL/SQL DML Trigger - BEFORE INSERT (Row-Level)

-- Create a sample table for demonstration
CREATE TABLE employees (
    employee_id NUMBER PRIMARY KEY,
    first_name VARCHAR2(50),
    last_name VARCHAR2(50),
    email VARCHAR2(100),
    hire_date DATE,
    salary NUMBER(10, 2)
);

-- Create a sequence for employee_id
CREATE SEQUENCE employee_id_seq START WITH 1 INCREMENT BY 1;

-- Trigger to automatically populate employee_id and set hire_date before insert
CREATE OR REPLACE TRIGGER trg_before_employee_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    -- Check if employee_id is null, then populate from sequence
    IF :NEW.employee_id IS NULL THEN
        SELECT employee_id_seq.NEXTVAL INTO :NEW.employee_id FROM DUAL;
    END IF;

    -- Set hire_date to the current system date if not provided
    IF :NEW.hire_date IS NULL THEN
        :NEW.hire_date := SYSDATE;
    END IF;

    -- Convert email to lowercase for standardization
    :NEW.email := LOWER(:NEW.email);

    -- Add a comment for demonstration of a validation check (optional)
    -- This part could be expanded for more complex validations
    IF :NEW.salary < 0 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Employee salary cannot be negative.');
    END IF;
END;
/

-- Test cases for the trigger
INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('John', 'Doe', 'JOHN.DOE@EXAMPLE.COM', 60000);

INSERT INTO employees (employee_id, first_name, last_name, email, hire_date, salary)
VALUES (101, 'Jane', 'Smith', 'JANE.SMITH@COMPANY.COM', TO_DATE('2023-01-15', 'YYYY-MM-DD'), 75000);

INSERT INTO employees (first_name, last_name, email, salary)
VALUES ('Alice', 'Brown', 'ALICE.BROWN@EXAMPLE.COM', 50000);

-- This insert will fail due to the salary validation
-- INSERT INTO employees (first_name, last_name, email, salary)
-- VALUES ('Bob', 'White', 'BOB.WHITE@EXAMPLE.COM', -100);

SELECT employee_id, first_name, last_name, email, hire_date, salary FROM employees;

-- Clean up
-- DROP TRIGGER trg_before_employee_insert;
-- DROP SEQUENCE employee_id_seq;
-- DROP TABLE employees;

Explanation

This example demonstrates a BEFORE INSERT row-level DML trigger named trg_before_employee_insert. This trigger fires just before a new row is inserted into the employees table. For each row being inserted, it performs three key actions:

Automatic ID Generation: If the employee_id is not provided (i.e., it's NULL), the trigger automatically retrieves the next value from the employee_id_seq sequence and assigns it to :NEW.employee_id. This ensures that every new employee gets a unique ID.

Default Hire Date: If the hire_date is not specified, the trigger automatically sets it to the current system date (SYSDATE). This simplifies data entry and ensures a default value.

Email Standardization: It converts the email address to lowercase, promoting data consistency.

Basic Validation: It includes a simple validation to prevent the insertion of an employee with a negative salary, raising an application error if this condition is met.

The FOR EACH ROW clause is crucial here, as it signifies that the trigger's body will execute for every single row that is part of the INSERT statement, allowing access to the :NEW pseudo-record to modify the data before it's stored. This type of trigger is excellent for pre-processing data, enforcing business rules, and ensuring data quality upon insertion.

Example 2: Oracle PL/SQL DML Trigger - AFTER UPDATE (Row-Level) for Auditing

-- Create a table to store audit logs for salary changes
CREATE TABLE salary_audit (
    audit_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    employee_id NUMBER,
    old_salary NUMBER(10, 2),
    new_salary NUMBER(10, 2),
    change_date TIMESTAMP DEFAULT SYSTIMESTAMP,
    changed_by VARCHAR2(100) DEFAULT USER
);

-- Trigger to log salary changes after an update
CREATE OR REPLACE TRIGGER trg_after_salary_update
AFTER UPDATE OF salary ON employees
FOR EACH ROW
WHEN (OLD.salary IS DISTINCT FROM NEW.salary)
BEGIN
    -- Only log if the salary has actually changed
    INSERT INTO salary_audit (employee_id, old_salary, new_salary)
    VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary);

    -- You could also add logging for who made the change and when
    -- INSERT INTO salary_audit (employee_id, old_salary, new_salary, change_date, changed_by)
    -- VALUES (:OLD.employee_id, :OLD.salary, :NEW.salary, SYSTIMESTAMP, USER);
END;
/

-- Update some salaries to trigger the audit
UPDATE employees SET salary = 65000 WHERE employee_id = (SELECT employee_id FROM employees WHERE first_name = 'John' AND last_name = 'Doe');
UPDATE employees SET salary = 80000 WHERE employee_id = (SELECT employee_id FROM employees WHERE first_name = 'Jane' AND last_name = 'Smith');

-- This update will not trigger the audit as salary is not changed
UPDATE employees SET first_name = 'Jonathan' WHERE employee_id = (SELECT employee_id FROM employees WHERE first_name = 'John' AND last_name = 'Doe');

SELECT employee_id, old_salary, new_salary, change_date, changed_by FROM salary_audit;

-- Clean up
-- DROP TRIGGER trg_after_salary_update;
-- DROP TABLE salary_audit;
-- DROP TABLE employees; -- Assuming you drop this from previous example
-- DROP SEQUENCE employee_id_seq; -- Assuming you drop this from previous example

Explanation

This example showcases an AFTER UPDATE row-level DML trigger, trg_after_salary_update, designed for auditing salary changes in the employees table. This trigger specifically fires after an UPDATE statement affects the salary column.

Here's how it works:

Specific Column Trigger: The OF salary clause ensures the trigger only fires when the salary column is updated, optimizing performance.

Conditional Firing (WHEN clause): The WHEN (OLD.salary IS DISTINCT FROM NEW.salary) clause is a powerful feature that ensures the trigger's body only executes if the salary value has genuinely changed. This prevents unnecessary audit entries if an update statement is issued but the salary value remains the same.

Auditing: Inside the trigger, an INSERT statement is executed into the salary_audit table. This record captures the employee_id, the old_salary (value before the update), and the new_salary (value after the update). It also automatically logs the change_date and changed_by (the user who made the change) thanks to the table's default values.

This type of trigger is invaluable for creating robust audit trails, ensuring compliance, and providing historical data for critical changes. The :OLD and :NEW pseudo-records are essential here to capture both the pre- and post-update values for comparison and logging.

Example 3: Oracle PL/SQL DDL Trigger - Auditing Schema Changes

-- Create a table to log DDL operations
CREATE TABLE ddl_audit_log (
    log_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    event_type VARCHAR2(50),
    object_type VARCHAR2(50),
    object_name VARCHAR2(100),
    sql_text CLOB,
    event_date TIMESTAMP DEFAULT SYSTIMESTAMP,
    event_user VARCHAR2(100) DEFAULT USER,
    host_name VARCHAR2(100)
);

-- DDL Trigger to log CREATE, ALTER, and DROP statements
CREATE OR REPLACE TRIGGER trg_audit_ddl_operations
AFTER DDL ON SCHEMA
BEGIN
    INSERT INTO ddl_audit_log (event_type, object_type, object_name, sql_text, host_name)
    VALUES (ORA_SYSEVENT, ORA_DICT_OBJ_TYPE, ORA_DICT_OBJ_NAME, ORA_SQL_TEXT, SYS_CONTEXT('USERENV', 'HOST'));
END;
/

-- Test cases for the DDL trigger
-- This will be logged
CREATE TABLE test_table_ddl (
    id NUMBER PRIMARY KEY,
    name VARCHAR2(50)
);

-- This will be logged
ALTER TABLE test_table_ddl ADD (description VARCHAR2(200));

-- This will be logged
CREATE VIEW test_view_ddl AS SELECT id, name FROM test_table_ddl;

-- This will be logged
DROP TABLE test_table_ddl;

-- This will be logged
DROP VIEW test_view_ddl;

SELECT event_type, object_type, object_name, event_date, event_user FROM ddl_audit_log ORDER BY event_date DESC;

-- Clean up (optional, as the trigger logs its own drops too)
-- DROP TRIGGER trg_audit_ddl_operations;
-- DROP TABLE ddl_audit_log;

Explanation

This example demonstrates a DDL trigger, trg_audit_ddl_operations, defined ON SCHEMA. This means the trigger will fire whenever a DDL statement (like CREATE, ALTER, DROP) is executed within the current schema. It's a statement-level trigger, as it doesn't specify FOR EACH ROW.

Here's a breakdown of its functionality:

DDL Event Response: The AFTER DDL ON SCHEMA clause makes this trigger respond to any DDL operation on objects within the schema.

Auditing DDL Operations: The trigger inserts a record into the ddl_audit_log table, capturing critical information about the DDL event:

ORA_SYSEVENT: Returns the type of system event that fired the trigger (e.g., 'CREATE', 'ALTER', 'DROP').

ORA_DICT_OBJ_TYPE: Returns the type of the dictionary object on which the DDL operation was performed (e.g., 'TABLE', 'VIEW').

ORA_DICT_OBJ_NAME: Returns the name of the dictionary object.

ORA_SQL_TEXT: Returns the full SQL text of the DDL statement that was executed. This is incredibly useful for forensic analysis.

SYS_CONTEXT('USERENV', 'HOST'): Retrieves the hostname from which the DDL operation was initiated.

event_date and event_user are automatically populated by the table's default values.

DDL triggers are powerful for maintaining database security, auditing schema changes, and ensuring compliance. They provide an invaluable record of structural modifications to your database.

Example 4: Oracle PL/SQL Database Event Trigger - LOGON Auditing

-- Create a table to log user logon events
CREATE TABLE logon_audit_log (
    log_id NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY PRIMARY KEY,
    username VARCHAR2(100),
    logon_time TIMESTAMP DEFAULT SYSTIMESTAMP,
    os_user VARCHAR2(100),
    host_name VARCHAR2(100),
    ip_address VARCHAR2(50),
    program_name VARCHAR2(200)
);

-- Database event trigger to log user logons
CREATE OR REPLACE TRIGGER trg_audit_logons
AFTER LOGON ON DATABASE
BEGIN
    INSERT INTO logon_audit_log (username, os_user, host_name, ip_address, program_name)
    VALUES (
        USER,
        SYS_CONTEXT('USERENV', 'OS_USER'),
        SYS_CONTEXT('USERENV', 'HOST'),
        SYS_CONTEXT('USERENV', 'IP_ADDRESS'),
        SYS_CONTEXT('USERENV', 'MODULE')
    );
END;
/

-- To test this trigger, you would need to disconnect and reconnect to the database.
-- Each successful logon will insert a record into logon_audit_log.

-- Example: Disconnect from SQL Developer/SQL*Plus and reconnect with a different user or the same user.

-- After reconnecting, query the audit log:
-- SELECT username, logon_time, os_user, host_name, ip_address, program_name FROM logon_audit_log ORDER BY logon_time DESC;

-- Clean up
-- DROP TRIGGER trg_audit_logons;
-- DROP TABLE logon_audit_log;

Explanation

This example illustrates a DATABASE event trigger, trg_audit_logons, which fires AFTER LOGON on the entire database. This means it executes every time a user successfully logs into the Oracle database.

Here's how it functions:

Logon Event Response: The AFTER LOGON ON DATABASE clause ensures this trigger activates immediately after a user's session is established.

Session Information Capture: The trigger inserts a record into the logon_audit_log table, capturing various details about the user's session:

USER: The Oracle username that logged in.

SYS_CONTEXT('USERENV', 'OS_USER'): The operating system user that initiated the connection.

SYS_CONTEXT('USERENV', 'HOST'): The hostname of the client machine.

SYS_CONTEXT('USERENV', 'IP_ADDRESS'): The IP address of the client machine.

SYS_CONTEXT('USERENV', 'MODULE'): The name of the application or module from which the connection originated (e.g., 'SQL Developer', 'JDBC Thin Client').

logon_time is automatically populated by the table's default SYSTIMESTAMP.

Database event triggers like this are invaluable for security monitoring, tracking user activity, and understanding database usage patterns. They provide crucial insights into who is connecting, from where, and using which applications.

Example 5: Oracle PL/SQL DML Trigger - Complex Data Validation and Cascading Update

-- Create a department table
CREATE TABLE departments (
    dept_id NUMBER PRIMARY KEY,
    dept_name VARCHAR2(100) UNIQUE NOT NULL
);

-- Insert some departments
INSERT INTO departments (dept_id, dept_name) VALUES (10, 'Sales');
INSERT INTO departments (dept_id, dept_name) VALUES (20, 'Marketing');
INSERT INTO departments (dept_id, dept_name) VALUES (30, 'IT');

-- Modify the employees table to include dept_id
ALTER TABLE employees ADD (dept_id NUMBER REFERENCES departments(dept_id));

-- Update existing employees with department IDs
UPDATE employees SET dept_id = 10 WHERE employee_id = (SELECT employee_id FROM employees WHERE first_name = 'John' AND last_name = 'Doe');
UPDATE employees SET dept_id = 20 WHERE employee_id = (SELECT employee_id FROM employees WHERE first_name = 'Jane' AND last_name = 'Smith');
UPDATE employees SET dept_id = 10 WHERE employee_id = (SELECT employee_id FROM employees WHERE first_name = 'Alice' AND last_name = 'Brown');


-- Create a trigger to validate department assignment and cascade updates on dept_id changes
CREATE OR REPLACE TRIGGER trg_employee_dept_management
BEFORE INSERT OR UPDATE OF dept_id ON employees
FOR EACH ROW
DECLARE
    v_dept_count NUMBER;
BEGIN
    -- Validation: Ensure the department ID exists
    IF :NEW.dept_id IS NOT NULL THEN
        SELECT COUNT(*)
        INTO v_dept_count
        FROM departments
        WHERE dept_id = :NEW.dept_id;

        IF v_dept_count = 0 THEN
            RAISE_APPLICATION_ERROR(-20002, 'Invalid Department ID: ' || :NEW.dept_id || '. Department does not exist.');
        END IF;
    END IF;

    -- Cascading Action (on update of dept_id): Log change to audit if needed (can be combined with previous audit trigger or done here)
    -- For demonstration, let's say we want to set a default for a new employee if a specific department is not provided
    -- This is a 'BEFORE' trigger, so we can modify :NEW
    IF INSERTING AND :NEW.dept_id IS NULL THEN
        -- Example: Assign to 'Unassigned' department if exists, or a default.
        -- For simplicity, let's default to a specific department if null on insert
        -- This part requires careful design based on business logic.
        -- For this example, let's ensure it's not null, or let the previous validation catch it.
        NULL; -- No specific action here for this example, the validation handles NULL or invalid IDs.
    END IF;

    -- Example of a cascading update based on a condition (this would be more common in AFTER trigger,
    -- but shown here for conceptual understanding of logic in BEFORE)
    -- If an employee's salary is less than 50000 and they are moved to IT, automatically increase salary (simplified example)
    -- This is generally not recommended for complex business rules as it can obscure logic, but possible.
    IF UPDATING('DEPT_ID') AND :OLD.dept_id != :NEW.dept_id THEN
        -- Consider logging this department change if not already done by a general update audit
        -- For instance: INSERT INTO dept_change_audit ...
        -- Example of a complex cascading logic: if moved to IT and salary is low, bump it up
        IF :NEW.dept_id = (SELECT dept_id FROM departments WHERE dept_name = 'IT') AND :NEW.salary < 55000 THEN
            :NEW.salary := 55000; -- Directly modify the new salary
        END IF;
    END IF;

END;
/

-- Test cases
-- This insert will fail due to invalid department ID
-- INSERT INTO employees (first_name, last_name, email, salary, dept_id)
-- VALUES ('Frank', 'Green', 'FRANK.GREEN@EXAMPLE.COM', 45000, 99);

-- This insert will succeed and employee_id/hire_date will be set by Example 1 trigger
INSERT INTO employees (first_name, last_name, email, salary, dept_id)
VALUES ('Olivia', 'White', 'OLIVIA.WHITE@EXAMPLE.COM', 48000, 30); -- Automatically sets salary to 55000 due to IT dept

-- Update an employee's department to IT (and trigger salary adjustment if applicable)
UPDATE employees SET dept_id = 30 WHERE first_name = 'John' AND last_name = 'Doe'; -- John's salary was 65000, no change.
UPDATE employees SET dept_id = 30 WHERE first_name = 'Alice' AND last_name = 'Brown'; -- Alice's salary was 50000, should change to 55000.

SELECT employee_id, first_name, last_name, salary, dept_id FROM employees ORDER BY employee_id;

-- Clean up
-- DROP TRIGGER trg_employee_dept_management;
-- DROP TABLE departments;
-- ALTER TABLE employees DROP COLUMN dept_id;
-- DROP TABLE employees; -- Assuming previous cleanup will be done
-- DROP SEQUENCE employee_id_seq; -- Assuming previous cleanup will be done
-- DROP TABLE salary_audit; -- Assuming previous cleanup will be done
-- DROP TABLE ddl_audit_log; -- Assuming previous cleanup will be done
-- DROP TABLE logon_audit_log; -- Assuming previous cleanup will be done

Explanation

This example demonstrates a more complex BEFORE INSERT OR UPDATE row-level DML trigger, trg_employee_dept_management, on the employees table. It specifically focuses on managing the dept_id column and showcases both data validation and a conceptual cascading action within a BEFORE trigger.

Here's a breakdown of its logic:

Trigger on Multiple Operations: The BEFORE INSERT OR UPDATE OF dept_id clause means the trigger will fire before an INSERT statement or before an UPDATE statement that specifically modifies the dept_id column.

Department Existence Validation:

It checks if the :NEW.dept_id (the proposed new department ID) actually exists in the departments table.

If the dept_id is provided but does not correspond to an existing department, it raises an RAISE_APPLICATION_ERROR, preventing the invalid data from being inserted or updated. This is a critical use of BEFORE triggers for data integrity.

Conditional Cascading Action (Conceptual):

The IF UPDATING('DEPT_ID') AND :OLD.dept_id != :NEW.dept_id block checks if the dept_id is actually being changed.

Inside this block, a conceptual cascading update is shown: if an employee is moved to the 'IT' department (using a subquery to find its dept_id) and their current salary is below 55000, their :NEW.salary is automatically adjusted to 55000.

Important Note: While possible, performing complex cascading logic directly within a BEFORE trigger that modifies other :NEW values should be done with caution. For very intricate business rules, sometimes an AFTER trigger combined with a separate procedure might be clearer, or even application-level logic. However, this demonstrates the capability of modifying :NEW values in a BEFORE trigger.

This example highlights how triggers can enforce intricate business rules, ensure referential integrity (even beyond standard foreign key constraints), and automate data adjustments based on complex conditions, contributing significantly to data quality and system automation.