Before they changed the water we had been discussing alternative
architectures for triggers. I, as usual, was plugging Java as
a trigger language. Other folks were looking for ways to avoid
defining vast number of highly similar triggers for various tables.

Partly due to the urging of Greg Dietz, I finally got around to
designing triggers for Netfrastructure. Since some of the ideas
that went into the design came from the architecture list, I thought
it only fair that I report back the architectural design. Whether
or not it is practical or even desirable for Interbase (or IBPhoenix
or FireBird or IBAlbuquerque) I will leave to the folks doing the
work.

Rather than present a formal description, I will offer a fairly
rich example of a trigger to log all changes to selected tables
to a change_log. Most triggers will be vastly simpler, but this
case is a) useful, b) pushes the envelope.

The trigger is attached to a table by the follow command:

create trigger <triggerName> for <tableName> after insert update
using 'netfrastructure.util.ChangeLog.trigger'

A single trigger can be declare before or after insert, update, delete,
or commit (more on this later). There is optionally a sequence number
a la Interbase. The thing in single quotes is a name of a Java
static method contain the trigger semantics. The calling prototype
is:

public static void trigger (Connection connection,
Record oldVersion,
Record newVersion,
int operation)

The trigger is defined as a static method in a Java class rather than
as a method in an interface. I did this so a single class can contain
a number of triggers. It's not the standard way of defining Java stuff,
but defining things to be called by external subsystems is not a Java
idea, either (though it is supported by a civilized invocation
protocol).

Because a trigger can be called for various operation, it is necessary
to tell it why it is being called, hence the "operation" parameter.
The operation codes are defined as masks to make writing a smart
trigger easier.

Because a trigger is a static method, it is not necessary to create
an object instance prior to calling it, which significantly reduces
the invocation cost. If an object context is required, as in the
sample, the static method can create an object instance at go at
it.

The interface used to pass record instances to the trigger is as
follows (sorry about the length and what your mail program might
do to the tabs):

public interface Record
{
public static final int PreInsert = 1;
public static final int PostInsert = 2;
public static final int PreUpdate = 4;
public static final int PostUpdate = 8;
public static final int PreDelete = 16;
public static final int PostDelete = 32;
public static final int PreCommit = 64;
public static final int PostCommit = 128;

public String getSchemaName();
public String getTableName();

public int getColumnId (String columnName);
public int nextColumnId (int columnId);
public int nextPrimaryKeyColumn (int columnId);
public boolean isChanged (int columnId, Record record);
public boolean wasNull ();

public String getColumnName (int columnId);
public int getColumnType (int columnId);
public int getPrecision (int columnId);
public int getScale (int columnId);

public <TYPE> get<TYPE>(String columnName);
public <TYPE> get<TYPE>(int columnId);
public void setString(String columnName, <TYPE> value); public void
setString(int columnId, <TYPE> value);
}

(Everything actual "throws SQLException".)

A trigger that knows what it's doing (ha ha) can use the "columnName"
forms of the various set/get functions. A completely generic trigger
can use the meta data calls to find out what's up. The two inquiry
functions, nextColumnId and nextPrimaryKeyColumn, take the previous
id, initially -1, and returns the next id or -1. Simple and very
cheap to pass across the Java/native interface. A RecordMetaData
object would have been possible to parallel JDBC meta data, but getting
a result set to process a trigger is too grotesque to be considered.

The "isChanged" method is solely to support the sample trigger, and
is more or less essential for performance. In all likelihood, it
will get heavy use.

The full trigger definition for ChangeLog is attached (Mr. Nordel,
prepare yourself for what you will consider ughly paragraphing).

A new comments on commit triggers. A pre-commit trigger can be
defined (lazy in the parser) but is essentially worthless since
a) the trigger cannot abort or influence the operation, and
b) the record is not yet visible to other transactions. The
post-commit trigger is likewise barred from aborting or changing
any data, be is executed after the transaction has been completed.
If the trigger wants to tell somebody outside the database that
something interesting has occurred, this is the place to do it.
A post commit trigger isn't give the verb type directly, but can
readily deduce it (null beforeRecord -> insert, null afterRecord ->
delete, otherwise update).

