Advantage Developer Zone

 
 
 

Advantage 8.0 Trigger Enhancements

Friday, April 14, 2006

With the release of Advantage 8.0 many new trigger enhancements have been made. These include the ability to disable triggers programmatically and through SQL statements, using the new SQL scripting support and more support for AFTER trigger events.

Disabling Triggers

Triggers can now be selectively disabled giving the developer more control over how the triggers are used. Triggers can be disabled for a single user, all database users, a single trigger or for a single table. Changing the state of a trigger will take place immediately and in most cases will be persisted until explicitly changed. The exception to this is when disabling triggers for a specific user, in this case the disabled setting will only apply until the user disconnects.

This functionality can be very useful when doing bulk INSERT, UPDATE or DELETE operations where triggers could potentially affect performance. This may also be used for the ADSSYS or other “system” users to avoid audit type triggers during automated data changes.

To disable a trigger use the sp_DisableTriggers stored procedure. The parameters for sp_DisableTriggers are; ObjectName, Parent, AllUsers and Options. The ObjectName can be a user, table, view or a trigger. The Parent parameter is the name of the table if the ObjectName is a trigger. AllUsers is either True to disable the trigger(s) for everyone or False to disable the trigger(s) for only the current user. Options is reserved for future use and must be set to 0.

The following example disables all triggers on the Employees table for the current user.

EXECUTE PROCEDURE sp_DisableTriggers('Employee', Null, False, 0)

 
To enable any disabled triggers us the sp_EnableTriggers stored procedure. The parameters are the same as for sp_DisableTriggers. The following example enables the triggers on the Employees table for all users.
 
EXECUTE PROCEDURE sp_EnableTriggers('Employee', Null, True, 0)
 

Modifying the Current Record

In previous versions of Advantage modifying the same record with an AFTER Trigger caused a locking error. With the release of Advantage 8.0 this limitation has been removed. This makes it much easier to change field values after an update has been posted. In the past to change a field value after an update an INSTEAD OF trigger had to be written. The example below is an INSTEAD OF Trigger for the Employee table:
 
   UPDATE Employee SET
            EmployeeNumber = __new.EmployeeNumber,
            FirstName = __new.FirstName,
            LastName = __new.LastName,
            Salary = __new.Salary,
            Department = __new.Department,
            ModifiedBy = USER()
   WHERE EmployeeNumber = (SELECT EmployeeNumber FROM __new)
 
This makes the SQL Statement much more complicated and if the table structure changes the Trigger will have to be modified to reflect the changes. Now this same task can be accomplished using an AFTER Trigger with a much simpler script.
 
   UPDATE Employee SET
            ModifiedBy = USER()
  WHERE EmployeeNumber = (SELECT EmployeeNumber FROM __new)
 

Scripting Support for Triggers

Advantage 8.0 now supports the ANSI SQL 2003 Persistent Stored Module (PSM) standard which supports local variables and control structures including IF statements and WHILE loops. For complete information on what is supported please refer to the Advantage Help File.
 
Although the use of Trigger Containers gives Advantage developers the ability to do very complex operations with triggers sometimes a separate module is not needed. The SQL Scripting support allows for more powerful triggers without the need for a separate module. We will add some logic to the trigger example from above. Instead of putting ADSSYS into the ModifiedBy field we want to put SYSTEM which will be easier for the end users to understand all other users will have their username logged.
 
IF USER() = 'ADSSYS' THEN
   UPDATE Employee SET Modified By = 'SYSTEM'
   WHERE Employee Number = (SELECT Employee Number FROM __new);
ELSE
   UPDATE Employee SET Modified By = USER()
   WHERE Employee Number = (SELECT Employee Number FROM __new);
END IF;
 

Summary

Advantage 8.0 enhances trigger functionality making them much more flexible and easier to implement. Addition of SQL scripting allows for quick implementation of triggers as well as a reduced learning curve due to the use of ANSI SQL standards.

You can get more information on SQL scripting and Triggers, along with many other Advantage topics, in our Online Seminar Archive.