Chapter 9. Triggers

Fred Toussi

The HSQL Development Group

$Revision: 3042 $

Copyright 2010 Fred Toussi. Permission is granted to distribute this document without any alteration under the terms of the HSQLDB license. Additional permission is granted to the HSQL Development Group to distribute this document with or without alterations under the terms of the HSQLDB license.

$Date: 2009-07-14 17:55:19 +0100 (Tue, 14 Jul 2009) $

Table of Contents

Overview
Trigger Properties
Trigger Event
Granularity
Trigger Action Time
References to Rows
Trigger Condition
Trigger Action in SQL
Trigger Action in Java
Trigger Creation

Overview

Trigger functionality first appeared in SQL:1999. Triggers embody the live database concept, where changes in SQL data can be monitored and acted upon. This means each time a DELETE, UPDATE or INSERT is performed, additional actions are taken by the declared triggers. SQL Standard triggers are imperative while the relational aspects of SQL are declarative. Triggers allow performing an arbitrary transformation of data that is being updated or inserted, or to prevent insert, updated or deletes, or to perform additional operations.

Some bad examples of SQL triggers in effect enforce an “integrity constraint” which would better be expressed as a CHECK constraint. A trigger that causes an exception if the value inserted in a column is negative is such an example. A check constraint that declares CHECK VALUE >= 0 (declarative) is a better way of expressing an integrity constraint than a trigger that throws an exception if the same condition is false.

Usage constraints cannot always be expressed by SQL’s integrity constraint statements. Triggers can enforce these constraints. For example, it is may be possible to write a check constraint that prevents data from being added, or modified on weekends. But it is not possible to use a check constraint to prevent deletes. A trigger can be used to enforce the time when each operation is allowed.

A trigger can modify the values that are inserted into the database, instead of rejecting them. For example, a badly formatted string can be cleaned up by a trigger before INSERT.

Triggers can also perform additional data changes, for example inserting an additional row into a different table for data audits.

A trigger is declared to activate when an UPDATE, INSERT or DELETE action is performed on a table. These actions may be direct or indirect. Indirect actions may arise from CASCADE actions of FOREIGN KEY constraints, or from data change statements performed on a VIEW that is based on the table that in.

It is possible to declare multiple triggers on a single table. The triggers activate one by one according to the order in which they were defined.

A row level trigger allows access to the deleted or inserted rows. For UPDATE actions there is both an old and new version of each row. A trigger can be specified to activate before or after the action has been performed. Triggers that are performed after the action cannot modify the rows that have been modified. These triggers can perform other actions, such as inserting rows into other tables. Triggers that are performed before the action can modify the inserted or updated rows but not the deleted rows.

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, 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. With the use of INSTEAD OF triggers a read-only view can effectively become updatable or insertable-into.

Trigger Properties

A trigger is declared on a specific table or view. Various trigger properties determine when the trigger is executed and how.

Trigger Event

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.

Granularity

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.

Trigger Action Time

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.

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 supplied 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.

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

Trigger Action in Java

A trigger action can be written as a Java class that implements the org.hsqldb.Trigger interface. This interface has a single 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 name of the trigger (as name argument), the name of the table (as table argument), the OLD ROW (as row1 argument) and the NEW ROW (as row2 argument). The row1 argument is null for row level INSERT triggers. The row2 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 triggerType argument is one of the constants in the org.hsqldb.Trigger interface which indicate the type of trigger, for example, INSERT_BEFORE_ROW or UPDATE_AFTER_ROW.

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 triggerType, String name, String table, Object row1[], Object row2[])

The Java method for a synchronous trigger (see below) can modify the values in row2 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 has two forms, synchronous, or asynchronous (immediate or queued). By default, or when QUEUE 0 is specified, the action is performed immediately by calling the Java method. This is similar to SQL trigger actions. 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 asynchronous notifications to be sent by the trigger event, 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.

Java triggers can modify the row data. They should not be used to modify the database, e.g. insert new rows, etc.

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 /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

Trigger Creation

CREATE TRIGGER

trigger definition

<trigger definition> ::= CREATE TRIGGER <trigger name> <trigger action time> <trigger event> ON <table name> [BEFORE <other trigger name>] [ REFERENCING <transition table or variable list> ] <triggered action>

<trigger action time> ::= BEFORE | AFTER | INSTEAD OF

<trigger event> ::= INSERT | DELETE | UPDATE [ OF <trigger column list> ]

