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

### Retaining An Audit Trail

### UniSQL Implementation Examples

#

## Contents at a Glance

> Trigger Syntax

> > Creating a Trigger
> > Correlation Names
> > Trigger Debugging
> > Renaming Triggers
> > Altering Triggers
> > Dropping Triggers

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

##

##

## Triggers

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 UniSQL

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, t he trigger referencing that activity is notified or raised. A raised trigger can then initiate another database event. The primary functional components of a trigger include the name, event time, event type, event target, condition and action.

> Syntax

>             CREATE TRIGGER trigger_name
>             [STATUS {ACTIVE|INACTIVE}]
>             [PRIORITY key]
>             event_time event_type [event_target]
>             [IF condition]
>             EXECUTE [ AFTER | DEFERRED ] action[;]

>             event_time:
>             BEFORE
>             AFTER
>             DEFERRED

>             event_type:
>             DELETE
>             STATEMENT DELETE
>             DELETE
>             STATEMENT DELETE
>             INSERT
>             STATEMENT INSERT
>             UPDATE
>             STATEMENT UPDATE
>             ROLLBACK
>             COMMIT

>             event_target:
>             ON class_name
>             ON class_name([ CLASS ]attribute_name)

>             condition:
>             expression

>             action:
>             REJECT
>             INVALIDATE TRANSACTION
>             PRINT message_string
>             CALL statement
>             INSERT statement
>             UPDATE statement
>             DELETE statement
>             EVALUATE statement

The name you assign to a trigger must be unique among all triggers. However, a t rigger can have the same name as another non-trigger entity in the database.

### Correlation Names in UniSQL

Using correlation names in the trigger definition provides access to the values of attributes defined in the target class. They represent the instance that is being affected by the database operation that raised the trigger. They are recognized by the sys tem as new, old and obj. These names can be used only the triggers have an event type of INSERT, UPDATE or DELETE.. The use is further restricted as shown.

| BEFORE | AFTER or DEFERRED
--- | --- | ---
INSERT | new | obj
UPDATE | obj new | obj old(AFTER)
DELETE | obj | NA

To use a correlation name in the event or an action clause of trigger definite ion, specify it as the start of a path expression involving attributes of the target class. e.g. old.EMP_NAME.

The obj name refers to the current value of an instance. This is used to access the attribute before it is updated or deleted. It is also used to access attribute values after an instance has been updated or inserted.

The new refers to the attribute values proposed by an insert or update operation. The new values are only accessible before an instance is actually up dated or inserted.

the old name refers to the attribute values that existed prior to the completion of an update operation. The values are retained only for the duration of the trigger evaluation. Once the trigger has finished, the old values are lost.

### Trigger Debugging in UniSQL

After a trigger has been defined, a good practice is to check that it works as intended for the application.

### Tracing a Trigger

The TRACE parameter can be used with the SET TRIGGER command to display a lot of trigger executions to the terminal. When TRACE is enabled, a message is sent to the terminal each time a trigger condition and action a re evaluated. he syntax is as shown below :

>     Syntax

>             SET TRIGGER TRACE switch [;]

>             switch:
>             ON
>             OFF

The TRACE capability will go into effect until this command is used to turn the switch ON. Once tracing is enable, it can only be turned off by issuing the command with the OFF switch, or by quitting the current database session.

### Renaming Triggers in UniSQL

The name of a trigger can be changed in the same way that a class is renamed. The RENAME statement allows you to specify the name of an existing trigger and assign it a new name.

>     Syntax

>             RENAME TRIGGER old_trigger_name AS new_trigger_name [;]

Because a trigger name can be the same as a class name, the TRIGGER keyword is required when you rename a trigger. Renaming a trigger does not require any associated changes to the database schema, nor will it affect the raising o f the trigger.

If the trigger you are renaming is a class trigger, you must either be the owner of the trigger or have been granted alter authorization on the class in the trigger's event target. You can only rename a trigger if you are the trigger's owner.

### Altering Triggers in UniSQL

The STATUS and PRIORITY option s in a trigger can be changed b y using the proper from of the ALTER command. They are the only two parts of the trigger that can be altered. If another part needs to be changed, then t he trigger has to dropped and recreated.

>     Syntax

>             ALTER TRIGGER trigger_name trigger_option [;]

>             trigger_option
>             STATUS { ACTIVE | INACTIVE }
>             PRIORITY key

Only one option can be specified in the same ALTER TRIGGER statement. If you are changing the trigger STATUS, you must specify ACTIVE or INACTIVE, whichever is appropriate. If you want to change the order in which the trigger is evaluated , the PRIORITY option must be used. The key specified has to be a non-negative floating point constant.

