Advantage Developer Zone


Disabling Triggers

Wednesday, June 14, 2006

Triggers provide powerful server side functionality that responds to changes made by client applications. Triggers can execute custom code or SQL scripts in response to INSERT, UPDATE and DELETE operations. This allows the developer to distribute processing between the client and server as well as centralize business logic.

However, there are some times when the developer may not want these triggers to run. This could be in the case of a bulk operation, where many records and/or tables are being affected. Advantage version 8.0 allows triggers to be disabled based on the application’s needs.

Why Disable Triggers?

Depending on what type of operation you are doing, you may wish to disable triggers temporarily or keep them disabled until you need them again. Consider an example where you have a trigger that logs modifications to your tables into an archive table for historical purposes. If you are importing a large batch of records, you do not necessarily want to log the details of each insert operation. You can temporarily disable the trigger, insert the records, update the archive table with an appropriate entry and then enable the trigger once again. This can improve the efficiency of the import process as well as keep the archive log compact and more readable.

If you have an option in your program to archive records instead of deleting them, this functionality can be implemented using a trigger. A trigger can insert the contents of a deleted record into a separate table whenever a record is deleted. By simply enabling or disabling this trigger you can add this archiving functionality to your application. This gives your users the ability to choose to permanently delete records or maintain a copy in a separate table.

Trigger Disabling Options

With the release of Advantage 8.0 the ability to disable triggers was introduced. There are four options for disabling triggers; by connection, by user, by table or for all database users. Disabling triggers by connection is not persisted; the triggers will be enabled the next time the user connects to the dictionary. Each of the other disabling options are persisted until changed programmatically or by using the Advantage Data Architect (ARC).

Triggers can be disabled individually as well. If you have more than one trigger defined for a table you can selectively enable one or all of the triggers as needed. This allows you to once again provide customizable features that are implemented on the server instead of in your client application.

Triggers are disabled for all replication actions by default. Replication will fire a conflict trigger when a replication conflict is detected. This is the only trigger type that will fire when a record is replicated from one Advantage server to another.

How do I Disable Triggers

Triggers can be disabled in the Advantage Data Architect. Triggers are disabled for all users at either the database or table level.

To disable triggers for a specific user or connection you use the built-in system procedures. The two system procedures are sp_DisableTriggers and sp_EnableTriggers. The syntax for each is shown below:

sp_DisableTriggers( ObjectName, CHARACTER,200,
Parent, CHARACTER,200,
AllUsers, LOGICAL,
Options, INTEGER )

sp_EnableTriggers( ObjectName, CHARACTER,200,
Parent, CHARACTER,200,
AllUsers, LOGICAL,
Options, INTEGER )

The ObjectName is the name of the trigger, table or dictionary to enable or disable. The Parent parameter is the table name or view name when the ObjectName is a trigger. The AllUsers parameter should be set to True if you want to enable/disable the trigger for all users. Set the parameter to False if the trigger should be disabled for only the current user. The Options parameter is reserved for future use and should be set to 0.

You call system procedures just like any other stored procedure that is defined in your database. The following two examples disable and then re-enable a trigger named LogEmp on the employee table.

EXECUTE PROCEDURE sp_DisableTrigger(’LogEmp’, ’Employee’, False, 0)

EXECUTE PROCEDURE sp_EnableTrigger(’LogEmp’, ’Employee’, False, 0)


Triggers are a powerful tool that can be used with your data dictionary to add functionality to your application. Triggers can ensure data integrity since they are enforced for any application that connects to your database. However, there are times when these triggers are not necessary. With Advantage you can now selectively disable triggers giving you more control over this powerful feature.