If case you missed it, the after commit trigger is the way to
implement your favorite replacement for the original Event Alerter
mechanism and is completely avoids Borland's (sic) database event
patent (tough noggies, Dale).
----------

package netfrastructure.sql;
import java.sql.*;

//
//
// Trigger method prototype:
//
// public static void trigger (Connection connection, Record oldVersion, Record newVersion, int type)
//
//


//
//
// Record
//
//
public interface Record
{
public static final int PreInsert = 1;
public static final int PostInsert = 2;
public static final int PreUpdate = 4;
public static final int PostUpdate = 8;
public static final int PreDelete = 16;
public static final int PostDelete = 32;

public String getSchemaName() throws SQLException;
public String getTableName() throws SQLException;

public int getColumnId (String columnName) throws SQLException;
public int nextColumnId (int columnId) throws SQLException;
public int nextPrimaryKeyColumn (int columnId) throws SQLException;
public boolean isChanged (int columnId, Record record) throws SQLException;
public boolean wasNull () throws SQLException;

public String getColumnName (int columnId) throws SQLException;
public int getColumnType (int columnId) throws SQLException;
public int getPrecision (int columnId) throws SQLException;
public int getScale (int columnId) throws SQLException;

public String getString(String columnName) throws SQLException;
public boolean getBoolean(String columnName) throws SQLException;
public byte getByte(String columnName) throws SQLException;
public short getShort(String columnName) throws SQLException;
public int getInt(String columnName) throws SQLException;
public long getLong(String columnName) throws SQLException;
public float getFloat(String columnName) throws SQLException;
public double getDouble(String columnName) throws SQLException;
public java.sql.Timestamp getTimestamp(String columnName) throws SQLException;
public java.sql.Date getDate(String columnName) throws SQLException;

public void setString(String columnName, String value) throws SQLException;
public void setBoolean(String columnName, boolean value) throws SQLException;
public void setByte(String columnName, byte value) throws SQLException;
public void setShort(String columnName, short value) throws SQLException;
public void setInt(String columnName, int value) throws SQLException;
public void setLong(String columnName, long value) throws SQLException;
public void setFloat(String columnName, float value) throws SQLException;
public void setDouble(String columnName, double value) throws SQLException;
public void setTimestamp(String columnName, java.sql.Timestamp value) throws SQLException;
public void setDate(String columnName, java.sql.Date value) throws SQLException;

public String getString(int columnId) throws SQLException;
public boolean getBoolean(int columnId) throws SQLException;
public byte getByte(int columnId) throws SQLException;
public short getShort(int columnId) throws SQLException;
public int getInt(int columnId) throws SQLException;
public long getLong(int columnId) throws SQLException;
public float getFloat(int columnId) throws SQLException;
public double getDouble(int columnId) throws SQLException;
public java.sql.Timestamp getTimestamp(int columnId) throws SQLException;
public java.sql.Date getDate(int columnId) throws SQLException;

public void setString(int columnId, String value) throws SQLException;
public void setBoolean(int columnId, boolean value) throws SQLException;
public void setByte(int columnId, byte value) throws SQLException;
public void setShort(int columnId, short value) throws SQLException;
public void setInt(int columnId, int value) throws SQLException;
public void setLong(int columnId, long value) throws SQLException;
public void setFloat(int columnId, float value) throws SQLException;
public void setDouble(int columnId, double value) throws SQLException;
public void setTimestamp(int columnId, java.sql.Timestamp value) throws SQLException;
public void setDate(int columnId, java.sql.Date value) throws SQLException;

}


----------

package netfrastructure.util;
import java.sql.*;
import netfrastructure.sql.*;

//
//
// ChangeLog
//
//


