12.5.2 Defining a trigger

Organization of this subsection
(1) Preparation for definition
(2) Definition method
(3) Trigger definition example

(1) Preparation for definition

When a trigger is defined, the SQL objects of the trigger action are created automatically on the basis of the triggered SQL statement and are stored in a data dictionary LOB RDAREA. Therefore, when triggers are to be defined, sufficient space must be available in the data dictionary LOB RDAREA. For details about estimating the size of the data dictionary LOB RDAREA, see 16.5 Determining the size of a data dictionary LOB RDAREA.

In order to execute a trigger event SQL statement, you must take into account the triggered SQL objects when you specify the SQL object buffer length. For details about estimating the buffer length used by SQL objects, see the manual HiRDB Version 9 System Definition.

(2) Definition method

The following definition SQLs are used to define triggers and to re-create and delete SQL objects.

(3) Trigger definition example

(a) Example of using a trigger

The following is an example of defining a trigger for the product management table so that if there is an increase in the value of the Price column that exceeds 10,000 yen, the pre-update and post-update prices will be inserted into the product management history table.

CREATE TRIGGER TR1    ...Trigger name
      AFTER          ...Timing of the trigger action
      UPDATE OF price    ...Triggering event
      ON product_management_table...Table the trigger is defined for
      REFERENCING OLD ROW AS X1    ...Pre-update row alias
                  NEW ROW AS Y1    ...Post-update row alias
      FOR EACH ROW   ...Whether for the entire statement or for each row
      WHEN(Y1.price - X1.price > 10000)
                                ...Trigger action search condition
      INSERT INTO product_management_history_table VALUES   ...Triggered SQL statement
                      (X1.item_no, X1.price, Y1.price)

[Figure]

(b) Example of a trigger action that uses an SQL control statement (assignment statement)

An assignment statement is an SQL statement that assigns a specified value to a specified column. A trigger can use an assignment statement before executing its action on a table. When an assignment statement is used in a trigger action, a relationship can be established between columns.

The following example shows two trigger definitions and their actions of updating the value of the Bonus column in response to updates of the value in the Position column of the staff table.

Explanation
The INSERT statement acts as a triggering event, bonus_trigger_1 is executed, and then a row is added. The INSERT statement causes the data in Bonus to be set to 0, and then the result of the assignment is stored.
Next, the UPDATE statement acts as a triggering event, bonus_trigger_2 is executed, and the data in Bonus is updated to 0.
(c) Example where the triggering action uses SQL control statements (a compound statement)

A compound statement is an SQL statement that executes multiple SQL statements within a single statement. An update to a table can act as a triggering event, such that the triggered SQL statement is a compound statement that enables the single trigger to update multiple tables.

The following example defines a trigger that enables updates to the master inventory table to be reflected in the Glasgow inventory table and the Edinburgh inventory table. If a compound statement were not used, it would be necessary to define two separate triggers.

CREATE TRIGGER local_stock_table_update_trigger
      AFTER
      UPDATE OF stock_count
      ON inventory_master
      REFERENCING NEW ROW post_update
                  OLD ROW pre_update
      BEGIN
        UPDATE Glasgow_stock SET stock_count=post_update.stock_count
               WHERE product_code=pre_update.product_code;
        UPDATE Edinburgh_stock SET stock_count=post_update.stock_count
               WHERE product_code=pre_update.product_code;
      END

(d) Example where the trigger action contains an SQL diagnostic statement (SIGNAL statement)

The SIGNAL statement causes an error to occur. If, before the action on a table, a trigger action is executed that specifies the SIGNAL statement, then if the action is invalid the SIGNAL statement will execute to prevent the action.

The following example defines a trigger where there is an attempt to update information for someone else, and before the staff information table is updated the SIGNAL statement issues an error and the update is prevented.

CREATE TRIGGER update_prevention_trigger
      BEFORE
      UPDATE
      ON staff_information
      REFERENCING OLD ROW AS X1
      WHEN(X1.employee_name<>USER) SIGNAL SQLSTATE '99001'