audit_system.sql

COURSE: DATABASE PROGRAMMING
 FACULTY: Computing And Information Science 
 GROUP MEMBER REG NO:
 -33341/2025
 -32217/2025
 -31388/2025
 -32122/2025
 -32884/2025

/*
================================================================================
 SCENARIO-BASED PRACTICAL 
 Database Auditing with PL/SQL - Banking System
 Deliverable: Single SQL Script
================================================================================
 Author : Database Developer
 Purpose: Implements a full DML audit-logging solution (audit table, per-table
          triggers, stored procedures with exception handling, error logging,
          and test scripts) for the tables: ALLOWANCES, ATTENDANCE, COUNTRIES,
          DEPARTMENTS, EMPLOYEES, ROLES.

 
*/

-- Run everything as the schema owner. SERVEROUTPUT so DBMS_OUTPUT test
-- messages are visible.
SET SERVEROUTPUT ON SIZE UNLIMITED;
SET LINESIZE 200;
SET PAGESIZE 100;

/*
================================================================================

*/

BEGIN
   FOR t IN (SELECT table_name FROM user_tables
             WHERE table_name IN ('ALLOWANCES','ATTENDANCE','COUNTRIES',
                                   'DEPARTMENTS','EMPLOYEES','ROLES',
                                   'AUDIT_LOG','ERROR_LOG'))
   LOOP
      EXECUTE IMMEDIATE 'DROP TABLE '||t.table_name||' CASCADE CONSTRAINTS';
   END LOOP;
END;
/

CREATE TABLE countries (
   country_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   country_name   VARCHAR2(100) NOT NULL,
   country_code   VARCHAR2(10)
);

CREATE TABLE departments (
   department_id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   department_name VARCHAR2(100) NOT NULL,
   country_id      NUMBER REFERENCES countries(country_id)
);

CREATE TABLE roles (
   role_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   role_name   VARCHAR2(100) NOT NULL,
   role_desc   VARCHAR2(200)
);

CREATE TABLE employees (
   employee_id    NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   first_name     VARCHAR2(50) NOT NULL,
   last_name      VARCHAR2(50) NOT NULL,
   email          VARCHAR2(100),
   hire_date      DATE DEFAULT SYSDATE,
   salary         NUMBER(10,2),
   department_id  NUMBER REFERENCES departments(department_id),
   role_id        NUMBER REFERENCES roles(role_id)
);

CREATE TABLE allowances (
   allowance_id     NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   employee_id      NUMBER REFERENCES employees(employee_id),
   allowance_type   VARCHAR2(50) NOT NULL,
   amount           NUMBER(10,2) NOT NULL,
   effective_date   DATE DEFAULT SYSDATE
);

CREATE TABLE attendance (
   attendance_id   NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   employee_id     NUMBER REFERENCES employees(employee_id),
   attendance_date DATE DEFAULT SYSDATE,
   status          VARCHAR2(20),
   check_in        DATE,
   check_out       DATE
);

/*
================================================================================

 A single, generic audit table stores audit rows for ALL six business
 tables. old_data / new_data are stored as CLOBs holding a
 "column=value; column=value" style snapshot of the row, which lets one
 table capture rows from tables of differing structure.
*/

CREATE TABLE audit_log (
   audit_id            NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   operation_type       VARCHAR2(10)   NOT NULL
                          CHECK (operation_type IN ('INSERT','UPDATE','DELETE')),
   object_name          VARCHAR2(30)   NOT NULL,
   operation_user       VARCHAR2(30)   NOT NULL,
   operation_timestamp  TIMESTAMP      DEFAULT SYSTIMESTAMP NOT NULL,
   old_data             CLOB,
   new_data             CLOB
);

COMMENT ON TABLE audit_log IS 'Central audit trail for DML on audited banking tables';

/*
================================================================================
 ERROR LOG TABLE (part of Task 3 requirements)
================================================================================
*/

CREATE TABLE error_log (
   error_id         NUMBER GENERATED BY DEFAULT AS IDENTITY PRIMARY KEY,
   procedure_name   VARCHAR2(60)   NOT NULL,
   error_message    VARCHAR2(4000),
   error_code       NUMBER,
   error_timestamp  TIMESTAMP DEFAULT SYSTIMESTAMP NOT NULL
);

