PL/SQL Triggers: Auto-Complete Orders & Audit Logging

Problem - PL/SQL Triggers

Consider a database with two tables: Orders and Audit_Log.
Tables:

• Orders(OrderID, CustomerID, OrderDate, Status, TotalAmount)
• Audit_Log(LogID, OrderID, Action, ActionDate)

Write PL/SQL triggers to perform the following tasks:

1. Automatically update the Status of an order to 'Completed' when TotalAmount > 10000.
2. Insert a record into Audit_Log whenever a new order is inserted or an existing order is updated. The log should record OrderID, the type of action ('INSERT' or 'UPDATE'), and the current timestamp.

Demo Table Creation and Sample Data

-- Create Orders table
CREATE TABLE Orders (
    OrderID      NUMBER(5) PRIMARY KEY,
    CustomerID   NUMBER(5),
    OrderDate    DATE,
    Status       VARCHAR2(20),
    TotalAmount  NUMBER(10,2)
);

-- Create Audit_Log table
CREATE TABLE Audit_Log (
    LogID     NUMBER(5) PRIMARY KEY,
    OrderID   NUMBER(5),
    Action    VARCHAR2(50),
    ActionDate DATE
);

-- Insert sample Orders data
INSERT INTO Orders VALUES (101, 1, TO_DATE('2025-09-01','YYYY-MM-DD'), 'Pending', 8500);
INSERT INTO Orders VALUES (102, 2, TO_DATE('2025-09-02','YYYY-MM-DD'), 'Pending', 12000);
INSERT INTO Orders VALUES (103, 3, TO_DATE('2025-09-03','YYYY-MM-DD'), 'Pending', 5000);

COMMIT;
  

PL/SQL Triggers

-- Trigger 1: Automatically update Status to 'Completed'
CREATE OR REPLACE TRIGGER trg_update_status
BEFORE INSERT OR UPDATE ON Orders
FOR EACH ROW
BEGIN
    IF :NEW.TotalAmount > 10000 THEN
        :NEW.Status := 'Completed';
    END IF;
END;
/

-- Trigger 2: Insert record into Audit_Log on INSERT or UPDATE
CREATE OR REPLACE TRIGGER trg_audit_log
AFTER INSERT OR UPDATE ON Orders
FOR EACH ROW
DECLARE
    v_logid NUMBER(5);
BEGIN
    SELECT NVL(MAX(LogID),0)+1 INTO v_logid FROM Audit_Log;

    INSERT INTO Audit_Log (LogID, OrderID, Action, ActionDate)
    VALUES (v_logid, :NEW.OrderID,
            CASE 
                WHEN INSERTING THEN 'INSERT'
                WHEN UPDATING THEN 'UPDATE'
            END,
            SYSDATE);
END;
/
  

Explanation

Trigger 1 (trg_update_status): This is a BEFORE INSERT OR UPDATE row-level trigger that automatically updates the Status to 'Completed' if the TotalAmount exceeds 10,000. It ensures high-value orders are immediately marked as completed.

Trigger 2 (trg_audit_log): This is an AFTER INSERT OR UPDATE row-level trigger that records each insert or update in the Audit_Log table. It tracks the OrderID, the type of action, and the timestamp. The LogID is generated by incrementing the current maximum ID.

Expected Output (Sample)

After inserting/updating Orders, the Status updates automatically and Audit_Log entries are created:

OrderID	CustomerID	Status	TotalAmount
101	1	Pending	8500
102	2	Completed	12000
103	3	Pending	5000

PL/SQL triggers example, Orders table, Audit_Log table, automatic status update, order completed trigger, insert audit log, Oracle PL/SQL triggers, row-level trigger, BEFORE INSERT OR UPDATE, AFTER INSERT OR UPDATE, TotalAmount > 10000, database programming example