This section delves into advanced Oracle PL/SQL trigger concepts, crucial for mastering database event automation and maintaining data integrity. We'll explore powerful trigger types like Compound Triggers and INSTEAD OF Triggers on Views, along with techniques for managing trigger execution order.

Compound Triggers

Compound Triggers in Oracle PL/SQL offer a unified approach to handle multiple timing points (BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, AFTER STATEMENT) within a single trigger body. This significantly improves code readability, maintainability, and often, performance, by allowing shared logic and state across different trigger events. Traditional triggers require separate trigger definitions for each timing point, leading to potential code duplication and complex state management. Compound triggers, introduced in Oracle Database 11g, simplify complex trigger logic by providing a single programmatic construct.

Example 1: Basic Compound Trigger for Auditing

CREATE TABLE employee_audit (
    audit_id        NUMBER GENERATED BY DEFAULT AS IDENTITY,
    employee_id     NUMBER,
    old_salary      NUMBER,
    new_salary      NUMBER,
    change_date     TIMESTAMP,
    changed_by      VARCHAR2(100)
);

CREATE OR REPLACE TRIGGER trg_employee_salary_audit
FOR UPDATE OF salary ON employees
COMPOUND TRIGGER
    -- Variables declared here are accessible across all timing points
    l_old_salary NUMBER;
    l_new_salary NUMBER;

    BEFORE EACH ROW IS
    BEGIN
        -- Capture the old salary before the row is updated
        l_old_salary := :OLD.salary;
    END BEFORE EACH ROW;

    AFTER EACH ROW IS
    BEGIN
        -- Capture the new salary after the row is updated
        l_new_salary := :NEW.salary;

        -- Insert audit record after each row is processed
        INSERT INTO employee_audit (employee_id, old_salary, new_salary, change_date, changed_by)
        VALUES (:OLD.employee_id, l_old_salary, l_new_salary, SYSTIMESTAMP, USER);
    END AFTER EACH ROW;

END trg_employee_salary_audit;
/

-- Test the trigger
UPDATE employees SET salary = salary * 1.05 WHERE employee_id = 100;
SELECT * FROM employee_audit WHERE employee_id = 100;

Explanation

This example demonstrates a basic compound trigger named trg_employee_salary_audit that fires on updates to the salary column of the employees table. It utilizes both BEFORE EACH ROW and AFTER EACH ROW sections. The l_old_salary and l_new_salary variables are declared in the compound trigger's declarative section, making them accessible to all timing point sections. In the BEFORE EACH ROW section, the original salary (:OLD.salary) is captured. In the AFTER EACH ROW section, the new salary (:NEW.salary) is captured, and an audit record is inserted into the employee_audit table. This unified structure ensures consistent access to data and simplifies the auditing logic.

Example 2: Managing Row-Level and Statement-Level Logic with Compound Triggers

CREATE TABLE product_stock (
    product_id   NUMBER PRIMARY KEY,
    product_name VARCHAR2(100),
    stock_qty    NUMBER
);

INSERT INTO product_stock VALUES (1, 'Laptop', 50);
INSERT INTO product_stock VALUES (2, 'Mouse', 200);

CREATE TABLE low_stock_alerts (
    alert_id      NUMBER GENERATED BY DEFAULT AS IDENTITY,
    product_id    NUMBER,
    current_stock NUMBER,
    alert_date    TIMESTAMP
);

CREATE OR REPLACE TRIGGER trg_product_stock_management
FOR UPDATE OF stock_qty ON product_stock
COMPOUND TRIGGER
    -- Collection to hold product IDs that went below threshold
    TYPE t_product_id_list IS TABLE OF product_stock.product_id%TYPE INDEX BY PLS_INTEGER;
    l_low_stock_products t_product_id_list;
    l_alert_threshold CONSTANT NUMBER := 10;

    AFTER EACH ROW IS
    BEGIN
        -- Check if stock fell below threshold for the current row
        IF :NEW.stock_qty < l_alert_threshold AND :OLD.stock_qty >= l_alert_threshold THEN
            l_low_stock_products(l_low_stock_products.COUNT + 1) := :NEW.product_id;
        END IF;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
    BEGIN
        -- After all rows are processed, insert alerts for products that are low in stock
        FOR i IN 1 .. l_low_stock_products.COUNT LOOP
            INSERT INTO low_stock_alerts (product_id, current_stock, alert_date)
            SELECT product_id, stock_qty, SYSTIMESTAMP
            FROM product_stock
            WHERE product_id = l_low_stock_products(i);
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('Processed ' || l_low_stock_products.COUNT || ' low stock alerts.');
    END AFTER STATEMENT;

END trg_product_stock_management;
/

-- Enable DBMS_OUTPUT for testing
SET SERVEROUTPUT ON;

-- Test the trigger
UPDATE product_stock SET stock_qty = 5 WHERE product_id = 1;
UPDATE product_stock SET stock_qty = 15 WHERE product_id = 2; -- This will not trigger an alert
UPDATE product_stock SET stock_qty = 8 WHERE product_id = 2;
SELECT * FROM low_stock_alerts;

Explanation

This example showcases how a compound trigger can manage both row-level and statement-level logic efficiently. The trg_product_stock_management trigger monitors updates to stock_qty in the product_stock table. In the AFTER EACH ROW section, it identifies products whose stock falls below a predefined l_alert_threshold. Instead of inserting an alert for each row immediately (which could be inefficient if many rows are updated), it stores the product_id in a collection l_low_stock_products. The AFTER STATEMENT section then iterates through this collection once after all row-level operations are complete, inserting a single alert for each product that met the low-stock criteria. This demonstrates how compound triggers can optimize operations by consolidating statement-level actions.

Example 3: Preventing Mutating Table Errors with Compound Triggers

CREATE TABLE sales (
    sale_id       NUMBER PRIMARY KEY,
    product_id    NUMBER,
    sale_date     DATE,
    quantity      NUMBER,
    unit_price    NUMBER
);

CREATE TABLE product_summary (
    product_id    NUMBER PRIMARY KEY,
    total_sales   NUMBER,
    last_sale_date DATE
);

INSERT INTO product_summary VALUES (101, 0, NULL);
INSERT INTO product_summary VALUES (102, 0, NULL);

-- Simulate mutating table issue if using separate triggers
-- CREATE OR REPLACE TRIGGER trg_sales_update_product_summary_each_row
-- AFTER INSERT ON sales
-- FOR EACH ROW
-- BEGIN
--     UPDATE product_summary
--     SET total_sales = total_sales + (:NEW.quantity * :NEW.unit_price)
--     WHERE product_id = :NEW.product_id;
-- END;
-- /

