System for Auditing of Table Data Changes Using Triggers

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):

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").

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.

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.

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.

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.

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

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');".

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.)

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.

This Page was Last Updated on 01/12/07

You Are Visitor Number

University of Arkansas • Division of Agriculture
Cooperative Extension Service
2301 South University Avenue
Little Rock, Arkansas 72204 • USA
Phone (501) 671-2000 • Fax (501) 671-2209

Mission • Disclaimer • EEO •
Privacy • FOI

Pictures of chickens, flowers, wheat, a boy looking through a magnifying glass, irrigation pipe, soybean pods, and fruits and vegetables.		Cooperative Extension Service





Cooperative Extension Service		Agricultural Experiment Station
Steve Rea's Oracle Tips, Tricks, and Scripts		Search \| Publications \| Jobs \| Personnel Directory \| Links