Implementing Soft Deletes and Audit Trails in Oracle Applications

Why Soft Deletes

In many business applications, deleting a record permanently is unacceptable. Regulatory requirements may mandate data retention, users may accidentally delete important records, and related data may reference the deleted row. Soft deletes solve this by marking records as deleted rather than physically removing them, while audit trails track every change for compliance and troubleshooting.

Soft Delete Pattern

ALTER TABLE orders ADD (
    is_deleted    VARCHAR2(1) DEFAULT 'N' NOT NULL
        CONSTRAINT chk_orders_deleted CHECK (is_deleted IN ('Y','N')),
    deleted_by    VARCHAR2(255),
    deleted_date  DATE
);

CREATE INDEX idx_orders_active ON orders(is_deleted);

-- View for active records (use this in APEX reports)
CREATE OR REPLACE VIEW active_orders AS
SELECT * FROM orders WHERE is_deleted = 'N';

-- Delete procedure (soft)
CREATE OR REPLACE PROCEDURE soft_delete_order(p_order_id IN NUMBER)
IS
BEGIN
    UPDATE orders
       SET is_deleted   = 'Y',
           deleted_by   = SYS_CONTEXT('APEX$SESSION', 'APP_USER'),
           deleted_date = SYSDATE
     WHERE order_id = p_order_id
       AND is_deleted = 'N';
END;

Audit Trail With Triggers

CREATE TABLE orders_audit (
    audit_id      NUMBER GENERATED ALWAYS AS IDENTITY,
    order_id      NUMBER NOT NULL,
    action        VARCHAR2(10) NOT NULL,
    changed_by    VARCHAR2(255),
    changed_date  DATE DEFAULT SYSDATE,
    old_values    CLOB,
    new_values    CLOB
);

CREATE OR REPLACE TRIGGER trg_orders_audit
AFTER INSERT OR UPDATE OR DELETE ON orders
FOR EACH ROW
DECLARE
    l_action VARCHAR2(10);
BEGIN
    IF INSERTING THEN l_action := 'INSERT';
    ELSIF UPDATING THEN l_action := 'UPDATE';
    ELSE l_action := 'DELETE';
    END IF;

    INSERT INTO orders_audit (order_id, action, changed_by, old_values, new_values)
    VALUES (
        NVL(:NEW.order_id, :OLD.order_id),
        l_action,
        COALESCE(SYS_CONTEXT('APEX$SESSION','APP_USER'),
                 SYS_CONTEXT('USERENV','SESSION_USER')),
        CASE WHEN l_action != 'INSERT' THEN
            JSON_OBJECT('status' VALUE :OLD.status,
                        'total' VALUE :OLD.total_amount,
                        'customer' VALUE :OLD.customer_id)
        END,
        CASE WHEN l_action != 'DELETE' THEN
            JSON_OBJECT('status' VALUE :NEW.status,
                        'total' VALUE :NEW.total_amount,
                        'customer' VALUE :NEW.customer_id)
        END
    );
END;

Querying the Audit Trail

Store old and new values as JSON for flexibility. You can then use JSON_TABLE to query specific field changes across the audit history without needing to modify the audit table structure when the source table gets new columns.

Performance and Maintenance

Soft deletes require every query to include WHERE is_deleted = 'N'. Using views or VPD policies to enforce this automatically prevents developers from forgetting the filter. Partition the audit table by date and implement a retention policy to archive or purge old audit records, otherwise the table will grow indefinitely and impact query performance.