CREATE OR REPLACE TRIGGER trg_sales_compound_summary
FOR INSERT ON sales
COMPOUND TRIGGER
    TYPE t_sales_data IS TABLE OF sales%ROWTYPE INDEX BY PLS_INTEGER;
    l_new_sales t_sales_data;
    l_idx       PLS_INTEGER := 0;

    AFTER EACH ROW IS
    BEGIN
        -- Store the new sale record in a collection
        l_idx := l_idx + 1;
        l_new_sales(l_idx) := :NEW;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
    BEGIN
        -- After all rows are inserted, process the summary updates
        FOR i IN 1 .. l_new_sales.COUNT LOOP
            UPDATE product_summary
            SET total_sales = total_sales + (l_new_sales(i).quantity * l_new_sales(i).unit_price),
                last_sale_date = l_new_sales(i).sale_date
            WHERE product_id = l_new_sales(i).product_id;
        END LOOP;
    END AFTER STATEMENT;
END trg_sales_compound_summary;
/

-- Test the trigger
INSERT INTO sales (sale_id, product_id, sale_date, quantity, unit_price) VALUES (1, 101, SYSDATE, 2, 10);
INSERT INTO sales (sale_id, product_id, sale_date, quantity, unit_price) VALUES (2, 102, SYSDATE, 1, 25);
INSERT INTO sales (sale_id, product_id, sale_date, quantity, unit_price) VALUES (3, 101, SYSDATE, 3, 10);

SELECT * FROM product_summary;

Explanation

This example illustrates how compound triggers effectively circumvent the "mutating table" error. A mutating table error occurs when a row-level trigger tries to query or modify the table on which it is firing. Here, trg_sales_compound_summary updates product_summary based on sales data. If we were to update product_summary in an AFTER EACH ROW trigger, it would cause a mutating table error because sales is being modified. By using a compound trigger, the AFTER EACH ROW section stores the relevant new sale data into a collection (l_new_sales). The actual UPDATE to product_summary is deferred to the AFTER STATEMENT section, which executes after the INSERT statement on sales has completed, thus avoiding the mutating table issue. This is a common and powerful application of compound triggers in Oracle PL/SQL development.

Example 4: Using BEFORE STATEMENT in Compound Trigger for Global Checks

CREATE TABLE orders (
    order_id      NUMBER PRIMARY KEY,
    customer_id   NUMBER,
    order_date    DATE,
    total_amount  NUMBER
);

CREATE TABLE order_logs (
    log_id        NUMBER GENERATED BY DEFAULT AS IDENTITY,
    action_type   VARCHAR2(10),
    log_message   VARCHAR2(255),
    log_date      TIMESTAMP
);

CREATE OR REPLACE TRIGGER trg_order_management
FOR INSERT OR UPDATE OR DELETE ON orders
COMPOUND TRIGGER
    -- Global variable for this trigger's execution
    l_operation_start_time TIMESTAMP;

    BEFORE STATEMENT IS
    BEGIN
        l_operation_start_time := SYSTIMESTAMP;
        INSERT INTO order_logs (action_type, log_message, log_date)
        VALUES ('INFO', 'Transaction on ORDERS table started.', l_operation_start_time);
    END BEFORE STATEMENT;

    AFTER EACH ROW IS
    BEGIN
        IF INSERTING THEN
            INSERT INTO order_logs (action_type, log_message, log_date)
            VALUES ('INSERT', 'Order ' || :NEW.order_id || ' inserted.', SYSTIMESTAMP);
        ELSIF UPDATING THEN
            INSERT INTO order_logs (action_type, log_message, log_date)
            VALUES ('UPDATE', 'Order ' || :NEW.order_id || ' updated.', SYSTIMESTAMP);
        ELSIF DELETING THEN
            INSERT INTO order_logs (action_type, log_message, log_date)
            VALUES ('DELETE', 'Order ' || :OLD.order_id || ' deleted.', SYSTIMESTAMP);
        END IF;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
    BEGIN
        INSERT INTO order_logs (action_type, log_message, log_date)
        VALUES ('INFO', 'Transaction on ORDERS table completed.', SYSTIMESTAMP);
        DBMS_OUTPUT.PUT_LINE('Total operation time: ' || (SYSTIMESTAMP - l_operation_start_time));
    END AFTER STATEMENT;
END trg_order_management;
/

SET SERVEROUTPUT ON;

-- Test the trigger
INSERT INTO orders VALUES (1, 10, SYSDATE, 100);
UPDATE orders SET total_amount = 120 WHERE order_id = 1;
DELETE FROM orders WHERE order_id = 1;

SELECT * FROM order_logs ORDER BY log_id;

Explanation

This example demonstrates the use of the BEFORE STATEMENT section within a compound trigger. The trg_order_management trigger logs various events on the orders table. In the BEFORE STATEMENT section, a timestamp l_operation_start_time is captured, and an initial log entry is made, marking the beginning of the DML operation. The AFTER EACH ROW section logs details for each individual row affected (insert, update, or delete). Finally, the AFTER STATEMENT section logs the completion of the DML operation and calculates the total time taken for the entire transaction, utilizing the l_operation_start_time set in the BEFORE STATEMENT section. This illustrates how compound triggers can be used for comprehensive transaction logging and performance monitoring.

Example 5: Implementing Complex Business Rules with Multiple Timing Points

CREATE TABLE customer_accounts (
    account_id        NUMBER PRIMARY KEY,
    customer_name     VARCHAR2(100),
    balance           NUMBER,
    last_transaction  TIMESTAMP
);

CREATE TABLE transaction_history (
    trans_id          NUMBER GENERATED BY DEFAULT AS IDENTITY,
    account_id        NUMBER,
    trans_type        VARCHAR2(10), -- 'DEBIT' or 'CREDIT'
    amount            NUMBER,
    trans_date        TIMESTAMP,
    new_balance       NUMBER
);

-- Initial data
INSERT INTO customer_accounts VALUES (101, 'Alice', 500, SYSTIMESTAMP);
INSERT INTO customer_accounts VALUES (102, 'Bob', 1000, SYSTIMESTAMP);

