Trust Coding : Programming: Order management system using trigger and cursor

Consider the following database tables:

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

Write a trigger that automatically updates the Status of an order to 'Completed' whenever the TotalAmount exceeds 10,000.
Write another trigger that automatically inserts a record into the Audit_Log table whenever:

a new order is inserted, or
an existing order is updated.

Using an explicit cursor, write a PL/SQL block that retrieves all orders where TotalAmount > 10,000, displays their OrderID, CustomerID, and TotalAmount, and updates their Status to 'Completed' if it is not already set.

Answer

1. Trigger to Update Status to 'Completed' When TotalAmount Exceeds 10,000

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;
/

Explanation:
This trigger executes before inserting or updating an order. If the TotalAmount is greater than 10,000, it automatically sets the Status to 'Completed'.

2. Trigger to Insert Records into Audit_Log on Insert or Update

CREATE OR REPLACE TRIGGER trg_audit_log
AFTER INSERT OR UPDATE ON Orders
FOR EACH ROW
BEGIN
    INSERT INTO Audit_Log (LogID, OrderID, Action, ActionDate)
    VALUES (Audit_Log_SEQ.NEXTVAL, :NEW.OrderID,
            CASE
                WHEN INSERTING THEN 'INSERT'
                WHEN UPDATING THEN 'UPDATE'
            END,
            SYSDATE);
END;
/

Explanation:
This trigger logs every insert or update operation in the Audit_Log table with the action type and timestamp.

3. PL/SQL Block Using Explicit Cursor

DECLARE
    CURSOR order_cursor IS
        SELECT OrderID, CustomerID, TotalAmount, Status
        FROM Orders
        WHERE TotalAmount > 10000;

    v_orderID     Orders.OrderID%TYPE;
    v_customerID  Orders.CustomerID%TYPE;
    v_total       Orders.TotalAmount%TYPE;
    v_status      Orders.Status%TYPE;
BEGIN
    OPEN order_cursor;

    LOOP
        FETCH order_cursor INTO v_orderID, v_customerID, v_total, v_status;
        EXIT WHEN order_cursor%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE('OrderID: ' || v_orderID ||
                             ', CustomerID: ' || v_customerID ||
                             ', TotalAmount: ' || v_total);

        -- Update status if not completed
        IF v_status <> 'Completed' THEN
            UPDATE Orders
            SET Status = 'Completed'
            WHERE OrderID = v_orderID;
        END IF;
    END LOOP;

    CLOSE order_cursor;

    COMMIT;
END;
/

Explanation of Explicit Cursor

The cursor retrieves all orders where TotalAmount > 10,000.
It fetches data row by row and displays OrderID, CustomerID, and TotalAmount.
If the order status is not 'Completed', it updates the status.
COMMIT ensures changes are saved.

Conclusion

Triggers automate database actions and maintain data integrity. The first trigger updates order status automatically. The second trigger logs all insert and update operations. The PL/SQL block with an explicit cursor processes high-value orders and updates their status when required.

Trust Coding : Programming

Order management system using trigger and cursor