/*
================================================================================
 TASK 2: DML AUDIT TRIGGERS  (30 Marks)
================================================================================
 One AFTER INSERT OR UPDATE OR DELETE ... FOR EACH ROW trigger per audited
 table. Each trigger builds a human-readable snapshot string of the
 :OLD and :NEW row images and inserts one row into AUDIT_LOG.
*/

-- ---------------------------------------------------------------------------
-- 2.1 ALLOWANCES
-- ---------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER trg_audit_allowances
AFTER INSERT OR UPDATE OR DELETE ON allowances
FOR EACH ROW
DECLARE
   v_op        VARCHAR2(10);
   v_old       CLOB;
   v_new       CLOB;
BEGIN
   IF INSERTING THEN
      v_op  := 'INSERT';
      v_new := 'ALLOWANCE_ID='||:NEW.allowance_id
             ||'; EMPLOYEE_ID='||:NEW.employee_id
             ||'; ALLOWANCE_TYPE='||:NEW.allowance_type
             ||'; AMOUNT='||:NEW.amount
             ||'; EFFECTIVE_DATE='||TO_CHAR(:NEW.effective_date,'YYYY-MM-DD HH24:MI:SS');
   ELSIF UPDATING THEN
      v_op  := 'UPDATE';
      v_old := 'ALLOWANCE_ID='||:OLD.allowance_id
             ||'; EMPLOYEE_ID='||:OLD.employee_id
             ||'; ALLOWANCE_TYPE='||:OLD.allowance_type
             ||'; AMOUNT='||:OLD.amount
             ||'; EFFECTIVE_DATE='||TO_CHAR(:OLD.effective_date,'YYYY-MM-DD HH24:MI:SS');
      v_new := 'ALLOWANCE_ID='||:NEW.allowance_id
             ||'; EMPLOYEE_ID='||:NEW.employee_id
             ||'; ALLOWANCE_TYPE='||:NEW.allowance_type
             ||'; AMOUNT='||:NEW.amount
             ||'; EFFECTIVE_DATE='||TO_CHAR(:NEW.effective_date,'YYYY-MM-DD HH24:MI:SS');
   ELSIF DELETING THEN
      v_op  := 'DELETE';
      v_old := 'ALLOWANCE_ID='||:OLD.allowance_id
             ||'; EMPLOYEE_ID='||:OLD.employee_id
             ||'; ALLOWANCE_TYPE='||:OLD.allowance_type
             ||'; AMOUNT='||:OLD.amount
             ||'; EFFECTIVE_DATE='||TO_CHAR(:OLD.effective_date,'YYYY-MM-DD HH24:MI:SS');
   END IF;

   INSERT INTO audit_log (operation_type, object_name, operation_user,
                           operation_timestamp, old_data, new_data)
   VALUES (v_op, 'ALLOWANCES', SYS_CONTEXT('USERENV','SESSION_USER'),
           SYSTIMESTAMP, v_old, v_new);
END;
/

-- ---------------------------------------------------------------------------
-- 2.2 ATTENDANCE
-- ---------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER trg_audit_attendance
AFTER INSERT OR UPDATE OR DELETE ON attendance
FOR EACH ROW
DECLARE
   v_op  VARCHAR2(10);
   v_old CLOB;
   v_new CLOB;
