At 10:56 AM 6/11/03 -0700, Chris Meelhuysen wrote:

>Would it be possible and is it helpful to have Database level triggers. As
>before any insert no matter what table or before any update or any delete.
>I just see this useful to reduce triggers that are used in multiple tables.
>Such as I want to set up a database after update trigger so that if a user
>does an update to no matter which table I can put an entry in a log that
>they did an update, rather then having to put that trigger on every after
>update for every table.
>
>Also, if such a procedure passed in the table that was actually being worked
>on I could further say if table is in (tasks, calls, assignments) then
>update activity and show that someone was updating their work load.

Database wide triggers are probably incompatible with the SQL security
model. The next best thing, generic triggers, are extremely handy things
and are implemented in Netfrastructure. The triggers themselves are
written in Java and declared in the data definition language. An example:

upgrade trigger PAGES_MODERATE_TRIGGER for PAGES
before insert before update before delete active position 0 class
MODERATOR
using 'netfrasoft.modules.Moderator.updateTrigger'

The actual trigger, which maintains a list of pages pending moderation, is
listed below.
The application containing the example is a collaboration system with many
different tables holding different types of content, but containing a core
set of
fields, specifically author, group, and status. Other useful examples include
change tracking and replication.

While not strictly required, generic triggers pretty much need computationally
cheap access to meta-data including table and schema name, field names,
field types, and primary key information. A cheap and easy way to iterate
through a table's field is also pretty much required.

I'm sure the Firebird trigger language could be extended to supply these
functions.
But there's no way I'm going to leave the topic without a plug for an embedded
Java (or other suitable language) running in a proper sandbox.

-----------------------------------------------------------------------------------------------------------------------
public static void updateTrigger (NfsConnection connection,Record
oldVersion,Record newVersion, int type)
throws SQLException
{
//Debug.println ("updateTrigger newVersion: " + newVersion);
if (newVersion == null)
{
deletePending (connection, oldVersion);
return;
}

String newStatus = newVersion.getString ("pending");

if (newVersion.getInt ("group_id") == 0)
{
newVersion.setString ("pending", "N");
return;
}

// An update from approved (N) or revision (R) turned into
pending (P)

if (oldVersion == null || (!oldVersion.getString
("pending").equals ("Y")))
{
//Debug.println ("updateTrigger oldStatus: " +
oldVersion.getString ("pending"));
newStatus = "Y";
newVersion.setString ("pending", newStatus);
}

if (!newStatus.equals ("Y"))
{
deletePending (connection, newVersion);
return;
}

PreparedStatement statement = connection.prepareStatement (
"replace into pending_pages
(author,table,key,group_id,last_update) values (?,?,?,?,'now')");
int n = 1;
statement.setString (n++, newVersion.getString ("author"));
statement.setString (n++, newVersion.getTableName());
statement.setString (n++, getKey (newVersion));
statement.setString (n++, newVersion.getString ("group_id"));
statement.executeUpdate();
statement.close();
}


Jim Starkey