Download HyperSQL User Guide

Triggers
INSTEAD OF triggers are allowed only when the trigger is declared on a VIEW. With this type of trigger, the event
(SQL statement) itself is not executed, only the trigger.
BEFORE or AFTER triggers are executed just before or just after the execution of the event. For example, just before
a row is inserted into a table, the BEFORE trigger is activated, and just after the row is inserted, the AFTER trigger
is executed.
BEFORE triggers can modify the row that is being inserted or updated. AFTER triggers cannot modify rows. They
are usually used to perform additional operations, such as inserting rows into other tables.
A trigger declared as FOR EACH STATEMENT can only be an AFTER trigger.
References to Rows
If the old rows or new rows are referenced in the SQL statements in the trigger action, they must have names. The
REFERENCING clause is used to give names to the old and new rows. The clause, REFERENCING OLD | NEW
TABLE is used for statement level triggers. The clause, REFERENCING OLD | NEW ROW is used for row level
triggers. If the old rows or new rows are referenced in the SQL statements in the trigger action, they must have names.
In the SQL statements, the columns of the old or new rows are qualified with the specified names.
Trigger Condition
The WHEN clause can specify a condition for the columns of the row that is being changed. Using this clause you can
simply avoid unnecessary trigger activation for rows that do not need it.
For UPDATE trigger, you can specify a list of columns of the table. If a list of columns is specified, then if the UPDATE
statement does not change the columns with SET clauses, then the trigger is not activated at all.
Trigger Action in SQL
The trigger action specifies what the trigger does when it is activated. This is usually written as one or more SQL
statements.
When a row level trigger is activated, there is an OLD ROW, or a NEW ROW, or both. An INSERT statement supplies
a NEW ROW row to be inserted into a table. A DELETE statement supplies an OLD ROW be deleted. An UPDATE
statement supplies both OLD ROW and NEW ROW that represent the updated rows before and after the update. The
REFERENCING clause gives names to these rows, so that the rows can be referenced in the trigger action.
In the example below, a name is given to the NEW ROW and it is used both in the WHEN clause and in the trigger
action SQL to insert a row into a triglog table after each row insert into the testtrig table.
CREATE TRIGGER trig AFTER INSERT ON testtrig
REFERENCING NEW ROW AS newrow
FOR EACH ROW WHEN (newrow.id > 1)
INSERT INTO TRIGLOG VALUES (newrow.id, newrow.data, 'inserted')
In the example blow, the trigger code modifies the updated data if a condition is true. This type of trigger is useful
when the application does not perform the necessary checks and modifications to data. The statement block that starts
with BEGIN ATOMIC is similar to an SQL/PSM block and can contain all the SQL statements that are allowed in
an SQL/PSM block.
CREATE TRIGGER t BEFORE UPDATE ON customer
REFERENCING NEW AS newrow FOR EACH ROW
BEGIN ATOMIC
IF LENGTH(newrow.firstname) > 10 THEN
SET newrow.firstname = LOWER(newrow.firstname);
158