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
- The log table must exist before the trigger is created, with matching columns.
- The trigger runs inside the insert transaction: an error in the log insert makes the original insert fail.