BEGIN
   IF INSERTING THEN
      v_op  := 'INSERT';
      v_new := 'ATTENDANCE_ID='||:NEW.attendance_id
             ||'; EMPLOYEE_ID='||:NEW.employee_id
             ||'; ATTENDANCE_DATE='||TO_CHAR(:NEW.attendance_date,'YYYY-MM-DD')
             ||'; STATUS='||:NEW.status
             ||'; CHECK_IN='||TO_CHAR(:NEW.check_in,'YYYY-MM-DD HH24:MI:SS')
             ||'; CHECK_OUT='||TO_CHAR(:NEW.check_out,'YYYY-MM-DD HH24:MI:SS');
   ELSIF UPDATING THEN
      v_op  := 'UPDATE';
      v_old := 'ATTENDANCE_ID='||:OLD.attendance_id
             ||'; EMPLOYEE_ID='||:OLD.employee_id
             ||'; ATTENDANCE_DATE='||TO_CHAR(:OLD.attendance_date,'YYYY-MM-DD')
             ||'; STATUS='||:OLD.status
             ||'; CHECK_IN='||TO_CHAR(:OLD.check_in,'YYYY-MM-DD HH24:MI:SS')
             ||'; CHECK_OUT='||TO_CHAR(:OLD.check_out,'YYYY-MM-DD HH24:MI:SS');
      v_new := 'ATTENDANCE_ID='||:NEW.attendance_id
             ||'; EMPLOYEE_ID='||:NEW.employee_id
             ||'; ATTENDANCE_DATE='||TO_CHAR(:NEW.attendance_date,'YYYY-MM-DD')
             ||'; STATUS='||:NEW.status
             ||'; CHECK_IN='||TO_CHAR(:NEW.check_in,'YYYY-MM-DD HH24:MI:SS')
             ||'; CHECK_OUT='||TO_CHAR(:NEW.check_out,'YYYY-MM-DD HH24:MI:SS');
   ELSIF DELETING THEN
      v_op  := 'DELETE';
      v_old := 'ATTENDANCE_ID='||:OLD.attendance_id
             ||'; EMPLOYEE_ID='||:OLD.employee_id
             ||'; ATTENDANCE_DATE='||TO_CHAR(:OLD.attendance_date,'YYYY-MM-DD')
             ||'; STATUS='||:OLD.status
             ||'; CHECK_IN='||TO_CHAR(:OLD.check_in,'YYYY-MM-DD HH24:MI:SS')
             ||'; CHECK_OUT='||TO_CHAR(:OLD.check_out,'YYYY-MM-DD HH24:MI:SS');
   END IF;

   INSERT INTO audit_log (operation_type, object_name, operation_user,
                           operation_timestamp, old_data, new_data)
   VALUES (v_op, 'ATTENDANCE', SYS_CONTEXT('USERENV','SESSION_USER'),
           SYSTIMESTAMP, v_old, v_new);
END;
/

-- ---------------------------------------------------------------------------
-- 2.3 COUNTRIES
-- ---------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER trg_audit_countries
AFTER INSERT OR UPDATE OR DELETE ON countries
FOR EACH ROW
DECLARE
   v_op  VARCHAR2(10);
   v_old CLOB;
   v_new CLOB;
BEGIN
   IF INSERTING THEN
      v_op  := 'INSERT';
      v_new := 'COUNTRY_ID='||:NEW.country_id
             ||'; COUNTRY_NAME='||:NEW.country_name
             ||'; COUNTRY_CODE='||:NEW.country_code;
   ELSIF UPDATING THEN
      v_op  := 'UPDATE';
      v_old := 'COUNTRY_ID='||:OLD.country_id
             ||'; COUNTRY_NAME='||:OLD.country_name
             ||'; COUNTRY_CODE='||:OLD.country_code;
      v_new := 'COUNTRY_ID='||:NEW.country_id
             ||'; COUNTRY_NAME='||:NEW.country_name
             ||'; COUNTRY_CODE='||:NEW.country_code;
   ELSIF DELETING THEN
      v_op  := 'DELETE';
      v_old := 'COUNTRY_ID='||:OLD.country_id
             ||'; COUNTRY_NAME='||:OLD.country_name
             ||'; COUNTRY_CODE='||:OLD.country_code;
   END IF;

   INSERT INTO audit_log (operation_type, object_name, operation_user,
                           operation_timestamp, old_data, new_data)
   VALUES (v_op, 'COUNTRIES', SYS_CONTEXT('USERENV','SESSION_USER'),
           SYSTIMESTAMP, v_old, v_new);
END;
/

-- ---------------------------------------------------------------------------
-- 2.4 DEPARTMENTS
-- ---------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER trg_audit_departments
AFTER INSERT OR UPDATE OR DELETE ON departments
FOR EACH ROW
DECLARE
   v_op  VARCHAR2(10);
   v_old CLOB;
   v_new CLOB;
