Overview of Triggers

Unlike a stored procedure, which is executed only when a client application includes a call to it, triggers are always invoked in response to an associated data event. For example, you can create a trigger that executes when a record is being inserted into your customer table. It doesn’t matter whether you are inserting the record from the customer table using the Table Browser in the Data Architect or from a client application—the trigger will execute.

This is an important point, so let us repeat it in a slightly different way. There is no way to circumvent the execution of a trigger during a record-level operation on a table. By comparison, a given stored procedure is only executed when a client application specifically invokes it. Consequently, the operations embodied in a trigger are guaranteed to take place in response to an event, while those in a stored procedure are not.

You write triggers to perform validation or to perform additional tasks in response to a data operation. For example, a trigger can be used to generate a unique customer ID each time a new record is being inserted into the customer table. Likewise, a trigger can be used to write to a log table each time a change is made to a customer record. This type of operation is usually called an audit trail.

As with AEPs (Advantage Extended Procedures), you can write triggers using any development environment that can create Windows DLLs (dynamic link libraries), Linux SO (shared object) libraries, in-process COM (component object model) objects, or .NET class libraries (.NET managed assemblies). Because each of these libraries can have one or more triggers, they are referred to as trigger containers for convenience. You can also write triggers using SQL scripts. A SQL script is a series of one or more SQL statements. When a SQL script includes two or more SQL statements, they are separated by semicolons.

Note

The only triggers supported by ADS for NetWare are SQL triggers.

The fact that ADS can execute triggers written in languages other than SQL is significant. Most database servers only support SQL-based triggers. While those servers often can include some primitive control structures in the SQL triggers, this SQL is necessarily quite limited compared to most programming languages. As a result, when you write your triggers using DLLs, shared object libraries, COM objects, or .NET class libraries, your ADS triggers can be significantly more powerful than those you can create for most other database servers.

If you implement your trigger as a DLL, COM object, shared object library, or .NET class library, your triggers are passed seven parameters. The first parameter is a unique number that identifies the client connection whose actions are initiating the trigger.

The second parameter is a handle to a connection. You use this connection for two purposes. First, you can use it to access any tables, views, or stored procedures in the data dictionary with which your trigger is associated. Second, you use this connection to work with special temporary, in-memory tables that ADS creates with information about the trigger event. Because this connection is associated with the connection that initiated the trigger, if the initiating connection is currently in a transaction, any operations that you perform within your trigger are processed in that same transaction.

Note

These first two parameters are the same as the first two parameters passed to a stored procedure function.

The third parameter of a trigger is the trigger object’s name, as defined in the data dictionary. The fourth parameter is the name of the table in the data dictionary with which the trigger is associated.

The fifth parameter identifies what trigger event is executing, and the sixth parameter is the type of trigger. Finally, the seventh parameter is the internal record number of the record being affected by the trigger.

As mentioned earlier, the connection handle passed in the second parameter provides you with access to one or more temporary in-memory tables. One of these is the error table, and that table has the name _ _error (this name is preceded by two underscore characters). The _ _error table has two fields: ERRORNO and MESSAGE. If you insert a record into the _ _error table, the trigger will fail, ADS will return an error, and the trigger event will not take place. For instance, if a BEFORE update trigger is executing, inserting a record into _ _error will prevent the record from being inserted (and any AFTER triggers will not fire either).

When you register your trigger into a data dictionary, it gives you the option to create additional temporary in-memory tables that can be accessed from the trigger. One option is to include value tables. For update triggers, these tables are named _ _new and __old (two underscore characters precede each of these names). The _ _old table c ontains the original values of the table’s fields, and the _ _new table contains the data that needs to be updated. Insert event triggers only include the _ _new table, and delete event triggers can only access the _ _old table. Both of these tables contain exactly one record.

When you choose to include value tables for access by your triggers, you can optionally include memo and BLOB (binary large objects) data in the _ _old and _ _new tables. If your triggers do not need to work with memo or BLOB data, you can increase the performance of your trigger by omitting these types of fields.

Note that the _ _old, _ _new, and _ _error tables that you use in triggers are in-memory tables. Operations performed on in-memory tables are very fast.

The code that appears in a trigger is often used to write to one or more tables in a database. When a trigger writes to a table, there exists the possibility that the write operation itself will fire another trigger, which could then possibly write to another table, firing yet another trigger, and so on, and so on. To prevent an infinitely recursive trigger execution (which would ultimately stop when you run out of stack space), ADS will only permit trigger recursion to 64 levels.

