Audit trail in APEX

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:

APEX examples