CREATE OR REPLACE TRIGGER trg_account_transactions
FOR UPDATE OF balance ON customer_accounts
COMPOUND TRIGGER
    TYPE t_account_updates IS TABLE OF customer_accounts%ROWTYPE INDEX BY PLS_INTEGER;
    l_pending_updates t_account_updates;
    l_idx PLS_INTEGER := 0;

    BEFORE EACH ROW IS
    BEGIN
        -- Prevent negative balance
        IF :NEW.balance < 0 THEN
            RAISE_APPLICATION_ERROR(-20001, 'Account balance cannot be negative.');
        END IF;
    END BEFORE EACH ROW;

    AFTER EACH ROW IS
    BEGIN
        -- Store the updated row for later processing in AFTER STATEMENT
        l_idx := l_idx + 1;
        l_pending_updates(l_idx) := :NEW;

        -- Record transaction history immediately (row-level detail)
        IF :NEW.balance > :OLD.balance THEN
            INSERT INTO transaction_history (account_id, trans_type, amount, trans_date, new_balance)
            VALUES (:NEW.account_id, 'CREDIT', :NEW.balance - :OLD.balance, SYSTIMESTAMP, :NEW.balance);
        ELSIF :NEW.balance < :OLD.balance THEN
            INSERT INTO transaction_history (account_id, trans_type, amount, trans_date, new_balance)
            VALUES (:NEW.account_id, 'DEBIT', :OLD.balance - :NEW.balance, SYSTIMESTAMP, :NEW.balance);
        END IF;
    END AFTER EACH ROW;

    AFTER STATEMENT IS
    BEGIN
        -- After all updates, perform a consolidated update for last_transaction timestamp
        -- This avoids individual row updates on last_transaction in the AFTER EACH ROW
        -- which might be less efficient for bulk updates
        FOR i IN 1 .. l_pending_updates.COUNT LOOP
            UPDATE customer_accounts
            SET last_transaction = SYSTIMESTAMP
            WHERE account_id = l_pending_updates(i).account_id;
        END LOOP;
        DBMS_OUTPUT.PUT_LINE('Processed ' || l_pending_updates.COUNT || ' account balance updates.');
    END AFTER STATEMENT;
END trg_account_transactions;
/

SET SERVEROUTPUT ON;

-- Test the trigger
-- Valid transactions
UPDATE customer_accounts SET balance = balance + 50 WHERE account_id = 101; -- Credit
UPDATE customer_accounts SET balance = balance - 20 WHERE account_id = 102; -- Debit

-- Invalid transaction (will raise error)
-- UPDATE customer_accounts SET balance = -10 WHERE account_id = 101;

SELECT * FROM customer_accounts;
SELECT * FROM transaction_history;

Explanation

This example demonstrates a sophisticated use of a compound trigger to enforce business rules and log transactions. The trg_account_transactions trigger fires on updates to the balance column of customer_accounts. The BEFORE EACH ROW section includes a validation check to prevent an account balance from becoming negative, raising an application error if violated. The AFTER EACH ROW section immediately records the individual credit or debit transaction in transaction_history. Crucially, the AFTER STATEMENT section performs a consolidated update to the last_transaction timestamp for all affected accounts. By collecting the updated account IDs in l_pending_updates during the row-level processing and then performing a single UPDATE statement in the AFTER STATEMENT section, this approach optimizes performance by reducing the number of individual UPDATE statements to the customer_accounts table itself for the last_transaction column, especially during bulk operations.

INSTEAD OF Triggers on Views

INSTEAD OF triggers in Oracle PL/SQL are a powerful mechanism to enable DML operations (INSERT, UPDATE, DELETE) on complex or non-updatable views. Normally, if a view is based on multiple tables, contains DISTINCT, GROUP BY, or aggregate functions, it cannot be directly updated. An INSTEAD OF trigger intercepts these DML operations on the view and executes a PL/SQL block that performs the corresponding DML on the underlying base tables, providing a seamless user experience as if the view itself were directly updatable. This allows developers to present simplified data models to users or applications while maintaining control over how data is manipulated in the underlying schema.

Example 1: INSTEAD OF INSERT on a View

CREATE TABLE employees_base (
    employee_id    NUMBER PRIMARY KEY,
    first_name     VARCHAR2(50),
    last_name      VARCHAR2(50),
    email          VARCHAR2(100) UNIQUE,
    phone_number   VARCHAR2(20),
    hire_date      DATE,
    job_id         NUMBER,
    salary         NUMBER,
    department_id  NUMBER
);

CREATE TABLE departments_base (
    department_id   NUMBER PRIMARY KEY,
    department_name VARCHAR2(100),
    location_id     NUMBER
);

INSERT INTO departments_base VALUES (10, 'IT', 1700);
INSERT INTO departments_base VALUES (20, 'HR', 1800);

CREATE OR REPLACE VIEW employee_details_v AS
SELECT
    e.employee_id,
    e.first_name,
    e.last_name,
    e.email,
    e.phone_number,
    e.hire_date,
    e.job_id,
    e.salary,
    e.department_id,
    d.department_name
FROM
    employees_base e
JOIN
    departments_base d ON e.department_id = d.department_id;

-- This view is not directly insertable because it's a join

CREATE OR REPLACE TRIGGER trg_instead_of_insert_employee_details
INSTEAD OF INSERT ON employee_details_v
FOR EACH ROW
BEGIN
    -- Insert into the base employees_base table
    INSERT INTO employees_base (
        employee_id,
        first_name,
        last_name,
        email,
        phone_number,
        hire_date,
        job_id,
        salary,
        department_id
    ) VALUES (
        :NEW.employee_id,
        :NEW.first_name,
        :NEW.last_name,
        :NEW.email,
        :NEW.phone_number,
        :NEW.hire_date,
        :NEW.job_id,
        :NEW.salary,
        :NEW.department_id
    );
END trg_instead_of_insert_employee_details;
/

-- Test the trigger
INSERT INTO employee_details_v (employee_id, first_name, last_name, email, phone_number, hire_date, job_id, salary, department_id)
VALUES (1001, 'John', 'Doe', 'john.doe@example.com', '555-1234', SYSDATE, 1, 60000, 10);

SELECT * FROM employee_details_v WHERE employee_id = 1001;
SELECT * FROM employees_base WHERE employee_id = 1001;

Explanation

