FairCom Java & .NET Stored Procedures

Trigger Basics

Triggers are a special type of stored procedure used to maintain database integrity.

Like stored procedures, triggers also contain Java code (embedded in a CREATE TRIGGER statement) and use FairCom DB SQL Java classes. However, triggers are automatically invoked (“fired”) by certain FairCom DB SQL operations (an INSERT, UPDATE, or DELETE operation) on the trigger’s target table.

This chapter provides a general description of triggers and discusses in detail where trigger procedures differ from stored procedures. Unless otherwise noted, much of the material in Using Stored Procedures also applies to triggers.

Triggers vs. Stored Procedures vs. Constraints

Triggers are identical to stored procedures in many respects. There are three main differences:

• Triggers are automatic. When the trigger event (an INSERT, UPDATE, or DELETE statement) affects the specified table (and, optionally in UPDATE operations, the specified columns), the Java code contained in the body of the trigger executes. Stored procedures, on the other hand, must be explicitly invoked by an application or another procedure.
• Triggers cannot have output parameters or a result set. Since triggers are automatic, there is no calling application to process any output they may generate. The practical consequence of this is that the Java code in the trigger body cannot invoke methods of the DhSQLResultSet class.
• Triggers have limited input parameters. The only possible input parameters for triggers are values of columns in the rows affected by the trigger event. If the trigger includes the REFERENCING clause, FairCom DB SQL passes the values (either as they existed in the database or are specified in the INSERT or UPDATE statement) of each row affected. The Java code in the trigger body can use those values in its processing by invoking the getValue() method of the OLDROW and NEWROW objects (see “OLDROW and NEWROW Objects: Passing Values to Triggers”).

The automatic nature of triggers make them well-suited for enforcing referential integrity. In this regard, they are like constraints, since both triggers and constraints can help insure that a value stored in the foreign key of a table must either be null or be equal to some value in the matching unique or primary key of another table.

However, triggers differ from constraints in the following ways:

• Triggers are active, while constraints are passive. While constraints prevent updates that violate referential integrity, triggers perform explicit actions in addition to the update operation.
• Triggers can do much more than enforce referential integrity. Because they are passive, constraints are limited to preventing updates in a narrow set of conditions. Triggers are more flexible. The following section outlines some common uses for triggers.

Typical Uses for Triggers

Typical uses for triggers include combinations of the following:

Cascading deletes

A delete operation on one table causes additional rows to be deleted from other tables that are related to the first table by key values. This is an active way of enforcing referential integrity that a table constraint enforces passively.

Cascading updates

An update operation on one table causes additional rows to be updated in other tables that are related to the first table by key values. These updates are commonly limited to the key fields themselves. This is an active way of enforcing referential integrity that a table constraint enforces passively.

Summation updates

An update operation in one table causes a value in a row of another table to be updated by being increased or decreased.

Automatic archiving

A delete operation on one table creates an identical row in an archive table that is not otherwise used by the database.

Restrictions on Creating Triggers

Triggers can be created only on user tables. An attempt to create triggers on system tables (systrigger, systables, sysprocedures etc.) results in an error: (error (20158) : Can’t create triggers on system tables).