| Sample code for use of triggers in hsqldb.
SQL to invoke is:
CREATE TRIGGER triggerSample BEFORE|AFTER INSERT|UPDATE|DELETE
ON myTable [FOR EACH ROW] [QUEUE n] [NOWAIT] CALL "myPackage.trigClass"
This will create a thread that will wait for its firing event to occur;
when this happens, the trigger's thread runs the 'trigClass.fire'
Note that this is still in the same Java Virtual Machine as the
database, so make sure the fired method does not hang.
There is a queue of events waiting to be run by each trigger thread.
This is particularly useful for 'FOR EACH ROW' triggers, when a large
number of trigger events occur in rapid succession, without the trigger
thread getting a chance to run. If the queue becomes full, subsequent
additions to it cause the database engine to suspend awaiting space
in the queue. Take great care to avoid this situation if the trigger
action involves accessing the database, as deadlock will occur.
This can be avoided either by ensuring the QUEUE parameter makes a large
enough queue, or by using the NOWAIT parameter, which causes a new
trigger event to overwrite the most recent event in the queue.
The default queue size is 1024.
Ensure that "myPackage.trigClass" is present in the classpath which
you use to start hsql.
If the method wants to access the database, it must establish
a JDBC connection.
When the 'fire' method is called, it is passed the following arguments:
fire (int type, String trigName, String tabName, Object oldRow[],
Object[] newRow)
where 'type' is one of the values enumerated in the Trigger interface and
the 'oldRow'/'newRow' pair represents the rows acted on. The first
length - 1 array slots contain column values and the final slot contains
either null or the value of the internally assigned row identity, if
the concerned table has no primary key. The final slot must _never_ be
modified.
The mapping of row classes to database types is specified in
/doc/hsqlSyntax.html#Datatypes.
To be done:
- Implement the "jdbc:default:connection: URL to provide transparent
and portable access to internal connections for use in triggers and
stored procedures.
- Implement declaritive column to trigger method argument
mapping, conditional execution (WHEN clause), etc.
- Investigate and refine synchronous and asynchronous trigger models.
Because certain combinations of trigger create parameters cause the
individual triggered actions of a multirow update to run in different
threads, it is possible for an 'after' trigger to run before its
corresponding 'before' trigger; the acceptability and implications
of this needs to be investigated, documented and the behaviour of
the engine fully specified.
- Investigate and implement the SQL 200n specified execution stack under
arbitrary triggered action and SQL-invoked routine call graphs.
author:
Peter Hudson
author:
boucherb@users
version:
1.7.2
since:
1.7.0 | 