Auditing database record changes in Oracle

Extends Database interview questions - triggers using Sybase database

Q. What are some of the considerations in auditing database table data?
A.

• Creating audit tables without any constraints to store record changes.  Audit tables are heavily hit with inserts, hence needs to have fewer indices if at all required.
• Decide on what combination of triggers are required like  Create, Update, or Delete in the CRUD operations.
• Create the triggers.

Here is an example for a given table shown below

CREATE TABLE bus_rule(
    id                           NUMBER(30)     NOT NULL,
    code                         VARCHAR2(50)   NOT NULL,
    rule_entry_id                NUMBER(30)     NOT NULL,
    name                         VARCHAR2(100)  NOT NULL,
    effective                    date           NULL,
    expires                      date           NULL,
    created_by                   VARCHAR2(30)   NOT NULL,
    created_date                 TIMESTAMP      NOT NULL,
    updated_by                   VARCHAR2(30)   NOT NULL,
    updated_date                 TIMESTAMP      NOT NULL,
    CONSTRAINT bus_rule_pk PRIMARY KEY (id),
    CONSTRAINT fk_bus_rule_rule_entry_id FOREIGN KEY (rule_entry_id) REFERENCES bus_rule_entry(id)
);

Step 1: Create a corresponding audit table to store historical data. Note that this table does not have any constraints as it is historical table. It adds a new column called "crud_operation" to tag if the data stored was due to Create, Update or Delete.

CREATE TABLE bus_rule_audit(
 id                           NUMBER(30)     NOT NULL,
    code                         VARCHAR2(50)   NOT NULL,
    rule_entry_id                NUMBER(30)     NOT NULL,
    name                         VARCHAR2(100)  NOT NULL,
    effective                    date           NULL,
    expires                      date           NULL,
    created_by                   VARCHAR2(30)   NOT NULL,
    created_date                 TIMESTAMP      NOT NULL,
    updated_by                   VARCHAR2(30)   NOT NULL,
    updated_date                 TIMESTAMP      NOT NULL,
    crud_operation               char(1)   NOT NULL
);

Step 2: Create triggers in Oracle

CREATE OR REPLACE TRIGGER bus_rule_trig

AFTER INSERT OR UPDATE OR DELETE 
ON bus_rule

REFERENCING OLD AS OLD NEW AS NEW
FOR EACH ROW
  DECLARE
    crud_operation  CHAR(1);
 
  BEGIN
             
  IF INSERTING THEN
     crud_operation := 'C';
  ELSIF UPDATING THEN
     crud_operation := 'U';
  ELSIF DELETING THEN
     crud_operation := 'D';
 END IF;
        

 IF INSERTING OR UPDATING THEN
 
      INSERT INTO bus_rule_audit (ID,CODE,RULE_ENTRY_ID,NAME,EFFECTIVE,EXPIRES,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE,CRUD_OPERATION) 
        VALUES (:NEW.ID, :NEW.CODE,:NEW.RULE_ENTRY_ID,:NEW.NAME,:NEW.EFFECTIVE,:NEW.EXPIRES,:NEW.CREATED_BY,:NEW.CREATED_DATE,:NEW.UPDATED_BY,:NEW.UPDATED_DATE,  crud_operation);

    ELSE 
      
      INSERT INTO bus_rule_audit (ID,CODE,RULE_ENTRY_ID,NAME,EFFECTIVE,EXPIRES,CREATED_BY,CREATED_DATE,UPDATED_BY,UPDATED_DATE,CRUD_OPERATION) 
        VALUES (:OLD.ID, :OLD.CODE,:OLD.RULE_ENTRY_ID,:OLD.NAME,:OLD.EFFECTIVE,:OLD.EXPIRES,:OLD.CREATED_BY,:OLD.CREATED_DATE,:OLD.UPDATED_BY,:OLD.UPDATED_DATE,  crud_operation);
    
    END IF;  
       
END;

Now, if you perform any CUD operation table bus_rule the bus_rule_trig will fire to insert records into the audit table bus_rule_audit.


Q. Why do you need to be careful as to where you use triggers?
A. The main problems with triggers are

a) they are global - they apply no matter what the context of the table activity; and
b) they are candid; it's easy to forget they are there until they hurt you with unintended consequences.


You may also like:

• SQL Interview Questions and Answers
• Defining Hibernate entities with auditable fields, soft delete field, optimistic locking field, etc
• Database interview questions and answers