BEGIN
   IF INSERTING THEN
      v_op  := 'INSERT';
      v_new := 'DEPARTMENT_ID='||:NEW.department_id
             ||'; DEPARTMENT_NAME='||:NEW.department_name
             ||'; COUNTRY_ID='||:NEW.country_id;
   ELSIF UPDATING THEN
      v_op  := 'UPDATE';
      v_old := 'DEPARTMENT_ID='||:OLD.department_id
             ||'; DEPARTMENT_NAME='||:OLD.department_name
             ||'; COUNTRY_ID='||:OLD.country_id;
      v_new := 'DEPARTMENT_ID='||:NEW.department_id
             ||'; DEPARTMENT_NAME='||:NEW.department_name
             ||'; COUNTRY_ID='||:NEW.country_id;
   ELSIF DELETING THEN
      v_op  := 'DELETE';
      v_old := 'DEPARTMENT_ID='||:OLD.department_id
             ||'; DEPARTMENT_NAME='||:OLD.department_name
             ||'; COUNTRY_ID='||:OLD.country_id;
   END IF;

   INSERT INTO audit_log (operation_type, object_name, operation_user,
                           operation_timestamp, old_data, new_data)
   VALUES (v_op, 'DEPARTMENTS', SYS_CONTEXT('USERENV','SESSION_USER'),
           SYSTIMESTAMP, v_old, v_new);
END;
/

-- ---------------------------------------------------------------------------
-- 2.5 EMPLOYEES
-- ---------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER trg_audit_employees
AFTER INSERT OR UPDATE OR DELETE ON employees
FOR EACH ROW
DECLARE
   v_op  VARCHAR2(10);
   v_old CLOB;
   v_new CLOB;
BEGIN
   IF INSERTING THEN
      v_op  := 'INSERT';
      v_new := 'EMPLOYEE_ID='||:NEW.employee_id
             ||'; FIRST_NAME='||:NEW.first_name
             ||'; LAST_NAME='||:NEW.last_name
             ||'; EMAIL='||:NEW.email
             ||'; HIRE_DATE='||TO_CHAR(:NEW.hire_date,'YYYY-MM-DD')
             ||'; SALARY='||:NEW.salary
             ||'; DEPARTMENT_ID='||:NEW.department_id
             ||'; ROLE_ID='||:NEW.role_id;
   ELSIF UPDATING THEN
      v_op  := 'UPDATE';
      v_old := 'EMPLOYEE_ID='||:OLD.employee_id
             ||'; FIRST_NAME='||:OLD.first_name
             ||'; LAST_NAME='||:OLD.last_name
             ||'; EMAIL='||:OLD.email
             ||'; HIRE_DATE='||TO_CHAR(:OLD.hire_date,'YYYY-MM-DD')
             ||'; SALARY='||:OLD.salary
             ||'; DEPARTMENT_ID='||:OLD.department_id
             ||'; ROLE_ID='||:OLD.role_id;
      v_new := 'EMPLOYEE_ID='||:NEW.employee_id
             ||'; FIRST_NAME='||:NEW.first_name
             ||'; LAST_NAME='||:NEW.last_name
             ||'; EMAIL='||:NEW.email
             ||'; HIRE_DATE='||TO_CHAR(:NEW.hire_date,'YYYY-MM-DD')
             ||'; SALARY='||:NEW.salary
             ||'; DEPARTMENT_ID='||:NEW.department_id
             ||'; ROLE_ID='||:NEW.role_id;
   ELSIF DELETING THEN
      v_op  := 'DELETE';
      v_old := 'EMPLOYEE_ID='||:OLD.employee_id
             ||'; FIRST_NAME='||:OLD.first_name
             ||'; LAST_NAME='||:OLD.last_name
             ||'; EMAIL='||:OLD.email
             ||'; HIRE_DATE='||TO_CHAR(:OLD.hire_date,'YYYY-MM-DD')
             ||'; SALARY='||:OLD.salary
             ||'; DEPARTMENT_ID='||:OLD.department_id
             ||'; ROLE_ID='||:OLD.role_id;
   END IF;

   INSERT INTO audit_log (operation_type, object_name, operation_user,
                           operation_timestamp, old_data, new_data)
   VALUES (v_op, 'EMPLOYEES', SYS_CONTEXT('USERENV','SESSION_USER'),
           SYSTIMESTAMP, v_old, v_new);
END;
/

-- ---------------------------------------------------------------------------
-- 2.6 ROLES
-- ---------------------------------------------------------------------------
CREATE OR REPLACE TRIGGER trg_audit_roles
AFTER INSERT OR UPDATE OR DELETE ON roles
FOR EACH ROW
DECLARE
   v_op  VARCHAR2(10);
   v_old CLOB;
   v_new CLOB;