If the trigger which is being altered is a class trigger, the person must be an owner or must have been granted ALTER authorization.. To alter a user trigger , you must be an owner.

### Dropping Triggers in UniSQL

A trigger can be dropped from the database schema. If it is a user trigger(i. e the trigger event is COMMIT or ROLLBACK), it is only visible to the owner so c an be dropped only by the owner. A class trigger can be dropped by any user who has ALTER authorization.

>     Syntax

>             DROP TRIGGER trigger_name [;]

The trigger_name represents the trigger to be removed. Only one trigger c an be removed with the DROP TRIGGER statement.

##

## Defining an Audit Trail in UniSQL

An audit trail specifies the sequence of modifications to a single tab le, 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
    BEFORE DELETE ON PROJECTIONS
    EXECUTE
            INSERT INTO P_Audit VALUES (
                            OBJ.PROJECTION_ID,
                            OBJ.PROJECTION_NAME,
                            OBJ.PROJECTION_TYPE,
                            OBJ.SPHEROID_CODE,
                            OBJ.PROJECTION_UOM,
                            OBJ.ZONE_CODE,
                            DATE '01/01/1800');

    CREATE TRIGGER Update_PROJECTIONS
    AFTER UPDATE ON PROJECTIONS
    EXECUTE
            INSERT INTO P_Audit VALUES(
                            OBJ.PROJECTION_ID,
                            OBJ.PROJECTION_NAME,
                            OBJ.PROJECTION_TYPE,
                            OBJ.SPHEROID_CODE,
                            OBJ.PROJECTION_UOM,
                            OBJ.ZONE_CODE,
                            DATE '01/01/1800');

A dummy date is inserted first into the Audit table as in UniSQL a variable cannot be entered into a trigger. This date is later replaced by the current date through the trigger described below. Also an additional trigger is added on the Audited table.

    CREATE TRIGGER Insert_P_Audit
    AFTER INSERT ON P_Audit
    EXECUTE
            CALL set_date();

The function set_date replaces the dummy date entered into the Audit table by the present date.

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 t he SQL operations INSERT, DELETE, UPDATE.

##

## Reconstructing a Previous State in UniSQL

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.ec)
> Results(transres.txt)

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.^M

> Reconstruction Algorithm 1(reco1.ec)
> Reconstructed Table(reco1.txt)

##

## Permitting Insertions in UniSQL

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
    AFTER INSERT ON PROJECTIONS FOR EACH ROW
    EXECUTE
           INSERT INTO P_Audit VALUES(
                                    NEW.PROJECTION_ID,
                                    NEW.PROJECTION_NAME,
                                    NEW.PROJECTION_TYPE,
                                    NEW.SPHEROID_CODE,
                                    NEW.PROJECTION_UOM,
                                    NEW.ZONE_CODE,
                                    DATE '01/01/1800');

A similar action of inserting a dummy date, replacing it by the current date via a trigger, is carried out here.

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

> Reconstruction Algorithm 2(reco2.ec)
> Reconstructed Table(reco1.txt)

##

## Backlogs in UniSQL

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 perform ed. Such an audit trail is called a backlog. The triggers must be changed also to store the operation code for Delete, Update and Insert.

We reconstruct the Table as of August 1, 1997

> Reconstruction Algorithm 3(reco3.ec)
> Reconstructed Table(reco3.txt)

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 UniSQL

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 August 1, 1997

> Reconstruction Algorithm 4(reco4.ec)
> Reconstructed Table(reco4.txt)

##

## Running Examples in UniSQL

Here are the actual source code file (.ec) shown in the previous sections which can be executed with the help of the give Makefile.

Certain changes will have to be made to the Makefile depending upon the directory in which the libraries are located. The variables INCDIR and LIBDIR need to be altered to the path where the include and the lib directories are located respectively.

In order to execute the programs the following steps need to be followed :

> Copy the .ec files from the different sections to the proper directory
> Make the required changes in the Makefile
> Say make Compile to compile all the programs
> Say make Run to run all the programs and obtain the results i n the corresponding.txt file
> If u need to get the output of only specific files, you need to say make Run <name of file> (as one word) i.e make Runreco1 followed by the name of the program you want to execute (without the .ec).
> To delete the output files say make Clean.

> Makefile

#### HTML Credits

Jose Alvin G. Gendrano, Department of Computer Science, University of Arizona (jag@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)

#### UniSQL Specific Code Credits

Rachana R. Shah, Department of Computer Science, University of Arizona (rachana@cs.arizona.edu)
November 23, 1997 (Last Update)