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

### Retaining An Audit Trail

### Sybase SQL Server Implementation Examples

###

#

## Contents at a Glance

> Trigger Syntax
> Defining an Audit Trail
> Reconstructing a Previous State
> Permitting Insertions
> Backlogs
> Using After Images Consistently
> Running Examples

##

##

## Trigger Syntax

TRIGGERS are system objects that provide the mechanism for the insertion of user-defined operations during the processing of a database request.

### Creating a Trigger in Sybase SQL Server

A SQL/X Trigger can be created to invoke an action in response to a specific database activity. As soon as the given activity is detected in the database, the trigger referencing that activity is notified or raised. A raised trigger can then initiate another database event. Two special tables are used in trigger statements: the deleted table and the inserted table. These are temporary tables used in trigger tests. Use these tables to test the effects of some data modification and to set conditions for trigger actions. You cannot directly alter the data in the trigger test tables, but you can use the tables in select statements to detect the effects of an insert, update or delete.

> Syntax

>             CREATE TRIGGER trigger_name
>             ON table_name
>     	FOR {insert, update,delete}
>     	AS SQL_statements

##

##

## Defining an Audit Trail in Sybase SQL Server

An Audit Trail specifies the sequence of modifications to a single table, the audited table. It contains information about the change, such as when the modification occurred , or who performed the modification, or which task effected the change. The audit trail permits the contents of the audited table to be reconstituted as of any time in the past.

    CREATE TABLE P_Audit (
            PROJECTION_ID   INT,
            PROJECTION_NAME INT,
            PROJECTION_TYPE INT,
            SPHEROID_CODE   INT,
            PROJECTION_UOM  INT,
            ZONE_CODE       INT ,
            When_Changed    DATE,
            PRIMARY KEY  (PROJECTION_ID, When_Changed)
            );

The When_Changed column indicates the date on which the values in the row were removed or changed in the PROJECTIONS table. The key of the audit table is the PROJECTION_ID along with the When_Changed column. The When_Changed is of type DATE and so each projection can be updated at most once per day.

We wish to retain the code for the PROJECTIONS table as it is and so the table P_Audit is maintained via triggers, working behind the scene. We define 2 triggers DELETE and UPDATE, on the audited table.

    CREATE TRIGGER Delete_PROJECTIONS
    ON PROJECTIONS
    FOR DELETE
    AS
    INSERT P_Audit
    SELECT *,GETDATE()
    FROM deleted

    CREATE TRIGGER Update_PROJECTIONS
    AFTER UPDATE ON PROJECTIONS
    ON PROJECTIONS
    FOR UPDATE
    AS
    INSERT P_Audit
    SELECT *,GETDATE()
    FROM deleted

    A system date is inserted first into the P_Audit table. This date is
    later updated by the date when the data has been changed.

In both the cases the values stored are the old values. These triggers assume that no other modifications are made to the P_Audit table, other than through the SQL operations INSERT, DELETE, UPDATE.

## Reconstructing a Previous State in Sybase SQL Server

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

> Transactions(trans)
> Results(trans.out)

The audit trail is append only, no rows are ever updated or deleted.

Now we wish to reconstruct the table as of a particular day in the past e.g: April 1, 1996.

> Trigger Definition, Transaction And Reconstruction Algorithm 1(reco1)
> Reconstructed Table(reco1.out)

It's not allowed to use UNION inside CREATE VIEW, so there are no CF9.4 and CF9.5.

## Permitting Insertions in Sybase SQL Server

There were certain assumptions made that the table was originally constitute d with insertions, and thereafter the only changes were updates and deletes. To remove this assumption, we include an INSERT trigger.

    CREATE TRIGGER Insert_PROJECTIONS
    ON PROJECTIONS
    FOR DELETE
    AS
    INSERT P_Audit
    SELECT *,GETDATE()
    FROM inserted

A similar action of inserting a system date, updating it by the date when the data has been changed.

Having insertions, increases the size of the audit table. Here again, we reconstruct the table as of April 1, 1996.

> Trigger Definition, Transaction And Reconstruction Algorithm 2(reco2)
> Reconstructed Table(reco2.out)

## Backlogs in Sybase SQL Server

Even though the trigger allows insertions to the PROJECTION table other than at the beginning, it still does not allow insertion after a deletion. One cannot distinguish a deletion followed by an insertion from 2 sequential updates just by looking at the table.

Hence we add a a column to P_audit that indicates which operation was performed. Such an audit trail is called a backlog. The triggers must be changed also to store the operation code for Delete, Update and Insert.

In Sybase, a column added with alter table is not visible to stored procedures that select * from that table. To reference the new column, drop the procedures and recreate them, so we recreate a P_Audit table.

    CREATE TABLE P_Audit (
            PROJECTION_ID   INT,
            PROJECTION_NAME INT,
            PROJECTION_TYPE INT,
            SPHEROID_CODE   INT,
            PROJECTION_UOM  INT,
            ZONE_CODE       INT ,
            When_Changed    DATE,
    	Operation       CHAR(1),
            PRIMARY KEY  (PROJECTION_ID, When_Changed)
            );

We reconstruct the Table as of April 1, 1997

> Trigger Definition, Transaction And Reconstruction Algorithm 3(reco3)
> Reconstructed Table(reco3.out)

This code is a combination of before images, where the previous values of the row are stored and after images, where the new values are stored. Before images are stored by the DELETE and UPDATE triggers and the after images are stored by the INSERT trigger.

## Using After Images Consistently in Sybase SQL Server

The reconstruction algorithm can be simplified considerably if the DELETE and UPDATE triggers use the after images consistently. The former need not insert any values, because the after image is not defined for deletions. The INSERT trigger may be retained as it already records the after image.

We reconstruct the Table as of Dec. 5, 1997

> Trigger Definition, Transaction And Reconstruction Algorithm 4(reco4)
> Reconstructed Table(reco4.out)

## Running Examples in Sybase SQL Server

All the queries in this chapter are in trans and reco* files, which are Sybase SQL source code files. Notice, all 'use fall97_331' statement will have to be changed to an available database. All results for each file are in *.out files. All source code file run under Sybase Serve 10. You can run each file by using the following command:

> isql -U[accountname] -P[password] -i [input file name] -o [output filename]

For example, if you want to run reco1 file, using following command:

> isql -U[accountname] -P[password] -i reco1 -o reco1.out

The result will be stored in reco1.out file.

References for Sybase Server 10

> SQL Server Reference ManualVolume I and II
> SQL Server Transact-SQL User's Guide

#### HTML Credits

Jose Alvin G. Gendrano, Department of Computer Science, University of Arizona (jag@cs.arizona.edu)
Yi-Jin Shi, Department of Computer Science, University of Arizona (shi@cs.arizona.edu)
Rachana R. Shah, Department of Computer Science, University of Arizona (rachana@cs.arizona.edu)
Jian Yang, Department of Computer Science, University of Arizona (yangjian@cs.arizona.edu)
April 27, 1999 (Last Update)

#### Sybase Specific Code Credits:

Yi-Jin Shi, Department of Computer Science, University of Arizona (shi@cs.arizona.edu)
December 16, 1997 (Last Update)