This example demonstrates an INSTEAD OF INSERT trigger on the employee_details_v view. The view is a join between employees_base and departments_base, making it inherently non-insertable. The trg_instead_of_insert_employee_details trigger intercepts any INSERT statements directed at employee_details_v. Instead of allowing the INSERT to fail, the trigger executes its PL/SQL block, which extracts the necessary data from the :NEW pseudo-record of the view and performs an INSERT operation directly on the underlying employees_base table. This allows users to insert data into the logical employee_details_v view as if it were a regular table, abstracting the complexity of the underlying table structure.

Example 2: INSTEAD OF UPDATE on a View

CREATE TABLE inventory_products (
    product_id    NUMBER PRIMARY KEY,
    product_name  VARCHAR2(100),
    description   VARCHAR2(500),
    unit_price    NUMBER
);

CREATE TABLE inventory_stock (
    product_id    NUMBER PRIMARY KEY,
    quantity_on_hand NUMBER,
    last_update   TIMESTAMP
);

INSERT INTO inventory_products VALUES (1, 'Laptop', 'Powerful laptop', 1200);
INSERT INTO inventory_products VALUES (2, 'Keyboard', 'Mechanical keyboard', 75);
INSERT INTO inventory_stock VALUES (1, 50, SYSTIMESTAMP);
INSERT INTO inventory_stock VALUES (2, 150, SYSTIMESTAMP);

CREATE OR REPLACE VIEW product_inventory_v AS
SELECT
    p.product_id,
    p.product_name,
    p.description,
    p.unit_price,
    s.quantity_on_hand
FROM
    inventory_products p
JOIN
    inventory_stock s ON p.product_id = s.product_id;

CREATE OR REPLACE TRIGGER trg_instead_of_update_product_inventory
INSTEAD OF UPDATE ON product_inventory_v
FOR EACH ROW
BEGIN
    -- Update inventory_products if product details changed
    IF UPDATING('product_name') OR UPDATING('description') OR UPDATING('unit_price') THEN
        UPDATE inventory_products
        SET
            product_name = :NEW.product_name,
            description = :NEW.description,
            unit_price = :NEW.unit_price
        WHERE product_id = :OLD.product_id;
    END IF;

    -- Update inventory_stock if quantity changed
    IF UPDATING('quantity_on_hand') THEN
        UPDATE inventory_stock
        SET
            quantity_on_hand = :NEW.quantity_on_hand,
            last_update = SYSTIMESTAMP
        WHERE product_id = :OLD.product_id;
    END IF;
END trg_instead_of_update_product_inventory;
/

-- Test the trigger
UPDATE product_inventory_v
SET unit_price = 1250, quantity_on_hand = 45
WHERE product_id = 1;

UPDATE product_inventory_v
SET product_name = 'Gaming Keyboard', description = 'RGB mechanical keyboard'
WHERE product_id = 2;

SELECT * FROM product_inventory_v WHERE product_id IN (1, 2);
SELECT * FROM inventory_products WHERE product_id IN (1, 2);
SELECT * FROM inventory_stock WHERE product_id IN (1, 2);

Explanation

This example showcases an INSTEAD OF UPDATE trigger for the product_inventory_v view, which combines product details and stock information. Since the view is a join, direct updates are not allowed. The trg_instead_of_update_product_inventory trigger intercepts UPDATE statements on the view. It intelligently determines which underlying table needs to be updated based on which columns of the view are being modified (using UPDATING clause). If product_name, description, or unit_price are updated, the inventory_products table is modified. If quantity_on_hand is updated, the inventory_stock table is updated, and its last_update timestamp is refreshed. This provides a unified update interface for the user while correctly routing changes to the appropriate base tables.

Example 3: INSTEAD OF DELETE on a View

CREATE TABLE customer_master (
    customer_id     NUMBER PRIMARY KEY,
    customer_name   VARCHAR2(100),
    status          VARCHAR2(10) -- 'ACTIVE', 'INACTIVE'
);

CREATE TABLE customer_orders (
    order_id        NUMBER PRIMARY KEY,
    customer_id     NUMBER,
    order_date      DATE,
    order_amount    NUMBER
);

INSERT INTO customer_master VALUES (1, 'Alice Smith', 'ACTIVE');
INSERT INTO customer_master VALUES (2, 'Bob Johnson', 'ACTIVE');
INSERT INTO customer_master VALUES (3, 'Charlie Brown', 'INACTIVE');

INSERT INTO customer_orders VALUES (101, 1, SYSDATE - 5, 150);
INSERT INTO customer_orders VALUES (102, 1, SYSDATE - 2, 200);
INSERT INTO customer_orders VALUES (103, 2, SYSDATE - 10, 500);

CREATE OR REPLACE VIEW active_customers_v AS
SELECT
    cm.customer_id,
    cm.customer_name,
    COUNT(co.order_id) AS total_orders,
    SUM(co.order_amount) AS total_order_amount
FROM
    customer_master cm
LEFT JOIN
    customer_orders co ON cm.customer_id = co.customer_id
WHERE
    cm.status = 'ACTIVE'
GROUP BY
    cm.customer_id, cm.customer_name;

-- This view cannot be directly deleted from because it's a grouped view

CREATE OR REPLACE TRIGGER trg_instead_of_delete_active_customers
INSTEAD OF DELETE ON active_customers_v
FOR EACH ROW
BEGIN
    -- Instead of deleting, we'll logically delete by updating the status
    -- You could also perform a hard delete on customer_master if desired
    UPDATE customer_master
    SET status = 'INACTIVE'
    WHERE customer_id = :OLD.customer_id;

    -- Optionally, log the action
    INSERT INTO order_logs (action_type, log_message, log_date)
    VALUES ('DELETE_LOGICAL', 'Customer ' || :OLD.customer_id || ' deactivated.', SYSTIMESTAMP);
END trg_instead_of_delete_active_customers;
/

-- Test the trigger
-- Assuming order_logs table exists from previous examples or create it.
-- CREATE TABLE order_logs ( log_id NUMBER GENERATED BY DEFAULT AS IDENTITY, action_type VARCHAR2(10), log_message VARCHAR2(255), log_date TIMESTAMP);

DELETE FROM active_customers_v WHERE customer_id = 1;

SELECT * FROM customer_master WHERE customer_id = 1;
SELECT * FROM active_customers_v; -- Customer 1 should no longer appear
SELECT * FROM order_logs WHERE log_message LIKE '%deactivated%';

Explanation

