Self-Auditing Employee Data Model in Oracle

Summary

Learn how to create a self-auditing Employee Data Model in Oracle. This guide includes SQL scripts for table creation and triggers for auditing changes.

The guide outlines how to:

1. Create an `Employee` table to store employee data.

2. Create an `Employee_Audit` table to keep track of changes to employee records.

3. Implement a trigger that automatically populates the `Employee_Audit` table upon data changes in the `Employee` table.

### Create the Employee Table

##The `Employee` table holds basic employee information along with tracking who created or last modified each record.

CREATE TABLE Employee (

    EmployeeID NUMBER PRIMARY KEY,

    FirstName VARCHAR2(50),

    LastName VARCHAR2(50),

    Email VARCHAR2(100),

    Status VARCHAR2(50),

    IsActive NUMBER(1) DEFAULT 1,

    CreatedBy VARCHAR2(50),

    CreatedDate TIMESTAMP,

    LastModifiedBy VARCHAR2(50),

    LastModifiedDate TIMESTAMP

);

### Create the Employee Audit Table

###The `Employee_Audit` table records changes made to the `Employee` table, including what was changed, who changed it, and when.

CREATE TABLE Employee_Audit (

    AuditID NUMBER PRIMARY KEY,

    EmployeeID NUMBER,

    AttributeChanged VARCHAR2(50),

    OldValue VARCHAR2(100),

    NewValue VARCHAR2(100),

    ModifiedBy VARCHAR2(50),

    ModifiedDate TIMESTAMP,

    FOREIGN KEY (EmployeeID) REFERENCES Employee(EmployeeID)

);

### Create Sequence for AuditID

##This SQL sequence is created for generating unique `AuditID`s for the `Employee_Audit` table.

CREATE SEQUENCE AuditID_Seq;

### Create Trigger for ETL Procedure

##This trigger is invoked after an `INSERT`, `UPDATE`, or `DELETE` operation on the `Employee` table. It populates the `Employee_Audit` table with the details of the change.

CREATE OR REPLACE TRIGGER Employee_Audit_Trigger

AFTER INSERT OR UPDATE OR DELETE ON Employee

FOR EACH ROW

DECLARE

    v_old_value VARCHAR2(100);

    v_new_value VARCHAR2(100);

    v_attribute_changed VARCHAR2(50);

BEGIN

    -- Trigger logic here

END;

/

By following this guide, you'll be able to keep track of changes to employee records directly within your Oracle database. 

This self-auditing mechanism is particularly useful for compliance and auditing purposes.