Chapter 10. Triggers

A trigger that is declared as BEFORE DELETE cannot modify the deleted row. In other words, it cannot decide to delete a different row by changing the column values of the row. A trigger that is declared as BEFORE INSERT or BEFORE UPDATE can modify the values that are inserted into the database. For example, a badly formatted string can be cleaned up by a trigger before INSERT or UPDATE.

BEFORE triggers cannot modify the other tables of the database. All BEFORE triggers can veto the action by throwing an exception.

Because BEFORE triggers can modify the inserted or updated rows, all constraint checks are performed after the execution of the BEFORE triggers. The checks include NOT NULL constraints, length of strings, CHECK constraints, and FOREIGN key constraints.

AFTER triggers can perform additional data changes, for example inserting an additional row into a different table for data audits or logs. These triggers cannot modify the rows that have been modified by the INSERT or UPDATE action.

A trigger that is declared on a VIEW, is an INSTEAD OF trigger. This term means when an INSERT, UPDATE or DELETE statement is executed with the view as the target, the trigger action is all that is performed, and no further data change takes place on the view. The trigger action can include all the statements that are necessary to change the data in the tables that underlie the view, or even other tables, such as audit tables. With the use of INSTEAD OF triggers a read-only view can effectively become updatable or insertable-into.

An example of INSTEAD OF TRIGGERS is one that performs an INSERT, UPDATE or DELETE on multiple tables that are used in the view.

The trigger event specifies the type of SQL statement that causes the trigger to execute. Each trigger is specified to execute when an INSERT, DELETE or UPDATE takes place.

The event can be filtered by two separate means. For all triggers, the WHEN clause can specify a condition against the rows that are the subject of the trigger, together with the data in the database. For example, a trigger can activate when the size of a table becomes larger than a certain amount. Or it can activate when the values in the rows being modified satisfy certain conditions.

An UPDATE trigger can be declared to execute only when certain columns are the subject of an update statement. For example, a trigger declared as AFTER UPDATE OF (datecolumn) will activate only when the UPDATE statement that is executed includes the column, datecolumn, as one of the columns specified in its SET statements.

A statement level trigger is performed once for the executed SQL statement and is declared as FOR EACH STATEMENT.

A row level trigger is performed once for each row that is modified during the execution of an SQL statement and is declared as FOR EACH ROW. Note that an SQL statement can INSERT, UPDATE or DELETE zero or more rows.

If a statement does not apply to any row, then the trigger is not executed.

If FOR EACH ROW or FOR EACH STATEMENT is not specified, then the default is FOR EACH STATEMENT.

The granularity dictates whether the REFERENCING clause can specify OLD ROW, NEW ROW, or OLD TABLE, NEW TABLE.

A trigger declared as FOR EACH STATEMENT can only be an AFTER trigger. These triggers are useful for logging the event that was triggered.

A trigger is executed BEFORE, AFTER or INSTEAD OF the trigger event.

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.

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.

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.

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 to 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);
     END IF;
   END

A trigger action can be written as a Java class that implements the org.hsqldb.trigger.Trigger interface. This interface has a fire method which is called when the trigger is activated, either before or after the event. When the method is called by the engine, it supplies the type of trigger as an int value defined by the interface(as type argument), the name of the trigger (as triggerName argument), the name of the table (as tableName argument), the list of names of the columns of the table (as columnNames argument), the OLD ROW (as oldRow argument) and the NEW ROW (as newRow argument). The oldRow argument is null for row level INSERT triggers. The newRow argument is null for row level DELETE triggers. For table level triggers, both arguments are null (that is, there is no access to the data). The type argument is one of the constants in the org.hsqldb.Trigger interface which indicate the type of trigger, for example, Trigger.INSERT_BEFORE_ROW or Trigger.UPDATE_AFTER_ROW. For compatibility with older triggers, there is also another fire method signature without the columnNames argument.

The Java class for the trigger can be reused for several triggers on different tables. The method code can distinguish between the different tables and triggers using the supplied arguments and take appropriate action.

 fire (int type, String triggerName, String tableName, String[] columnNames, Object oldRow[], Object newRow[])

The Java method for a synchronous trigger (see below) can modify the values in newRow in a BEFORE trigger. Such modifications are reflected in the row that is being inserted or updated. Any other modifications are ignored by the engine.

A Java trigger that uses an instance of org.hsqldb.trigger.Trigger has two forms, synchronous, or asynchronous (immediate or queued). By default, or when QUEUE 0 is specified, the trigger is synchronous and the action is performed immediately by calling the Java method. This is similar to SQL trigger actions.

Asynchronous triggers must not modify the row data and they must not execute any SQL statements. When QUEUE n is specified with n larger than 0, the engine uses a separate thread to execute the Java method, using a queue with the size n. For certain applications, such as real-time systems this allows the trigger event to send asynchronous notifications to external systems, without introducing delays in the engine. With asynchronous triggers, an extra parameter, NOWAIT can be used in trigger definition. This overcomes the queue full condition. In this mode, old calls that are still in the queue are discarded one by one and replaced with new calls.

Synchronous Java row level triggers that are declared with BEFORE trigger action time can modify the row data. Triggers with AFTER trigger action time can modify the database, e.g. insert new rows. If the trigger needs to access the database, the same method as in Java Language Routines SQL/JRT can be used. The Java code should connect to the URL "jdbc:default:connection" and use this connection to access the database.

For sample trigger classes and test code see, org.hsqldb.sample.TriggerSample, org.hsqldb.test.TestTriggers, org.hsqldb.test.TriggerClass and the associated text script TestTriggers.txt in the /testrun/hsqldb/ directory. In the example below, the trigger is activated only if the update statement includes SET clauses that modify any of the specified columns (c1, c2, c3). Furthermore, the trigger is not activated if the c2 column in the updated row is null.

 CREATE TRIGGER TRIGBUR BEFORE UPDATE OF c1, c2, c3 ON testtrig 
   referencing NEW ROW AS newrow
   FOR EACH ROW WHEN (newrow.c2 IS NOT NULL)
   CALL "org.hsqldb.test.TriggerClass"

Java functions can be called from an SQL trigger. So it is possible to define the Java function to perform any external communication that are necessary for the trigger, and use SQL code for checks and alterations to data.

 CREATE TRIGGER t BEFORE UPDATE ON customer
   REFERENCING NEW AS newrow FOR EACH ROW
   BEGIN ATOMIC
     IF LENGTH(newrow.firstname) > 10 THEN
       CALL my_java_function(newrow.firstname, newrow.lastname);
     END IF;
   END