Database Auditing System with PL/SQL

A complete Oracle PL/SQL auditing solution built for a banking system, designed to track and log every Data Manipulation Language (DML) operation performed on critical database tables — ensuring accountability, transparency, and data integrity.

Overview

This project implements a full audit logging system using Oracle Database triggers, stored procedures, and exception handling. It automatically records who changed what, when, and how, across the bank's core tables, and captures any operational errors in a dedicated error log.

Audited Tables

• ALLOWANCES
• ATTENDANCE
• COUNTRIES
• DEPARTMENTS
• EMPLOYEES
• ROLES

Features

• Centralized Audit Table — A single table capturing INSERT/UPDATE/DELETE activity across all monitored tables.
• AFTER Triggers — Row-level triggers on each audited table that automatically log DML operations with old and new data snapshots.
• Stored Procedures — Parameterized UPDATE and DELETE procedures for the ALLOWANCES table with built-in exception handling.
• Error Logging — Failed operations are caught and recorded in an ERROR_LOG table instead of crashing the application.
• Test Suite — Scripted INSERT, UPDATE, DELETE, and failure-case tests to validate the entire system end to end.

Project Structure

All logic is delivered as a single SQL script, organized into the following sections:

project.sql
├── 1. AUDIT table creation
├── 2. ERROR_LOG table creation
├── 3. Trigger: ALLOWANCES
├── 4. Triggers: ATTENDANCE, COUNTRIES, DEPARTMENTS, EMPLOYEES, ROLES
├── 5. Procedure: update_allowance
├── 6. Procedure: delete_allowance
├── 7. Test scripts (INSERT / UPDATE / DELETE / error case)
└── 8. Verification queries (AUDIT and ERROR_LOG contents)

Schema Design

AUDIT Table

Column	Description
audit_id	Primary key (auto-generated)
operation_type	INSERT, UPDATE, or DELETE
object_name	Name of the affected table
operation_user	Database user who performed the operation
operation_timestamp	Date and time of the operation
old_data	Data before the operation (UPDATE/DELETE only)
new_data	Data after the operation (INSERT/UPDATE only)

ERROR_LOG Table

Column	Description
error_id	Primary key
procedure_name	Name of the procedure where the error occurred
error_message	Oracle error message
error_code	SQLCODE
error_timestamp	Date and time of the error

Prerequisites

• Oracle Database (XE, Standard, or Enterprise Edition)
• SQL Developer, SQL*Plus, or any Oracle-compatible SQL client
• An active connection to the target schema containing the ALLOWANCES, ATTENDANCE, COUNTRIES, DEPARTMENTS, EMPLOYEES, and ROLES tables

Setup & Execution

1. Connect to your Oracle database using SQL Developer or SQL*Plus.
2. Run the full project.sql script in order — it creates the audit infrastructure before any triggers or procedures that depend on it.
3. Confirm all objects compiled successfully (no SP2- or PLS- errors).
4. Run the included test scripts to validate INSERT, UPDATE, and DELETE auditing.
5. Query the AUDIT and ERROR_LOG tables to confirm expected results.

-- Example verification queries
SELECT * FROM audit ORDER BY operation_timestamp DESC;
SELECT * FROM error_log ORDER BY error_timestamp DESC;

Testing

Test	Action	Expected Result
1	Insert a new allowance record	Record inserted; AUDIT logs an INSERT entry
2	Update the inserted record	Record updated; AUDIT stores old and new values
3	Delete the updated record	Record deleted; AUDIT stores deleted data as old data
4	Trigger an invalid UPDATE/DELETE	Procedure handles the error gracefully; entry appears in ERROR_LOG

Notes

• Object names follow standard Oracle PL/SQL naming conventions.
• All code is commented to explain trigger logic, procedure flow, and exception handling.
• The script is idempotent-friendly where possible (drops existing objects before recreation) to support repeated testing.

Author

Prepared as part of a scenario-based practical examination on Database Auditing with PL/SQL.