09_plsql.sql

-- ============================================================
-- Purpose : Automatically record order delivery status changes
-- Author  : [Your Name]
-- Date    : [Submission Date]
-- Tool    : Oracle 11g Express Edition
-- ============================================================

CREATE OR REPLACE TRIGGER trg_order_status_history
AFTER UPDATE OF order_status ON customer_order
FOR EACH ROW
WHEN (OLD.order_status <> NEW.order_status)
DECLARE
    v_note VARCHAR2(200);
BEGIN
    v_note := 'Status changed automatically by trigger';

    INSERT INTO order_status_history (
        status_history_id,
        order_id,
        old_status,
        new_status,
        changed_by,
        changed_at,
        note
    )
    VALUES (
        status_history_seq.NEXTVAL,
        :NEW.order_id,
        :OLD.order_status,
        :NEW.order_status,
        USER,
        SYSDATE,
        v_note
    );
EXCEPTION
    WHEN OTHERS THEN
        RAISE_APPLICATION_ERROR(-20001, 'trg_order_status_history failed: ' || SQLERRM);
END;
/

-- Suggested trigger test for screenshots:
-- UPDATE customer_order
-- SET order_status = 'ACCEPTED'
-- WHERE order_id = 7010;
--
-- COMMIT;
--
-- SELECT status_history_id, order_id, old_status, new_status, changed_by, changed_at, note
-- FROM order_status_history
-- WHERE order_id = 7010
-- ORDER BY status_history_id;