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:

10 Comments " Audit trail in APEX "

Roel 01 February, 2009 12:56

Tobias,
First thanks for mentioning my name... ;-)
Second : I think the code will not work when inserting a record, because the :OLD values are NULL on insert. You need to add some code to address that.
Third : These kind of tables have a tendency to grow to an enormous size...without any purge mechanism whatsoever. Also nobody seems to use the data that's in the table. And Oracle has some (database) features/options to addrees the underlying business questions (like Data Vault, Flashback Data Query and Archive etc).
Cheers
Roel

Tobias Arnhold 02 February, 2009 07:49

Hi Roel,
to point second:
I corrected the code. Thanks for checking. :D

Stew 08 April, 2009 18:52

I know this is an old post, but...

I was just reading about auditing an Apex application in the excellent book "Pro Oracle Application Express". Their conclusion was "don't reinvent the wheel," use Oracle's auditing features instead. They show how to easily set up auditing of your data in the Enterprise Edition of Oracle.

Just a suggestion for another way to get it done.

Good luck,

Stew

Tobias Arnhold 09 April, 2009 09:28

Hi Stew,

I think using Oracle features for auditing is a really good solution but if I'm not wrong, there is a high amount of license costs behind this audit feature. Isn't it an extra feature to license to your Enterprise Edition?

Regards

Tobias

Stew 09 April, 2009 20:12

Tobias,

Thanks for the reply. I wouldn't know anything about licensing costs, as I'm a lowly programmer!

Anonymous

actually, this looks more like what i would call a journal trail.

try this... if one models the tables in oracle designer (no licence cost there) then one can define that the table can be journaled server side at the physical model stage.

when generating the table ddl, designer will create ddl for the journal table. then one generates the table api from designer and all the logic is provided for populating the journal tables.

this works great.

ohs audit 14 April, 2011 02:47

Thanks for showing the program code for the auditing. Specially generating a trigger reminds me the programming I used to do to generate electronic pulses and trigger.

ritika 15 June, 2011 14:47

well hi,
i am quite new to apex and have this silly trouble. its like i ve to create a GUI with 2 item, eg name , and branch. after hitting the submit button, the values in the item(the text fields) should populate a table.

i have created the GUI the table, but dnt undrstand how to populate the table.

it would be great if someone could help.

Anonymous

the triggers has errors,
error ORA-04098: trigger 'DB.TRG_AUDIT_BOOKS' is invalid and failed re-validation

Anonymous

There is a typo
-- when insert
v_trg_aktion := 'INSERT';
ELSE

should be
-- when insert
v_trg_action := 'INSERT';
ELSE

Thanks