InstantDB: Database Triggers

Database Triggers

WARNING - InstantDB's database triggers are a non-standard feature. Using database triggers will make your application less portable.

InstantDB's database triggers provide a simple, yet powerful facility for reacting to database events. By defining triggers on a table, your application can be notified of any changes that take place to that table. InstantDB can notify you whenever a row is added, deleted, or updated. You can even be notified when a transaction commits or rolls back - thus enabling you to bring external data structures into sync with database values.

You can define as many triggers on a table as you like. You can define triggers on as many tables as you like. There are no in built limits. You can even nest triggers - with database updates inside one trigger causing another trigger to activate.

Whilst a trigger is active, you can query any table in the database. In addition, you can modify any tables other than the trigger table. All activity whilst in the trigger is fully transactional. If a transaction commits or rolls back, then every database change made by a trigger also commits or rolls back.

Referential integrity checks.
Range or value checks.
Mirroring of data into de-normalised tables.
Data replication to remote databases.
Real time updates to multiple clients.

The possibilities are virtually endless.

If you restrict all your activity in a trigger to database operations, then there is no need to implement onCommit() or onRollback() methods - the database will handle the commit and rollback of all your trigger activity.

If you choose to update data structures, such as queues or lists, using data from the database, then you may wish to implement onCommit() and onRollback() to ensure that your data structures remain consistent with the database contents. An example where you would have to do this is where you are building a queue of database updates which is being read by a seperate background thread.

You can even use RMI or CORBA to transmit database changes to other Java™ virtual machines, although this is probably best done using the queue and background thread technique.

You create a trigger object by extending db.idbTrigger. You can then override onAdd, onUpdate, onDelete, onCommit and onRollback as desired. You should place your trigger code in these methods.

In order to define your trigger, it is then simply a method of creating one of your new trigger objects. When you create the trigger object, you specify the table and events that it should be triggered on:

class myTrigger extends idbTrigger {
public static void main (String args[]) {
...
myTrigger trig1 = new myTrigger ("table1",
idbTrigger.TRIGGER_ON_ADD+
idbTrigger.TRIGGER_ON_DELETE);
...
} // method main
myTrigger (String table, int events)
throws SQLException {
super (table, events);
} // method TrigExpl
public void onAdd (Vector row, long transID)
throws SQLException {
...
} // method onAdd
public void onDelete (Vector row, long transID)
throws SQLException {
...
} // method onDelete
} // class myTrigger

A full example using triggers can be found in TrigExpl.java. Running the TrigExpl class will create a small database. Triggers are defined on the main table in this database, so that whenever a row is added or deleted, a secondary table of statistics gets updated. The secondary table also has a trigger defined for when it gets updated - showing that triggers can be nested. The secondary table also triggers on commits and rollbacks.