This example demonstrates an INSTEAD OF DELETE trigger on active_customers_v, a view that aggregates customer information and their order summaries. Directly deleting from this grouped view is not possible. The trg_instead_of_delete_active_customers trigger intercepts DELETE statements on this view. Instead of physically deleting the customer, it performs a logical delete by updating the status column of the underlying customer_master table to 'INACTIVE'. This ensures data integrity and allows for auditing or soft-deletes, where records are marked as inactive rather than permanently removed. It also demonstrates how INSTEAD OF triggers can enforce specific business logic for deletion operations.

Example 4: Handling Multiple DML Operations on a View

CREATE TABLE books_master (
    book_id     NUMBER PRIMARY KEY,
    title       VARCHAR2(200),
    author      VARCHAR2(100),
    isbn        VARCHAR2(20) UNIQUE
);

CREATE TABLE book_prices (
    book_id     NUMBER PRIMARY KEY,
    price       NUMBER,
    currency    VARCHAR2(3)
);

INSERT INTO books_master VALUES (1, 'The Great Gatsby', 'F. Scott Fitzgerald', '978-0743273565');
INSERT INTO books_master VALUES (2, 'To Kill a Mockingbird', 'Harper Lee', '978-0446310789');
INSERT INTO book_prices VALUES (1, 15.99, 'USD');
INSERT INTO book_prices VALUES (2, 12.50, 'USD');

CREATE OR REPLACE VIEW book_details_v AS
SELECT
    bm.book_id,
    bm.title,
    bm.author,
    bm.isbn,
    bp.price,
    bp.currency
FROM
    books_master bm
JOIN
    book_prices bp ON bm.book_id = bp.book_id;

CREATE OR REPLACE TRIGGER trg_instead_of_book_details
INSTEAD OF INSERT OR UPDATE OR DELETE ON book_details_v
FOR EACH ROW
BEGIN
    IF INSERTING THEN
        INSERT INTO books_master (book_id, title, author, isbn)
        VALUES (:NEW.book_id, :NEW.title, :NEW.author, :NEW.isbn);

        INSERT INTO book_prices (book_id, price, currency)
        VALUES (:NEW.book_id, :NEW.price, :NEW.currency);

    ELSIF UPDATING THEN
        IF UPDATING('title') OR UPDATING('author') OR UPDATING('isbn') THEN
            UPDATE books_master
            SET
                title = :NEW.title,
                author = :NEW.author,
                isbn = :NEW.isbn
            WHERE book_id = :OLD.book_id;
        END IF;

        IF UPDATING('price') OR UPDATING('currency') THEN
            UPDATE book_prices
            SET
                price = :NEW.price,
                currency = :NEW.currency
            WHERE book_id = :OLD.book_id;
        END IF;

    ELSIF DELETING THEN
        DELETE FROM book_prices WHERE book_id = :OLD.book_id;
        DELETE FROM books_master WHERE book_id = :OLD.book_id;
    END IF;
END trg_instead_of_book_details;
/

-- Test the trigger
-- Insert
INSERT INTO book_details_v (book_id, title, author, isbn, price, currency)
VALUES (3, '1984', 'George Orwell', '978-0451524935', 9.99, 'USD');

-- Update
UPDATE book_details_v SET price = 16.50 WHERE book_id = 1;
UPDATE book_details_v SET author = 'Harper Lee (Revised)' WHERE book_id = 2;

-- Delete
DELETE FROM book_details_v WHERE book_id = 3;

SELECT * FROM book_details_v;
SELECT * FROM books_master;
SELECT * FROM book_prices;

Explanation

This comprehensive example demonstrates a single INSTEAD OF trigger (trg_instead_of_book_details) that handles INSERT, UPDATE, and DELETE operations on the book_details_v view. This view is a join of books_master and book_prices. The trigger uses INSERTING, UPDATING, and DELETING conditional predicates to determine the type of DML operation. For INSERT, it inserts into both base tables. For UPDATE, it selectively updates books_master or book_prices based on which columns of the view are modified. For DELETE, it deletes records from both base tables to maintain data consistency. This consolidated approach streamlines the management of DML operations on complex views, providing a unified and robust interface.

Example 5: Combining INSTEAD OF Trigger with Error Handling

CREATE TABLE customer_profiles (
    profile_id    NUMBER PRIMARY KEY,
    first_name    VARCHAR2(50),
    last_name     VARCHAR2(50),
    birth_date    DATE
);

CREATE TABLE customer_contacts (
    contact_id    NUMBER PRIMARY KEY,
    profile_id    NUMBER,
    email         VARCHAR2(100) UNIQUE,
    phone         VARCHAR2(20)
);

INSERT INTO customer_profiles VALUES (1, 'Alice', 'Smith', TO_DATE('1990-01-15', 'YYYY-MM-DD'));
INSERT INTO customer_contacts VALUES (101, 1, 'alice.s@example.com', '555-1111');

CREATE OR REPLACE VIEW customer_info_v AS
SELECT
    cp.profile_id,
    cp.first_name,
    cp.last_name,
    cp.birth_date,
    cc.email,
    cc.phone
FROM
    customer_profiles cp
LEFT JOIN
    customer_contacts cc ON cp.profile_id = cc.profile_id;

CREATE OR REPLACE TRIGGER trg_instead_of_customer_info
INSTEAD OF INSERT OR UPDATE ON customer_info_v
FOR EACH ROW
DECLARE
    v_contact_id NUMBER;
BEGIN
    IF INSERTING THEN
        -- Insert into customer_profiles
        INSERT INTO customer_profiles (profile_id, first_name, last_name, birth_date)
        VALUES (:NEW.profile_id, :NEW.first_name, :NEW.last_name, :NEW.birth_date);

        -- If contact info provided, insert into customer_contacts
        IF :NEW.email IS NOT NULL OR :NEW.phone IS NOT NULL THEN
            SELECT NVL(MAX(contact_id), 0) + 1 INTO v_contact_id FROM customer_contacts;
            INSERT INTO customer_contacts (contact_id, profile_id, email, phone)
            VALUES (v_contact_id, :NEW.profile_id, :NEW.email, :NEW.phone);
        END IF;

    ELSIF UPDATING THEN
        -- Update customer_profiles
        IF UPDATING('first_name') OR UPDATING('last_name') OR UPDATING('birth_date') THEN
            UPDATE customer_profiles
            SET
                first_name = :NEW.first_name,
                last_name = :NEW.last_name,
                birth_date = :NEW.birth_date
            WHERE profile_id = :OLD.profile_id;
        END IF;

        -- Update or insert into customer_contacts
        IF UPDATING('email') OR UPDATING('phone') THEN
            BEGIN
                -- Try to update existing contact
                UPDATE customer_contacts
                SET
                    email = :NEW.email,
                    phone = :NEW.phone
                WHERE profile_id = :OLD.profile_id;

                -- If no rows updated, it means no existing contact, so insert
                IF SQL%ROWCOUNT = 0 AND (:NEW.email IS NOT NULL OR :NEW.phone IS NOT NULL) THEN
                    SELECT NVL(MAX(contact_id), 0) + 1 INTO v_contact_id FROM customer_contacts;
                    INSERT INTO customer_contacts (contact_id, profile_id, email, phone)
                    VALUES (v_contact_id, :OLD.profile_id, :NEW.email, :NEW.phone);
                END IF;
            EXCEPTION
                WHEN DUP_VAL_ON_INDEX THEN
                    RAISE_APPLICATION_ERROR(-20002, 'Duplicate email address. Please use a unique email.');
                WHEN OTHERS THEN
                    RAISE;
            END;
        END IF;
    END IF;
