By defining a trigger, you can execute an SQL statement automatically when an operation (update, insertion, or deletion) is performed on a certain table. To define a trigger, specify information such as the table that defines the trigger, the SQL statement that specifies the trigger operation timing (trigger-activating SQL statement), the SQL statement to be executed automatically (trigger SQL statement), and the conditions under which that operation is executed (trigger operation search conditions). When an SQL statement that satisfies the trigger operation search conditions is executed for the table that defines the trigger, the trigger SQL statement is executed automatically. Figure 4-8 shows an overview of triggers.

Figure 4-8 Trigger overview

Explanation:

When the UAP executes an SQL that activates the trigger, table A, which defines the trigger, calls the trigger. If the search conditions for trigger operation are satisfied, the trigger SQL statement (in this case, row insertion for table B and row update for table C) is automatically executed.

If you use a trigger, you do not need to describe the following types of operations in the UAP:

• When a certain table is updated, always update another table.
• When a certain table is updated, always update a certain column in the updated row. (Associate a column with another column.)

For example, suppose that when prices in a product management table are changed, the changes are accumulated in a product management history table. If a trigger is not used, the UAP that updates the product management table must also always update the product management history. If a trigger is used, the UAP that updates the product management table need not be concerned about updating the product management history table because the latter table can be manipulated automatically. By using triggers appropriately as in this example, you can reduce the work load involved in creating a UAP.

When a trigger is defined, the functions, procedures, and trigger SQL objects that use that table become invalid and must be re-created. When a resource (such as a table or index) being used by a trigger is defined, redefined, or deleted, the SQL objects of the trigger become invalid and must be re-created.

For details about triggers, see the HiRDB Version 8 Installation and Design Guide.