ADS supports triggers on three types of events. These are record insertions, record updates, and record deletions. For each of these events, ADS provides three types of triggers. These are BEFORE triggers, INSTEAD OF triggers, and AFTER triggers. Each of these trigger types is discussed in the following sections.

BEFORE Triggers

A BEFORE trigger is one that is executed before the event takes place. For example, a BEFORE trigger for a delete event will execute prior to a record’s deletion. From within this trigger, your code can evaluate the record that is being deleted and take an appropriate action. The action may be to do nothing, in which case the record will be deleted.

Alternatively, you can write an error message to the _ _error table, in which case no further action is taken by ADS, and the record will not be deleted. When you write to the _ _error table from within a trigger, no further triggers for that operation on that record will be fired. For example, if you write an error to the _ _error table from a BEFORE delete trigger, any AFTER triggers associated with deletions on that same record will be skipped for that data operation. (As is discussed in the next section, you cannot have a BEFORE trigger and an INSTEAD OF trigger on the same event type.)

BEFORE triggers permit you to implement many of the same types of validation that can be performed using field-level and record-level constraints. In most cases, if you have the choice to perform validation using either a trigger or constraints, it’s nearly always preferable to use constraints. The reason for this is that constraints are easier to configure and manage, and they execute faster.

On the other hand, there are many types of validation that can be performed by a trigger that cannot be accomplished using constraints. For example, if validation can only be accomplished by verifying that data being written to a table is consistent with data values stored in other tables, and those relationships cannot be represented using referential integrity, triggers provide you with a reliable solution for validating the data.

While a BEFORE trigger can validate an operation for a table, at least with respect to insert and update triggers, a BEFORE trigger cannot be used to change the values of the record being affected. When you need to change the data that is being written to a table from within a trigger, use an INSTEAD OF trigger.

INSTEAD OF Triggers

INSTEAD OF triggers replace the event they are associated with. For example, if you create an INSTEAD OF insert trigger, ADS will not insert the associated record. If you want the record to be inserted, you do it yourself using the code in your trigger.

Before an INSTEAD OF trigger fires, ADS has already verified that the requested operation does not violate the table’s field and record constraints. Note, however, that if the affected table is involved in one or more referential integrity relationships, those constraints are not applied unless the INSTEAD OF trigger completes without an error.

Using an INSTEAD OF trigger disables the execution of any AFTER triggers for the same event on the same table. If you add an INSTEAD OF trigger to a table event where there is already an AFTER trigger, move the AFTER trigger code to the INSTEAD OF trigger, and then delete the other trigger definitions.

INSTEAD OF triggers are a powerful tool for performing additional actions to coincide with an event. One of the more common uses is to insert a timestamp into a field that indicates the last time a record was updated. From within the INSTEAD OF trigger, you would update the existing table based on the data in the _ _new table, inserting the current date/time into the field reserved for the last access timestamp in the process.

Another example involves creating an audit trail. From the INSTEAD OF delete trigger you can write the values of the deleted record, as well as the time of deletion, to a special audit trail table. From the INSTEAD OF insert trigger, you can write the values of the fields being inserted, as well as the time of insertion, into the audit trail table. From the INSTEAD OF update trigger, you can write the values of the fields whose values have changed, as well as the time of the change, to the audit trail table. Importantly, so long as the trigger is being executed in a transaction, if for some reason the transaction must be rolled back, any changes written to the audit trail table in the trigger would also be rolled back. (An AFTER trigger can also be used for this purpose, but only if there is no INSTEAD OF trigger on the same event type.)

Note

If your INSTEAD OF trigger adds to, or modifies, the data while it is being written to the underlying table, it may be necessary for you to refresh that record in the client application if you want to use the new values, depending on the Advantage data access mechanism you are using.

AFTER Triggers

AFTER triggers execute following the successful insertion, deletion, or modification of a record. For example, you can use AFTER triggers to perform notifications. Imagine that you have a special table in your database where errors are logged. Imagine further that these error records have a field indicating the severity of the error. After an error is inserted into the error database, the trigger can inspect the severity of the error, and if severe enough, send an e-mail to the network administrator or the help desk.

If you want to implement notification, but your table already includes an INSTEAD OF trigger, perform the notification inside of the INSTEAD OF trigger, because an AFTER trigger will not be fired if an INSTEAD OF trigger exists.