<trigger column list> ::= <column name list>

<triggered action> ::= [ FOR EACH { ROW | STATEMENT } ] [ <triggered when clause> ] <triggered SQL statement>

<triggered when clause> ::= WHEN <left paren> <search condition> <right paren>

<triggered SQL statement> ::= <SQL procedure statement> | BEGIN ATOMIC { <SQL procedure statement> <semicolon> }... END | [QUEUE <integer literal>] [NOWAIT] CALL <HSQLDB trigger class FQN>

<transition table or variable list> ::= <transition table or variable>...

<transition table or variable> ::= OLD [ ROW ] [ AS ] <old transition variable name> | NEW [ ROW ] [ AS ] <new transition variable name> | OLD TABLE [ AS ] <old transition table name> | NEW TABLE [ AS ] <new transition table name>

<old transition table name> ::= <transition table name>

<new transition table name> ::= <transition table name>

<transition table name> ::= <identifier>

<old transition variable name> ::= <correlation name>

<new transition variable name> ::= <correlation name>

Trigger definition is a relatively complex statement. The combination of <trigger action time> and <trigger event> determines the type of the trigger. Examples include BEFORE DELETE, AFTER UPDATE, INSTEAD OF INSERT. If the optional [ OF <trigger column list> ] is specified for an UPDATE trigger, then the trigger is activated only if one of the columns that is in the <trigger column list> is specified in the UPDATE statement that activates the trigger.

If a trigger is FOR EACH ROW, which is the default option, then the trigger is activated for each row of the table that is affected by the execution of an SQL statement. Otherwise, it is activated once only per statement execution. In the first case, there is a before and after state for each row. For UPDATE triggers, both before and after states exist, representing the row before the update, and after the update. For DELETE, triggers, there is only a before state. For INSERT triggers, there is only an after state. If a trigger is FOR EACH STATEMENT, then a transient table is created containing all the rows for the before state and another transient table is created for the after state.

The [ REFERENCING <transition table or variable> ] is used to give a name to the before and after data row or table. This name can be referenced in the <SQL procedure statement> to access the data.

The optional <triggered when clause> is a search condition, similar to the search condition of a DELETE or UPDATE statement. If the search condition is not TRUE for a row, then the trigger is not activated for that row.

The <SQL procedure statement> is limited to INSERT, DELETE, UPDATE and MERGE statements.

The <HSQLDB trigger class FQN> is a delimited identifer that contains the fully qualified name of a Java class that implements the org.hsqldb.Trigger interface.

Early releases of HyperSQL version 2.0 do not allow the use of OLD TABLE or NEW TABLE in statement level triggers.

TRIGGERED SQL STATEMENT

triggered SQL statement

The <triggered SQL statement> has three forms.

The first form is a single SQL procedure statement. This statement can reference the OLD ROW and NEW ROW variables. For example, it can reference these variables and insert a row into a separate table.

The second form is enclosed in a BEGIN ... END block and can include one or more SQL procedure statements. In BEFORE triggers, you can include SET statements to modify the inserted or updated rows. In AFTER triggers, you can include INSERT, DELETE and UPDATE statements to change the data in other database tables. SELECT and CALL statements are allowed in BEFORE and AFTER triggers. CALL statements in BEFORE triggers should not modify data.

The third form specifies a call to a Java method.

An example of a trigger with a block is given below. The block can include elements discussed the SQL-Invoked Routines chapter, including local variables, loops and conditionals. You can also raise an exception in such blocks in order to terminate the execution of the SQL statement that caused the trigger to execute.

create trigger trig after insert on testtrig 
   referencing new row as newrow
   for each row when (newrow.id > 1)
   begin atomic
   insert into triglog values (newrow.id, newrow.data, 'inserted');
   /* more statements can be included */
   end

TRIGGER EXECUTION ORDER

trigger execution order

<trigger execution order> ::= BEFORE <other trigger name>

HyperSQL extends the SQL Standard to allow the order of execution of a trigger to be specified by using [BEFORE <other trigger name>] in the definition. The newly defined trigger will be executed before the specified other trigger. If this clause is not used, the new trigger is executed after all the previously defined triggers of the same scope (BEFORE, AFTER, EACH ROW, EACH STATEMENT).

DROP TRIGGER

drop trigger statement

<drop trigger statement> ::= DROP TRIGGER <trigger name>

Destroy a trigger.


$Revision: 3601 $