LLI Wiki — My Digital Knowledge Base

Oracle audit trigger on inserts

Log user, host and date of each insert with an AFTER INSERT trigger and SYS_CONTEXT.

An AFTER INSERT ... FOR EACH ROW trigger can write to an audit table who inserted a row, from which machine and when. The example uses SYS_CONTEXT('userenv', ...) to capture the session context.

Example

Watched table app.orders, log table app.orders_log (business columns are illustrative):

CREATE OR REPLACE TRIGGER app.trig_orders_audit
AFTER INSERT
    ON app.orders
    FOR EACH ROW
BEGIN
    INSERT INTO app.orders_log (
        sessionid, username, host, osuser, logon_date,
        order_no, item, quantity
    )
    VALUES (
        sys_context('userenv', 'sessionid'),
        sys_context('userenv', 'session_user'),
        sys_context('userenv', 'host'),
        sys_context('userenv', 'os_user'),
        sysdate,
        :new.order_no, :new.item, :new.quantity
    );
END;
/

How it works

Element Role
AFTER INSERT ... FOR EACH ROW runs once per inserted row, after the insert
:new.<column> column value in the new row
sys_context('userenv', 'session_user') session user
sys_context('userenv', 'host') client machine
sys_context('userenv', 'os_user') operating system user
sys_context('userenv', 'sessionid') audit session id

Notes

Mastodon LLIMastodon SocialFosstodon