BEGIN
   IF INSERTING THEN
      v_op  := 'INSERT';
      v_new := 'ROLE_ID='||:NEW.role_id
             ||'; ROLE_NAME='||:NEW.role_name
             ||'; ROLE_DESC='||:NEW.role_desc;
   ELSIF UPDATING THEN
      v_op  := 'UPDATE';
      v_old := 'ROLE_ID='||:OLD.role_id
             ||'; ROLE_NAME='||:OLD.role_name
             ||'; ROLE_DESC='||:OLD.role_desc;
      v_new := 'ROLE_ID='||:NEW.role_id
             ||'; ROLE_NAME='||:NEW.role_name
             ||'; ROLE_DESC='||:NEW.role_desc;
   ELSIF DELETING THEN
      v_op  := 'DELETE';
      v_old := 'ROLE_ID='||:OLD.role_id
             ||'; ROLE_NAME='||:OLD.role_name
             ||'; ROLE_DESC='||:OLD.role_desc;
   END IF;

   INSERT INTO audit_log (operation_type, object_name, operation_user,
                           operation_timestamp, old_data, new_data)
   VALUES (v_op, 'ROLES', SYS_CONTEXT('USERENV','SESSION_USER'),
           SYSTIMESTAMP, v_old, v_new);
END;
/

/*
================================================================================
 TASK 3: STORED PROCEDURES WITH EXCEPTION HANDLING  (25 Marks)
================================================================================
*/

-- ---------------------------------------------------------------------------
-- 3.1 sp_update_allowance : Update Allowance
-- ---------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE sp_update_allowance (
   p_allowance_id   IN allowances.allowance_id%TYPE,
   p_allowance_type IN allowances.allowance_type%TYPE,
   p_amount         IN allowances.amount%TYPE
) IS
   e_invalid_amount EXCEPTION;
BEGIN
   IF p_amount IS NULL OR p_amount < 0 THEN
      RAISE e_invalid_amount;
   END IF;

   UPDATE allowances
      SET allowance_type = p_allowance_type,
          amount         = p_amount,
          effective_date = SYSDATE
    WHERE allowance_id = p_allowance_id;

   IF SQL%ROWCOUNT = 0 THEN
      RAISE NO_DATA_FOUND;
   END IF;

   COMMIT;
   DBMS_OUTPUT.PUT_LINE('Allowance '||p_allowance_id||' updated successfully.');

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      ROLLBACK;
      INSERT INTO error_log (procedure_name, error_message, error_code, error_timestamp)
      VALUES ('SP_UPDATE_ALLOWANCE',
              'No allowance found with allowance_id = '||p_allowance_id,
              SQLCODE, SYSTIMESTAMP);
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Error: allowance_id '||p_allowance_id||' does not exist. Logged to ERROR_LOG.');

   WHEN e_invalid_amount THEN
      ROLLBACK;
      INSERT INTO error_log (procedure_name, error_message, error_code, error_timestamp)
      VALUES ('SP_UPDATE_ALLOWANCE',
              'Invalid amount supplied for allowance_id = '||p_allowance_id,
              -20001, SYSTIMESTAMP);
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Error: invalid amount. Logged to ERROR_LOG.');

   WHEN OTHERS THEN
      ROLLBACK;
      INSERT INTO error_log (procedure_name, error_message, error_code, error_timestamp)
      VALUES ('SP_UPDATE_ALLOWANCE', SQLERRM, SQLCODE, SYSTIMESTAMP);
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Unexpected error logged to ERROR_LOG: '||SQLERRM);
END sp_update_allowance;
/

-- ---------------------------------------------------------------------------
-- 3.2 sp_delete_allowance : Delete Allowance
-- ---------------------------------------------------------------------------
CREATE OR REPLACE PROCEDURE sp_delete_allowance (
   p_allowance_id IN allowances.allowance_id%TYPE
) IS
BEGIN
   DELETE FROM allowances
    WHERE allowance_id = p_allowance_id;

   IF SQL%ROWCOUNT = 0 THEN
      RAISE NO_DATA_FOUND;
   END IF;

   COMMIT;
   DBMS_OUTPUT.PUT_LINE('Allowance '||p_allowance_id||' deleted successfully.');

