Developing Time-Oriented Applications in SQL | Richard T. Snodgrass

### Oracle8 Server Implementation Examples

### Retaining An Audit Trail

#

## Contents at a Glance

> Implementation Details
> Defining the Audit Trail
> Queries and Modifications

> > Extracting a Prior State
> > Other Queries

> Permitting Insertions
> Backlogs
> Using After Images Consistently
> Running Examples

#

## Detailed Contents

This document outlines the implementation of an audit trail for Chapter 9 using the Oracle DBMS. It follows the general outline of the TDB book and is arranged in the following manner. All code samples are numbered as in the chapter when taken from the chapter.

> Implementation Details
> Defining the Audit Trail

> > Create the audit trail table
> > Create the triggers

> Queries and Modifications

> > Extracting a Prior State

> > > Execute the transactions
> > > Reconstruction Algorithm 1

> > Other Queries

> > > Reconstruction Algorithm 1 as a View
> > > Convert PROJECTIONS to a transaction state table

> Permitting Insertions

> > Create an INSERT trigger
> > Reconstruction Algorithm 2

> Backlogs

> > Reconstruction Algorithm 3

> Using After Images Consistently

> > Create the after image triggers
> > Reconstruction Algorithm 4
> > Transaction state table using after image
> > Reconstruct current table using after images
> > Define PROJECTIONS as a view on P_Audit

> Running Examples

##

## Implementation Details (Oracle8 Server)

Oracle's syntax contains a few small deviations from the standard. Most significantly, all SQL statements must be terminated with a semicolon. This includes the individual statements nested in larger DDL statements like CREATE TRIGGER. Secondly, the current date is stored in the ghost column SYSDATE, which is silently considered a column of each table in the system. Oracle also does not support date literals, so dates must be created using the TO_DATE function. TO_DATE takes two string parameters: the string of characters representing the date value and a pattern string. In all examples with TO_DATE in this chapter the pattern string 'MM/DD/YYYY' is used, indicating a two digit month, a two digit day, and a four digit year delimited by forward slashes.

Since triggers are used extensively in this chapter, a few points should be clarified regarding the trigger syntax in Oracle. An example of a CREATE TRIGGER statement in Oracle is provided below.

CREATE TRIGGER Update_PROJECTIONS
AFTER UPDATE ON PROJECTIONS
FOR EACH ROW
BEGIN
INSERT INTO P_Audit VALUES (:old.PROJECTION_ID,
:old.PROJECTION_NAME,
:old.PROJECTION_TYPE,
:old.SPHEROID_CODE,
:old.PROJECTION_UOM,
:old.ZONE_CODE,
SYSDATE);
END;
/

Notice the trailing slash. Without the trailing slash Oracle will refuse to create the trigger. This requirement does not appear in any documentation I've seen and learning of the requirement by trial and error is nearly impossible. Also notice the two semicolons as required by Oracle. In trigger definitions in Oracle, the keyword :old refers to the triggering row of the trigger's table as it appeared before the triggering event. :new refers to the modified row.

A final note regarding triggers is that a trigger may be dropped using the following statement:

DROP TRIGGER trigger_name;

#

##

## Defining the Audit Trail (Oracle8 Server)

An audit trail specifies the sequence of modifications to a single table, the audited table. The audit trail consists of information about each modification, such as the date when it occurred. This allows the audited table to be reconstructed at any given time in the past. We define the audit trail for the PROJECTIONS table as the P_Audit table.

> SQL to create the audit trail table (9.1.sql)
> SQL to create the PROJECTIONS table (Create_Table_Projections.sql)

The audit trail table contains all the columns from the PROJECTIONS table as well as the When_Changed column, which indicates the date on which the values in the row were removed or changed. The key of the audit table is PROJECTION_ID and When_Changed. In order to track modifications to the PROJECTIONS table, we establish triggers which work behind the scenes. We define two triggers, DELETE and UPDATE, on the audited table.

> SQL to create the triggers(9.2.sql)

In both cases, the values stored in the audit trail table are the old values. Since inserted values can be found in the audited table, there is no need for an INSERT trigger.

#

##

## Queries and Modifications (Oracle8 Server)

### Extracting a Prior State

The audit trail allows us to reconstruct the state of the PROJECTIONS table at any day in the past. The following transactions are executed on the PROJECTIONS table.

> Execute the transactions (9.2.Data.sql)
> View the results (9.2.Data.Results.txt)

We now wish to reconstruct the table as of April 1, 1996, that is, to see the table as it existed between transactions 7 and 8. The code to construct the table is provided below, along with the reconstructed table.

> Reconstruction Algorithm 1 (9.3.sql)
> Reconstructed Table (9.3.Results.txt)

### Other Queries

Complex queries on reconstructed data are best realized by forming a view of the reconstructed data. The following code creates such a view. The view is then queried to list the information (PROJECTION_ID and PROJECTION_TYPE) on projection type 12 as of April 1, 1996.