END trg_instead_of_customer_info;
/

-- Test the trigger
-- Insert with full info
INSERT INTO customer_info_v (profile_id, first_name, last_name, birth_date, email, phone)
VALUES (2, 'Bob', 'Johnson', TO_DATE('1985-05-20', 'YYYY-MM-DD'), 'bob.j@example.com', '555-2222');

-- Update profile info only
UPDATE customer_info_v SET birth_date = TO_DATE('1991-01-15', 'YYYY-MM-DD') WHERE profile_id = 1;

-- Update contact info (existing)
UPDATE customer_info_v SET email = 'alice.smith@example.com' WHERE profile_id = 1;

-- Update contact info (adding for Bob, who already exists in profile but might not have contact)
UPDATE customer_info_v SET email = 'bobby.j@example.com', phone = '555-3333' WHERE profile_id = 2;

-- Attempt to insert duplicate email (will raise error)
-- INSERT INTO customer_info_v (profile_id, first_name, last_name, birth_date, email, phone)
-- VALUES (3, 'Carol', 'Davis', SYSDATE, 'alice.smith@example.com', '555-4444');

SELECT * FROM customer_info_v;
SELECT * FROM customer_profiles;
SELECT * FROM customer_contacts;

Explanation

This example demonstrates a more complex INSTEAD OF trigger (trg_instead_of_customer_info) that handles INSERT and UPDATE on customer_info_v, a view that combines customer profile and contact details. It includes robust error handling. For INSERT operations, it inserts into customer_profiles and conditionally inserts into customer_contacts if contact information is provided. For UPDATE operations, it updates customer_profiles or customer_contacts selectively.

Crucially, within the UPDATE section for contact information, it attempts to UPDATE an existing contact. If no rows are updated (meaning no contact record exists for that profile), it then INSERTs a new contact record. It also includes an EXCEPTION block to catch DUP_VAL_ON_INDEX for the email column, raising a custom application error if a duplicate email is attempted, providing a user-friendly error message. This illustrates how INSTEAD OF triggers can encapsulate complex DML logic, including conditional operations and error management, for a simplified view interface.

Managing Trigger Order with FOLLOWS

In Oracle PL/SQL, when multiple triggers are defined for the same event on the same table, their execution order can be important. Prior to Oracle Database 11g, the order of execution for multiple triggers on the same event and timing point (e.g., BEFORE INSERT) was non-deterministic, meaning it could vary and should not be relied upon. The FOLLOWS clause, introduced in Oracle Database 11g, allows you to explicitly specify the execution order of triggers. This ensures that a particular trigger runs after another named trigger, providing predictable behavior and enabling complex trigger dependencies. This feature is particularly useful for enforcing chained business rules or ensuring that data transformations happen in a specific sequence.

Example 1: Basic FOLLOWS Clause

CREATE TABLE product_log (
    log_id        NUMBER GENERATED BY DEFAULT AS IDENTITY,
    product_id    NUMBER,
    log_message   VARCHAR2(255),
    log_date      TIMESTAMP
);

CREATE TABLE products (
    product_id   NUMBER PRIMARY KEY,
    product_name VARCHAR2(100),
    price        NUMBER
);

-- Trigger 1: Log price change
CREATE OR REPLACE TRIGGER trg_products_log_price_change
BEFORE UPDATE OF price ON products
FOR EACH ROW
BEGIN
    INSERT INTO product_log (product_id, log_message, log_date)
    VALUES (:NEW.product_id, 'Price changed from ' || :OLD.price || ' to ' || :NEW.price, SYSTIMESTAMP);
END trg_products_log_price_change;
/

-- Trigger 2: Enforce price minimum
CREATE OR REPLACE TRIGGER trg_products_enforce_min_price
BEFORE UPDATE OF price ON products
FOR EACH ROW
BEGIN
    IF :NEW.price < 5 THEN
        :NEW.price := 5; -- Set to minimum if too low
        INSERT INTO product_log (product_id, log_message, log_date)
        VALUES (:NEW.product_id, 'Price adjusted to minimum of 5.', SYSTIMESTAMP);
    END IF;
END trg_products_enforce_min_price;
/

-- Now, recreate trg_products_log_price_change to FOLLOW trg_products_enforce_min_price
-- This ensures logging happens AFTER price potentially gets adjusted
DROP TRIGGER trg_products_log_price_change;

CREATE OR REPLACE TRIGGER trg_products_log_price_change
BEFORE UPDATE OF price ON products
FOR EACH ROW
FOLLOWS trg_products_enforce_min_price -- This trigger will run after trg_products_enforce_min_price
BEGIN
    INSERT INTO product_log (product_id, log_message, log_date)
    VALUES (:NEW.product_id, 'Final price after adjustment: ' || :NEW.price, SYSTAMIESTAMP);
END trg_products_log_price_change;
/

INSERT INTO products VALUES (1, 'Widget', 10);
INSERT INTO products VALUES (2, 'Gadget', 2);

-- Test the order
UPDATE products SET price = 3 WHERE product_id = 1;
UPDATE products SET price = 1 WHERE product_id = 2;

SELECT * FROM product_log WHERE product_id IN (1,2) ORDER BY log_id;
SELECT * FROM products WHERE product_id IN (1,2);

Explanation

