Audit Table changes with Trigger by example

==================================
General
==================================
This is an example of auditing DML operations on a table using a trigger, and logging these changes into an audit table.
Audit table resides on a dedicated Tablespace.
Optionally this table can be partitioned by change date.

==================================
Trigger code
==================================
The trigger is inserting records into Audit table whenever there is an update on the base table.

CREATE OR REPLACE TRIGGER AUD_CUSTOMERS_TRG
  AFTER UPDATE OR INSERT OR DELETE ON CUSTOMERS
  FOR EACH ROW
DECLARE
  v_oper_user VARCHAR2(30);
BEGIN

  SELECT  UPPER(SUBSTR(SYS_CONTEXT('USERENV','OS_USER'),1,30)) INTO v_oper_user 
    FROM DUAL;

  IF INSERTING THEN
    INSERT INTO AUD_CUSTOMERS
      (change_date, change_action, old_new, customer_id, name, oper_user)
    VALUES
      (SYSDATE, 'INSERT', 'NEW', :NEW.customer_id, :NEW.name, v_oper_user);
  ELSIF UPDATING THEN
    INSERT INTO AUD_CUSTOMERS
      (change_date, change_action, old_new, customer_id, name, oper_user)
    VALUES
      (SYSDATE, 'UPDATE', 'OLD', :OLD.customer_id, :OLD.name,  v_oper_user);
    INSERT INTO AUD_CUSTOMERS
      (change_date, change_action, old_new, customer_id, name, oper_user)
    VALUES
      (SYSDATE, 'UPDATE', 'NEW', :NEW.customer_id, :NEW.name, v_oper_user);
  ELSE
    INSERT INTO AUD_CUSTOMERS
      (change_date, change_action, old_new, customer_id, name, oper_user)
    VALUES
      (SYSDATE, 'DELETE', 'OLD', :OLD.customer_id, :OLD.name,  v_oper_user);
  END IF;
END;

==================================

The DDL of the Audit Table

==================================

The structure of the Audit table is same as the base table, with addition of four fields:

- change_date

- change_action: INSERT/UPDATE/DELETE

- old_new: NEW/OLD

- oper_user: The OS login of the user who made this change.

CREATE TABLE AUD_CUSTOMERS(

change_date                DATE NOT NULL, 

change_action              VARCHAR2(10 BYTE) NOT NULL,

old_new                    VARCHAR2(3 BYTE) NOT NULL,

customer_id                VARCHAR2(5 BYTE)  ,                          

name                       VARCHAR2(30 BYTE) ,                          

oper_user                  VARCHAR2(30 BYTE)  

)

tablespace AUDIT_TBS NOLOGGING;

alter table AUD_CUSTOMERS

  add constraint AUD_CUSTOMERS_PK primary key (change_date, change_action, old_new)

  using index 

  tablespace AUDIT_TBS;

grant select, insert, update, delete, references, alter, index on AUD_CUSTOMERS to MANAGER;

grant select on AUD_CUSTOMERS to SELECTOR;

grant select on AUD_CUSTOMERS to USERS_ROLE;