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.
The following definition SQLs are used to define triggers and to re-create and delete SQL objects.
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)
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.
CREATE TRIGGER bonus_trigger_1
BEFORE
INSERT
ON staff_table
REFERENCING NEW ROW AS X1
FOR EACH ROW
SET X1.bonus=CASE X1.position
WHEN 'A' THEN X1.salary*0.08
WHEN 'B' THEN X1.salary*0.1
ELSE 0 END
CREATE TRIGGER bonus_trigger_2
BEFORE
UPDATE OF position, salary
ON staff_table
REFERENCING NEW ROW AS X1
FOR EACH ROW
SET X1.bonus=CASE X1.position
WHEN 'A' THEN X1.salary*0.08
WHEN 'B' THEN X1.salary*0.1
ELSE 0 END
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
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'