This example demonstrates the basic use of the FOLLOWS clause. We have two BEFORE UPDATE triggers on the products table. trg_products_enforce_min_price ensures the price is at least 5. trg_products_log_price_change logs the price change. Without FOLLOWS, the logging trigger might fire before the minimum price enforcement, logging the old price instead of the potentially adjusted new price. By adding FOLLOWS trg_products_enforce_min_price to trg_products_log_price_change, we guarantee that trg_products_enforce_min_price executes first, potentially adjusting :NEW.price, and then trg_products_log_price_change fires, logging the final, adjusted price. This ensures the audit log accurately reflects the state after all price validations.

Example 2: Chaining Multiple Triggers with FOLLOWS

CREATE TABLE employee_bonuses (
    emp_id       NUMBER PRIMARY KEY,
    base_salary  NUMBER,
    bonus_pct    NUMBER,
    calculated_bonus NUMBER
);

-- Trigger 1: Validate bonus percentage
CREATE OR REPLACE TRIGGER trg_emp_bonus_validate_pct
BEFORE INSERT OR UPDATE OF bonus_pct ON employee_bonuses
FOR EACH ROW
BEGIN
    IF :NEW.bonus_pct < 0 OR :NEW.bonus_pct > 100 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Bonus percentage must be between 0 and 100.');
    END IF;
END trg_emp_bonus_validate_pct;
/

-- Trigger 2: Calculate bonus
CREATE OR REPLACE TRIGGER trg_emp_bonus_calculate
BEFORE INSERT OR UPDATE OF base_salary, bonus_pct ON employee_bonuses
FOR EACH ROW
FOLLOWS trg_emp_bonus_validate_pct -- Ensure percentage is valid before calculation
BEGIN
    :NEW.calculated_bonus := :NEW.base_salary * (:NEW.bonus_pct / 100);
END trg_emp_bonus_calculate;
/

-- Trigger 3: Log bonus calculation
CREATE OR REPLACE TRIGGER trg_emp_bonus_log_calculation
AFTER INSERT OR UPDATE ON employee_bonuses
FOR EACH ROW
FOLLOWS trg_emp_bonus_calculate -- Log after calculation is complete
BEGIN
    INSERT INTO product_log (product_id, log_message, log_date) -- Using product_log for simplicity
    VALUES (:NEW.emp_id, 'Bonus calculated for emp ' || :NEW.emp_id || ': ' || :NEW.calculated_bonus, SYSTIMESTAMP);
END trg_emp_bonus_log_calculation;
/

-- Test the triggers
INSERT INTO employee_bonuses (emp_id, base_salary, bonus_pct) VALUES (1, 50000, 10);
UPDATE employee_bonuses SET bonus_pct = 15 WHERE emp_id = 1;

-- This will raise an error due to validation trigger
-- INSERT INTO employee_bonuses (emp_id, base_salary, bonus_pct) VALUES (2, 60000, 120);

SELECT * FROM employee_bonuses WHERE emp_id = 1;
SELECT * FROM product_log WHERE product_id = 1 ORDER BY log_id DESC;

Explanation

This example demonstrates chaining multiple triggers using the FOLLOWS clause for a sequence of operations.

trg_emp_bonus_validate_pct (runs first): Validates that bonus_pct is within a valid range (0-100). If not, it raises an error, preventing further processing.

trg_emp_bonus_calculate (runs second, FOLLOWS trg_emp_bonus_validate_pct): This trigger calculates the calculated_bonus based on base_salary and bonus_pct. It is crucial that this calculation happens after bonus_pct has been validated and potentially adjusted.

trg_emp_bonus_log_calculation (runs third, FOLLOWS trg_emp_bonus_calculate): This AFTER trigger logs the final calculated bonus. It needs to run after the calculated_bonus has been computed and committed by the previous trigger. This chaining ensures a logical flow: validation -> calculation -> logging, providing deterministic and correct behavior for complex data transformations.

Example 3: Managing ORDER of BEFORE vs. AFTER Triggers (Conceptual)

While FOLLOWS is primarily for triggers at the same timing point, it's worth noting how it can interact with the natural order of BEFORE and AFTER triggers. A BEFORE trigger (even with FOLLOWS) will always fire before an AFTER trigger on the same event, but FOLLOWS provides fine-grained control within the BEFORE or AFTER groups.

CREATE TABLE transaction_records (
    transaction_id  NUMBER PRIMARY KEY,
    amount          NUMBER,
    status          VARCHAR2(20),
    creation_date   TIMESTAMP,
    processing_date TIMESTAMP
);

-- Trigger 1: Set initial status and creation date (BEFORE INSERT)
CREATE OR REPLACE TRIGGER trg_txn_set_initial_status
BEFORE INSERT ON transaction_records
FOR EACH ROW
BEGIN
    :NEW.status := 'PENDING';
    :NEW.creation_date := SYSTIMESTAMP;
END trg_txn_set_initial_status;
/

-- Trigger 2: Perform processing and update status (AFTER INSERT)
CREATE OR REPLACE TRIGGER trg_txn_process_transaction
AFTER INSERT ON transaction_records
FOR EACH ROW
BEGIN
    -- Simulate some processing time or logic
    -- In a real scenario, this might call a package or another procedure
    UPDATE transaction_records
    SET status = 'PROCESSED',
        processing_date = SYSTIMESTAMP
    WHERE transaction_id = :NEW.transaction_id;

    INSERT INTO product_log (product_id, log_message, log_date)
    VALUES (:NEW.transaction_id, 'Transaction ' || :NEW.transaction_id || ' processed.', SYSTIMESTAMP);
END trg_txn_process_transaction;
/

-- You cannot use FOLLOWS between BEFORE and AFTER triggers.
-- The order (BEFORE then AFTER) is inherent.
-- However, if you had multiple AFTER triggers, FOLLOWS would apply within that group.

-- Test
INSERT INTO transaction_records (transaction_id, amount) VALUES (100, 500);

SELECT * FROM transaction_records WHERE transaction_id = 100;
SELECT * FROM product_log WHERE product_id = 100 ORDER BY log_id DESC;

Explanation

This example demonstrates the inherent order of BEFORE and AFTER triggers. trg_txn_set_initial_status is a BEFORE INSERT trigger that sets the initial status and creation_date. trg_txn_process_transaction is an AFTER INSERT trigger that updates the status to 'PROCESSED' and sets the processing_date. Even without FOLLOWS, the BEFORE trigger will always execute entirely before the AFTER trigger. The FOLLOWS clause is used to establish order among triggers of the same timing point (e.g., two BEFORE INSERT triggers or two AFTER UPDATE triggers). This example clarifies that FOLLOWS provides control within a timing group, not across the fundamental BEFORE/AFTER distinction.