/***

Assumes the following tables in the default namespace

upgrade table sessions (
sessionId bigint primary key,
starttime timestamp,
user varchar (32));

upgrade table change_log (
sessionId bigint references sessions,
verbNumber int,
when timestamp,
schemaName varchar (128),
tableName varchar (128),
primaryKey varchar (32),
columnName varchar (128),
value clob,
primary key (sessionId, verbNumber, columnName))

create sequence sessionId
create index log_index on change_log (schemaName, tableName)

create trigger <triggerName> for <tableName> after insert update
using 'netfrastructure.util.ChangeLog.trigger'

****/

public class ChangeLog
{
protected static int verbCount;

protected NfsConnection connection;
protected Record oldRecord;
protected Record newRecord;
protected PreparedStatement logInsert;
protected String schema;
protected String table;
protected int verbNumber;

// The actual trigger. In a subclass, this will normally be replaced

public static void trigger (NfsConnection connection, Record oldVersion, Record newVersion, int type)
throws SQLException
{
new ChangeLog().logChanges (connection, oldVersion, newVersion, type);
}

// Method to do actual work. Depending on trigger type, find changes

protected void logChanges (NfsConnection cnct, Record oldVersion, Record newVersion, int type)
throws SQLException
{
connection = cnct;
oldRecord = oldVersion;
newRecord = newVersion;
table = newRecord.getTableName();
schema = newRecord.getSchemaName();
verbNumber = verbCount++;

// If this is a PostInsert, log all fields; if PostUpdate, log change fields

if (type == Record.PostInsert)
for (int columnId = -1; (columnId = newVersion.nextColumnId (columnId)) >= 0;)
logChange (columnId);
else if (type == Record.PostUpdate)
for (int columnId = -1; (columnId = newVersion.nextColumnId (columnId)) >= 0;)
if (newVersion.isChanged (columnId, oldVersion))
logChange (columnId);

// If we compiled an insert, we're done with it now

if (logInsert != null)
logInsert.close();
}

// Something changed. Find out what and log it.

protected void logChange (int columnId) throws SQLException
{
String column = newRecord.getColumnName (columnId);
String value = newRecord.getString (columnId);
String session = connection.getAttribute ("sessionId", null);

// If we don't have a session, create a session record

if (session == null)
{
session = createSession();
connection.setAttribute ("sessionId", session);
}

// If this is the first time through, compile an insert

if (logInsert == null)
logInsert = connection.prepareStatement (
"insert into change_log " +
"(when,sessionId,verbNumber,schemaName,tableName,primaryKey,columnName,value) " +
"values ('now',?,?,?,?,?,?,?)");

// Pick up primary key, concatenating segments with "|"

int key = newRecord.nextPrimaryKeyColumn (-1);
String primaryKey = newRecord.getString (key);

while ((key = newRecord.nextPrimaryKeyColumn (key)) >= 0)
primaryKey += '|' + newRecord.getString (key);

// Insert record into change log

int n = 1;
logInsert.setString (n++, session);
logInsert.setInt (n++, verbNumber);
logInsert.setString (n++, schema);
logInsert.setString (n++, table);
logInsert.setString (n++, primaryKey);
logInsert.setString (n++, column);
logInsert.setString (n++, value);
logInsert.executeUpdate();
}

// Create a new session

protected String createSession () throws SQLException
{
// Pick up effective user. No user, no update.

String user = connection.getAttribute ("user", null);

if (user == null)
throw new SQLException ("user not set, cannot perform update");

// Pick up an session id from a sequence

long id = connection.getSequenceValue ("sessionId");
PreparedStatement statement = connection.prepareStatement (
"insert into sessions (starttime,sessionId,user) values ('now',?,?)");
int n = 1;
statement.setLong (n++, id);
statement.setString (n++, user);
statement.executeUpdate();
statement.close();

return String.valueOf (id);
}
}


----------



Jim Starkey

[Non-text portions of this message have been removed]