Generic triggers for InterBase?
<< Convert your Firebird applications to Unicode | Database technology articles | InterBase, Firebird and Blobs -a technical overview >>
Generic triggers for InterBase?
By Jim Starkey - 20th November 2000; Source: https://www.ibphoenix.com
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 Deatz, 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 declared before or after insert, update, delete, or commit (more on this later). There is optionally a sequence number à 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:
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 metadata 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.
A few 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, because it 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 given 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 it completely avoids Borland's (sic) database event patent.
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 replace
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 no
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);
}
}
This paper was written by Jim Starkey and is copyright Jim Starkey and IBPhoenix Inc. You may republish it verbatim, including this notation. You may update, correct, or expand the material, provided that you include a notation that the original work was produced by Ms. Harrison and IBPhoenix Inc.
See also:
Trigger
Stored Procedure and Trigger Language
Writing stored procedures and triggers
Using procedures to create and drop triggers
back to top of page
<< Convert your Firebird applications to Unicode | Database technology articles | InterBase, Firebird and Blobs -a technical overview >>
