APEX-AT-WORK no image

Audit trail in APEX

Von Tobias Arnhold 1.31.2009
In case you have the requirement to use audit trail functionality in your APEX application then look at that example:

First we need two tables:
- The orignial table (BOOKS)
- The audit trail table (AUDIT_BOOKS)

-- the orignial table (BOOKS)
create table BOOKS
(
B_ID NUMBER not null,
B_NAME VARCHAR2(100),
B_DESCRIPTION VARCHAR2(500)
);

-- the audit trail table (AUDIT_BOOKS) always
-- includes 3 more columns for the user, the date
-- and the action (delete, update, insert)
-- this table shouldn't include any PK or FK
create table AUDIT_BOOKS
(
B_ID NUMBER not null,
B_NAME VARCHAR2(100),
B_DESCRIPTION VARCHAR2(500),
AUDIT_USER VARCHAR2(50),
AUDIT_DATE DATE,
AUDIT_ACTION VARCHAR2(6)
);

Next step is to create a trigger which includes the audit trail functionality:

CREATE OR REPLACE TRIGGER trg_audit_books
-- starts on every update, insert or delete command
AFTER INSERT OR DELETE OR UPDATE ON books
FOR EACH ROW
DECLARE
-- variable which declares if update, delete or insert process
v_trg_action VARCHAR2(6);
BEGIN
IF updating
THEN
-- when update
v_trg_action := 'UPDATE';
ELSIF deleting
THEN
-- when delete
v_trg_action := 'DELETE';
ELSIF inserting
THEN
-- when insert
v_trg_aktion := 'INSERT';
ELSE
-- if something else
v_trg_action := NULL;
END IF;

IF v_trg_action IN ('DELETE','UPDATE') THEN
-- if v_trg_action is DELETE or UPDATE then insert old table values
INSERT INTO audit_books
( B_ID, B_NAME, B_DESCRIPTION,
AUDIT_USER, AUDIT_DATE, AUDIT_ACTION)
VALUES
(:OLD.B_ID, :OLD.B_NAME, :OLD.B_DESCRIPTION,
UPPER(v('APP_USER')), SYSDATE, v_trg_action);
ELSE
-- if v_trg_action is INSERT then insert new table values
INSERT INTO audit_books
( B_ID, B_NAME, B_DESCRIPTION,
AUDIT_USER, AUDIT_DATE, AUDIT_ACTION)
VALUES
(:NEW.B_ID, :NEW.B_NAME, :NEW.B_DESCRIPTION,
UPPER(v('APP_USER')), SYSDATE, v_trg_action);
END IF;
-- about the insert command on the audit table
-- for current apex user: v('APP_USER')
-- for date: SYSDATE
-- for sql command: v_trg_action
END trg_audit_books;


This is all you need to use audit trail.
Special thanks to Roel and Anthony who helped me out a bit: http://forums.oracle.com/forums/message.jspa?messageID=3240814

Post Tags: