Advantage Database Server: The Official Guide

If you are creating SQL-script triggers, you can write and configure your triggers entirely in the Advantage Data Architect. In fact, if you needed to, you could create your triggers at runtime using SQL. However, creating triggers, like AEPs, is often part of your overall database design process, which means that you are most likely to create and register your triggers at design time.

For any trigger other than SQL scripts, creating and registering the trigger requires two distinct steps. In the first step, you create the trigger container using your development environment of choice. You can then register the trigger for your data dictionary using the Advantage Data Architect, although this registration can also be performed programmatically at runtime, if necessary. If you are creating your triggers using SQL scripts, you define your SQL and configure the trigger all within the provided Triggers dialog box in the Advantage Data Architect.

Triggers and Transactions

As you learned earlier, triggers are commonly used to apply changes to one or more tables within your database. This is especially true with INSTEAD OF triggers, which take responsibility for applying the requested change.

When you register your trigger with a data dictionary, you are given the option to perform all changes within the trigger in an all-or-none fashion within a virtual transaction. When you do this, and your trigger returns an error code, all changes performed within the trigger prior to the error are rolled back.

This implicit transaction is distinct from any transaction that might be active on the connection through which the trigger is firing. Specifically, if a trigger is fired through a connection that is in a transaction, and the trigger returns an error, all changes made by the trigger are restored, but the transaction remains in force. It will be up to the client application to either commit or roll back the transaction, depending on the needs of the application.

It is also important to note that you cannot start a transaction on the connection passed to the trigger from within your trigger’s code. Likewise, you cannot commit or roll back any transaction currently active on this connection.

You incur a performance penalty when you use this implicit transaction. Consequently, if data integrity is not as important as performance, you can disable implicit transactions using the Use implicit transactions to maintain data integrity option on the Triggers dialog box in the Advantage Data Architect.

ALS (Advantage Local Server) does not support transactions, nor does it support implicit transactions within triggers. As a result, if a trigger executed by ALS makes changes to more than one table, and an error occurs in the trigger, the changes already made to one or more tables within the trigger will remain.

Trigger Priority

When you define a trigger, you can assign a value to it that identifies the priority of the trigger. Trigger priority is used when you have two triggers of the same trigger type and event type on a given table. The trigger with a lower priority value gets executed before triggers with a higher priority value.

For example, imagine that you have two AFTER insert triggers on a particular table. If one of these triggers has a priority of 1, and the other has a priority of 2, the trigger with a priority of 1 will execute first, followed by the trigger with a priority of 2.

Trigger priority only applies to BEFORE and AFTER triggers. It does not apply to INSTEAD OF triggers. This is because you can have only one INSTEAD OF trigger for a given event type per table.

Triggers and Performance

Although triggers provide you with an important and creative way to ensure the integrity of your data, there are performance issues that you should consider. Specifically, triggers are executed once per row of data. For example, imagine that you have an INSTEAD OF delete trigger. If you execute a DELETE SQL query against the table that uses this trigger, and that query deletes 1,000 records, the trigger will execute 1,000 times. As a result, what might have been a fast query if no trigger was present may now be a time-consuming one.

The following are some guidelines for getting the most out of triggers:

• If a constraint can do the same task that you can perform from a trigger, use a constraint.

• When writing triggers, ensure that the code in your triggers is as efficient as possible.

• Avoid inherently slow operations from inside a trigger. For example, do not read from a file on the local file system within trigger code.

• If possible, only use the connection that is passed to your trigger to work with data. Obtaining a second connection is a relatively time-consuming operation.

Getting Started

As mentioned previously, you build your trigger either using a SQL script or using your development environment of choice. For all triggers other than SQL scripts, Advantage provides you with project templates for the most popular development environments, just as they do for AEPs. In most cases, you begin your trigger project by using the template, although you could also create your trigger project manually. If you decide to create your trigger project manually, it is a good idea to study the code created by the trigger template, so that you will implement your trigger project correctly for your particular development environment.

These templates include one function that provides you with a prototype for a trigger. This function can be used to create any type of trigger. For example, it could be used to create a BEFORE delete trigger, or an INSTEAD OF update trigger.

Normally, you will take this one function and change its name. For those environments, such as Delphi, where you must explicitly export your functions, you also need to change the name of the function that appears in the exports clause.

You will also likely make one or more copies of this trigger, providing each with a different name (and corresponding entry in the exports clause, if applicable). Each of these copies can be used to implement a different trigger. In other words, you can implement many different triggers in a single project.

The next section discusses how to create triggers for your DemoDictionary data dictionary.