EXCEPTION
   WHEN NO_DATA_FOUND THEN
      ROLLBACK;
      INSERT INTO error_log (procedure_name, error_message, error_code, error_timestamp)
      VALUES ('SP_DELETE_ALLOWANCE',
              'No allowance found with allowance_id = '||p_allowance_id,
              SQLCODE, SYSTIMESTAMP);
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Error: allowance_id '||p_allowance_id||' does not exist. Logged to ERROR_LOG.');

   WHEN OTHERS THEN
      ROLLBACK;
      INSERT INTO error_log (procedure_name, error_message, error_code, error_timestamp)
      VALUES ('SP_DELETE_ALLOWANCE', SQLERRM, SQLCODE, SYSTIMESTAMP);
      COMMIT;
      DBMS_OUTPUT.PUT_LINE('Unexpected error logged to ERROR_LOG: '||SQLERRM);
END sp_delete_allowance;
/

/*
================================================================================
 TASK 4: TESTING AND VALIDATION  (25 Marks)
================================================================================
*/

-- ---------------------------------------------------------------------------
-- Setup: minimal reference data so ALLOWANCES has a valid employee to point to
-- ---------------------------------------------------------------------------
INSERT INTO countries (country_name, country_code) VALUES ('Rwanda', 'RW');
INSERT INTO departments (department_name, country_id) VALUES ('Finance', 1);
INSERT INTO roles (role_name, role_desc) VALUES ('Teller', 'Front-desk banking teller');
INSERT INTO employees (first_name, last_name, email, salary, department_id, role_id)
VALUES ('Aline', 'Uwase', 'aline.uwase@bank.com', 450000, 1, 1);
COMMIT;

-- ---------------------------------------------------------------------------
-- Test 1: INSERT
-- ---------------------------------------------------------------------------
DECLARE
   v_new_allowance_id allowances.allowance_id%TYPE;
BEGIN
   INSERT INTO allowances (employee_id, allowance_type, amount, effective_date)
   VALUES (1, 'Transport', 50000, SYSDATE)
   RETURNING allowance_id INTO v_new_allowance_id;

   COMMIT;
   DBMS_OUTPUT.PUT_LINE('Test 1 (INSERT): new allowance_id = '||v_new_allowance_id);
END;
/

-- ---------------------------------------------------------------------------
-- Test 2: UPDATE (via stored procedure) -- updates allowance_id 1
-- ---------------------------------------------------------------------------
BEGIN
   sp_update_allowance(p_allowance_id => 1, p_allowance_type => 'Housing', p_amount => 75000);
END;
/

-- ---------------------------------------------------------------------------
-- Test 3: DELETE (via stored procedure) -- deletes allowance_id 1
-- ---------------------------------------------------------------------------
BEGIN
   sp_delete_allowance(p_allowance_id => 1);
END;
/

-- ---------------------------------------------------------------------------
-- Test 4: Error handling -- invalid UPDATE and DELETE (non-existent id)
-- ---------------------------------------------------------------------------
BEGIN
   sp_update_allowance(p_allowance_id => 9999, p_allowance_type => 'Meal', p_amount => 20000);
END;
/

BEGIN
   sp_delete_allowance(p_allowance_id => 9999);
END;
/

/*
================================================================================
 DELIVERABLE 8: VERIFICATION QUERIES
================================================================================
*/

-- Contents of the AUDIT table (most recent first)
COLUMN operation_type       FORMAT A10
COLUMN object_name          FORMAT A12
COLUMN operation_user       FORMAT A15
COLUMN operation_timestamp  FORMAT A26
SELECT audit_id, operation_type, object_name, operation_user,
       TO_CHAR(operation_timestamp,'YYYY-MM-DD HH24:MI:SS') AS operation_timestamp,
       old_data, new_data
  FROM audit_log
 ORDER BY audit_id;

-- Contents of the ERROR_LOG table
COLUMN procedure_name FORMAT A25
COLUMN error_message  FORMAT A50
SELECT error_id, procedure_name, error_message, error_code,
       TO_CHAR(error_timestamp,'YYYY-MM-DD HH24:MI:SS') AS error_timestamp
  FROM error_log
 ORDER BY error_id;

/*
================================================================================
 END OF SCRIPT
================================================================================
*/