Example 4: Managing Dependencies for Data Cleansing and Validation

CREATE TABLE user_profiles (
    user_id     NUMBER PRIMARY KEY,
    username    VARCHAR2(50) UNIQUE,
    email       VARCHAR2(100),
    full_name   VARCHAR2(150)
);

-- Trigger 1: Trim whitespace from username and email
CREATE OR REPLACE TRIGGER trg_user_profile_trim_fields
BEFORE INSERT OR UPDATE ON user_profiles
FOR EACH ROW
BEGIN
    :NEW.username := TRIM(:NEW.username);
    :NEW.email := TRIM(:NEW.email);
END trg_user_profile_trim_fields;
/

-- Trigger 2: Validate email format (after trimming)
CREATE OR REPLACE TRIGGER trg_user_profile_validate_email
BEFORE INSERT OR UPDATE ON user_profiles
FOR EACH ROW
FOLLOWS trg_user_profile_trim_fields -- Ensure email is trimmed before validation
BEGIN
    IF :NEW.email IS NOT NULL AND NOT REGEXP_LIKE(:NEW.email, '^[A-Za-z0-9._%+-]+@[A-Za-z0-9.-]+\.[A-Za-z]{2,4}$') THEN
        RAISE_APPLICATION_ERROR(-20003, 'Invalid email format.');
    END IF;
END trg_user_profile_validate_email;
/

-- Trigger 3: Generate full_name (after all data is clean and valid)
CREATE OR REPLACE TRIGGER trg_user_profile_generate_name
BEFORE INSERT OR UPDATE ON user_profiles
FOR EACH ROW
FOLLOWS trg_user_profile_validate_email -- Ensure validation is done before generating
BEGIN
    -- Assume full_name should be generated from username if not provided
    IF :NEW.full_name IS NULL THEN
        :NEW.full_name := :NEW.username;
    END IF;
END trg_user_profile_generate_name;
/

-- Test the triggers
INSERT INTO user_profiles (user_id, username, email) VALUES (1, '  testuser  ', ' test@example.com '); -- Will be trimmed and validated
INSERT INTO user_profiles (user_id, username, email, full_name) VALUES (2, 'anotheruser', 'another@domain.net', 'Another User');

-- This will raise an error due to invalid email format after trimming
-- INSERT INTO user_profiles (user_id, username, email) VALUES (3, 'bademail', 'bademail@');

SELECT * FROM user_profiles;

Explanation

This example showcases a chain of triggers using FOLLOWS for data cleansing and validation on the user_profiles table.

trg_user_profile_trim_fields: This trigger runs first, trimming any leading or trailing whitespace from the username and email fields. This is a crucial pre-processing step.

trg_user_profile_validate_email: This trigger, set to FOLLOWS trg_user_profile_trim_fields, runs after the trimming. It then validates the email format. This order ensures that the email is cleaned before validation, preventing errors caused by extraneous spaces.

trg_user_profile_generate_name: This trigger, set to FOLLOWS trg_user_profile_validate_email, runs last in the BEFORE group. It generates the full_name based on the username if full_name is not provided. By this point, both username and email are clean and validated, ensuring the generated full_name uses correct data. This demonstrates how FOLLOWS facilitates a clear pipeline for data preparation.

Example 5: Using FOLLOWS for Data Integrity and Referential Actions

CREATE TABLE parent_table (
    parent_id   NUMBER PRIMARY KEY,
    parent_name VARCHAR2(100)
);

CREATE TABLE child_table (
    child_id    NUMBER PRIMARY KEY,
    parent_id   NUMBER,
    child_name  VARCHAR2(100),
    status      VARCHAR2(10)
);

-- Initial data
INSERT INTO parent_table VALUES (1, 'Parent A');
INSERT INTO parent_table VALUES (2, 'Parent B');
INSERT INTO child_table VALUES (101, 1, 'Child A1', 'ACTIVE');
INSERT INTO child_table VALUES (102, 1, 'Child A2', 'ACTIVE');
INSERT INTO child_table VALUES (103, 2, 'Child B1', 'ACTIVE');

-- Trigger 1: Before deleting a parent, check if children exist and raise error
CREATE OR REPLACE TRIGGER trg_parent_check_children
BEFORE DELETE ON parent_table
FOR EACH ROW
BEGIN
    DECLARE
        v_child_count NUMBER;
    BEGIN
        SELECT COUNT(*)
        INTO v_child_count
        FROM child_table
        WHERE parent_id = :OLD.parent_id;

        IF v_child_count > 0 THEN
            RAISE_APPLICATION_ERROR(-20004, 'Cannot delete parent; associated children exist.');
        END IF;
    END;
END trg_parent_check_children;
/

-- Trigger 2: After successful parent deletion, log the action
CREATE OR REPLACE TRIGGER trg_parent_log_deletion
AFTER DELETE ON parent_table
FOR EACH ROW
FOLLOWS trg_parent_check_children -- Log only after the check ensures no children
BEGIN
    INSERT INTO product_log (product_id, log_message, log_date)
    VALUES (:OLD.parent_id, 'Parent ' || :OLD.parent_id || ' deleted successfully.', SYSTIMESTAMP);
END trg_parent_log_deletion;
/

-- Test the triggers
-- Attempt to delete parent with children (will fail)
-- DELETE FROM parent_table WHERE parent_id = 1;

-- Delete parent without children (will succeed)
INSERT INTO parent_table VALUES (3, 'Parent C - No Children');
DELETE FROM parent_table WHERE parent_id = 3;

SELECT * FROM product_log WHERE product_id = 3 ORDER BY log_id DESC;
SELECT * FROM parent_table;
SELECT * FROM child_table;

Explanation

This example demonstrates FOLLOWS in the context of data integrity and referential actions, specifically for DELETE operations.

trg_parent_check_children: This BEFORE DELETE trigger checks if any child records exist for the parent being deleted. If children are found, it raises an application error, preventing the deletion of the parent. This acts as a custom referential integrity check.

trg_parent_log_deletion: This AFTER DELETE trigger is set to FOLLOWS trg_parent_check_children. It logs the successful deletion of a parent. By using FOLLOWS, we ensure that this logging trigger only executes after the trg_parent_check_children trigger has successfully run and confirmed that there are no dependent children (or allowed the deletion if no children). This guarantees that deletion logs are only recorded for truly successful parent deletions, enhancing audit trail reliability.