> Reconstruction Algorithm as a View (9.4.sql)
> Reconstructed View Query (9.5.sql) and query results (9.5.Results.txt)

The easiest way to perform sequenced and nonsequenced queries on an audit trail is to convert it to a transaction state table, with start and stop transaction timestamps. The following code creates a view of PROJECTIONS as a transaction state table. An example query on the transaction state table is then provided: When was it recorded that projection type 12 had a particular spheroid code?

> Transaction State Table View (9.6.sql)
> State Table Query (9.7.sql) and query results (9.7.Results.sql)

#

##

## Permitting Insertions (Oracle8 Server)

The previous reconstruction makes the assumption that the table was originally constituted with insertions and any subsequent changes were either updates or deletions. Hence, no insertions are allowed after any updates or deletes. To remove this assumption, we define an INSERT trigger to include insertions in the audit trail.

> SQL to create the INSERT trigger (9.8.sql)

Suppose an insertion of a new projection, number 6, was made on June 29, 1996. The reconstruction code in the previous section would include this projection in the reconstructed table as of April 1, 1996. We now add this transaction to the previous set of transactions and then execute the following reconstruction algorithm. The reconstructed table is also provided below.

> Data to reconstruct the projections table (9.9.Data.sql)
> Query on the reconstructed table (9.9.sql) and query results (9.9.Results.txt)

#

##

## Backlogs (Oracle8 Server)

Although the trigger in the previous section allows insertions to the PROJECTIONS table other than at the beginning, it does not allow insertions after a deletion. Currently a deletion followed by an insertion of the same projection is indistinguishable from two sequential updates in the audit trail. To differentiate a deletion from an update, we create a backlog, which is an audit trail containing a column that indicates which operation was done. We therefore add the Operation column to P_Audit and update the triggers to reflect the additional column as well. The following code executes a set of transactions (which includes deletions and insertions of the same projection) and then reconstructs the PROJECTIONS table as of April 1, 1996. The reconstructed table is also provided.

> SQL to add the operation column to P_Audit (9.10.Add_Operation_Column.sql) , change the triggers (9.10.Change_Triggers.sql) , and update the data (9.10.Data.sql)
> SQL to Reconstruct the table (9.10.sql) and results (9.10.Results.txt)

This code is somewhat complex because a combination of before images (previous values of the row) and after images (new values of the row) appear in P_Audit. Specifically, the UPDATE and DELETE triggers store before images and the INSERT trigger stores after images.

#

##

## Using After Images Consistently (Oracle8 Server)

The reconstruction algorithm can be simplified considerably if the DELETE and UPDATE triggers use after images consistently. Specifically, the DELETE trigger need not store any values, because the after image is not defined for deletions. The UPDATE trigger is modified to store the new row values. The INSERT trigger may be retained, as it already records the after image. The following code creates the modified DELETE and UPDATE triggers.

> SQL to create the after image triggers (9.11.sql)
> SQL code to restore the sample data (9.10.Data.sql)

The following code reconstructs the PROJECTIONS table using after images. The result is shown.

> Reconstruction Algorithm 4 (9.12.sql)
> Reconstructed Table (9.12.Results.txt)

Using only after images also changes the transaction-state view. The following code creates a view of PROJECTIONS as a transaction state table using after images.

> Transaction State Table View using After Images (9.13.sql)
> After Image State Table Query (9.13.Select.Results.txt)

P_Audit now contains all of PROJECTIONS, allowing the current version to be computed even more easily. The following code reconstructs the current version of the table.

> Reconstruct the current version (9.15.sql)
> Reconstructed current table (9.15.Result.txt)

We can also define the PROJECTIONS table as a view on P_Audit to achieve space savings, as the current information would not be in both the audited table and the backlog. However, this will require more expensive retrieval of that information. The following code defines PROJECTIONS as a view on P_Audit.

> PROJECTIONS as a view of P_Audit (9.16.sql)
> P_Audit view results (9.16.Select.Results.txt)

#

##

## Running Examples (Oracle8 Server)

The example code in this chapter is included in several *.sql files. These files are named with the number of the corresponding code fragment from the chapter or with a simple description of their function. These files can be run in the Oracle command line utility sqlplus. Starting sqlplus will require a user name and password. When sqlplus is ready for commands it will give the prompt:

SQL>

From this prompt the user can enter valid SQL or commands directly to sqlplus. Running the sample code is done with the start command. Ie, to run the code fragment 9.1 type:

SQL> [path to examples]/start 9.1.sql

#### HTML Credits:

Jose Alvin G. Gendrano, Department of Computer Science, University of Arizona (jag@cs.arizona.edu)
Jian Yang, Department of Computer Science, University of Arizona (yangjian@cs.arizona.edu)
April 28, 1999 (Last Update)

#### DB2 Specific Code Credits:

Helen M. Thomas, Department of Management Information Systems, University of Arizona (helen@loochi.bpa.arizona.edu)
December 3, 1997 (Last Update)