System for Auditing of Table Data Changes Using Triggers
(Specific to Banner products)

Here is a system of Unix and SQL scripts I derived back in 1997 (from scripts by Daniel Booth and Dawn Wisniewski) to generate an audit trail of table field data changes and to create reports about those changes (dbc = database changes). You can use it to generate reports on any of several named subsets of fields that you set up by associating the names of the reports with the individual fields (such as an "all" report for all fields, and an "audit" report for a specified subset of those fields), and those reports can be full detailed reports or brief reports with less detail, and can be for all users or a specified user making the changes.

You will need to be very selective on what tables and fields you audit with this.  Otherwise, it could overwhelm you with massive amounts of data.  We are currently auditing an average of 10 fields in 12 tables.  Below are the steps and scripts to run to install and use the dbc audit system:

The following SQL and Unix .shl scripts are used by the dbc audit system (click on the highlighted or underlined script name to download it, and edit them to match your directory structure before you try to run them):

    dbc_create_tables.sql
    dbc_gen_triggers.sql
    dbc_gen_chg_trigger.shl
    dbc_gen_id_trigger.shl
    dbc_gen_report_set.shl
    dbc_report_set.sql
    dbc_audit_rpt.shl
    dbc_audit_user_rpt.sql
    dbc_audit_userb_rpt.sql
    dbc_audit_all_rpt.sql
    dbc_audit_allb_rpt.sql
    dbc_totals.sql
    dbc_disable_triggers.sql
    dbc_enable_triggers.sql
    dbc_drop_triggers.sql

1. Run dbc_create_tables.sql (in sqlplus) from userid system to create the audit tables dbc_changes and dbc_columns.  The dbc_changes table holds the list of individual field changes from the audit triggers, and the dbc_columns table contains the list of field names being audited (the table name must be included in the field name in the form of mytable_myfield), along with the set of report names associated with each field (defaulting to "all,audit").

2. Run dbc_gen_triggers.sql to generate the change triggers (using dbc_gen_chg_trigger.shl), insert/delete triggers (using dbc_gen_id_trigger.shl), and default field report sets (using dbc_gen_report_set.shl) for a given table. This will generate the files dbc_gen_chg_trigger.sql, dbc_gen_id_trigger.sql, and dbc_gen_report_set.sql, with triggers for all auditable fields (character (char's and varchar's), numeric, and date fields). For more than one table, run this script and the resulting generated files (steps 2 and 3) for each table that you want audited.

3. Have dbc_gen_triggers.sql run the full generated SQL for you, or, edit those generated files to remove any fields that you don't want audited (remove them from all three generated files) in that given table, and run those edited generated files individually to actually create the triggers.

4. If you want to modify the default report subset names, run dbc_report_set.sql to change the report subset names (in lower case, and separated by commas) for each individual field in a given audited table name from the original defaults of "audit,all". To keep from typing the set of report subset names for each field, you can also enter your own default set of names, which will be entered whenever you press "Y" for a field.

5. To generate the audit report, run dbc_audit_rpt.shl (from the unix prompt), giving it a date range when prompted, and, optionally, a given userid (else, for everyone), and a given named report subset of fields, such as audit or all (else, for all fields; in lower case), and a given level of detail (full or brief).   You'll also need to enter your Banner userid and password to run the included SQL scripts.  This script will run the following SQL scripts, based on the responses given: dbc_audit_user_rpt.sql, dbc_audit_userb_rpt.sql, dbc_audit_all_rpt.sql, and dbc_audit_allb_rpt.sql, and will create the report in dbc_audit_rpt.lst.

6. To generate an overview count of user changes for records currently in the dbc_changes table, run dbc_totals.sql (from sqlplus).

7. If you no longer need to run reports on an old set of audit records, you can just delete those old records in dbc_changes through sql by using something like "delete from system.dbc_changes where dbc_chg_date < to_date('17-Feb-99');".

8. When you have finished auditing, run dbc_disable_triggers.sql to generate the SQL to disable all the dbc triggers and have dbc_disable_triggers.sql run the full generated SQL for you, or, edit the generated dbc_disable_triggers_do.sql file to remove any triggers that you don't want disabled and run that edited file to actually disable the triggers. (You may want to just drop all of those dbc triggers by running dbc_drop_triggers.sql if you are never going to use them any more, since they probably incur some overhead even when they are disabled.)

9. If you want to start auditing again, run dbc_enable_triggers.sql to generate the SQL to enable all the dbc triggers and have dbc_enable_triggers.sql run the full generated SQL for you, or, edit the generated dbc_enable_triggers_do.sql file to remove any triggers that you don't want enabled and run that edited file to actually enable the triggers.

Copyright © 2009 by Maristream.   All information, scripts, forms, and other material
on this web site are freely available to all Banner and Oracle Database Administrators,
Systems Administrators, Programmers, and others that may need it.

The webmaster who maintains this web site may be reached at srea@maristream.org. Visit our other web sites:

Disclaimer:  As with all software, especially where it affects your vital data, make sure that you examine theses scripts and that you understand what they do before you use them to see if they would have any adverse effect on your particular setup or database layout.  Make a full backup of your database in case you have to revert to your original copy of the database before the scripts were run.  Use these scripts at your own risk.  As a condition of using these scripts, you agree to hold harmless both Maristream and Stephen Rea for any problems that they may cause or other situations that may arise from their use, and that neither Maristream nor I will be held liable for those consequences.