Limitations of Dexterity Table Triggers

  • Article

David Meego - Click for blog homepage "Why doesn't my Dexterity table trigger fire when the table is changed."

I have heard this question asked a number of times and so I thought I would highlight some of the possible reasons for the trigger failing to fire.  Some of the reasons can be resolved by coding, while other reasons are a limitation in the way Dexterity table triggers work and cannot be resolved from inside Dexterity.

Dexterity triggers are client side triggers which work from inside the current instance of the Dexterity application. They can only "see" what happens in the that instance of the application.  A Dexterity table trigger does not involve anything at the database level, there will not be a SQL Server trigger registered.

Dexterity table triggers are registered against a particular table and can be set as application wide (by using a 0 for the second parameter below) or restricted to a single form. You can register multiple triggers to have triggers restricted to multiple forms.  They are registered for specific table event(s).

The syntax for the registration of Dexterity table triggers is shown below:

Trigger_RegisterDatabase(anonymous(table table_name), form form_name , table_operations, script processing_procedure {, tag} )

where table_operations can be any of the following operations (add the constants together when more than one operation is needed):
 
TRIGGER_ON_DB_READ   (1)
TRIGGER_ON_DB_READ_LOCK   (2)
TRIGGER_ON_DB_ADD   (4)
TRIGGER_ON_DB_UPDATE   (8)
TRIGGER_ON_DB_DELETE   (16)
 
Note: the value of the constant is shown above in parenthesis.

For trigger to fire, the table event must:

  • Be a successful table event (no errors at the database level).
  • Occur using a table buffer which has the trigger registered against it.
  • Be one of the operations defined when the trigger was registered.

For tables not in the Dynamics.dic, you will need to use cross dictionary triggers using the Trigger_RegisterDatabaseByName() function.

So based on the information above, the following situations could cause a table change to occur without the table trigger firing:

  1. The table change is made by an external (non-Dexterity) application. This can be any external program such as eConnect, Integration Manager, Business Portal, directly at the table level by a SQL Administrator or a third party customisation.
     
  2. The table change is made from within the Dexterity application by a non-Dexterity method.  This could be a call to a SQL stored procedure, use of pass through SQL scripts or even access via customisation written using Visual Studio Tools or Visual Basic for Applications (VBA) and ActiveX Data Objects (ADO). Any method which does not use Dexterity commands with a table buffer.
     
  3. The Dexterity customisation with the table trigger is not installed on the application instance running.
     
  4. The table event is using Dexterity commands, but is using a different table buffer to the one against which the trigger is registered.
     
  5. The table event is using Dexterity commands on the same table buffer, for different operations for which the trigger is registered.
     
  6. The table event is using a different table definition to the one against which the trigger is registered. In Dexterity it is possible to have duplicated table definitions referring to the same physical table. This could be to facilitate opening the table twice at the same time or could be because a customisation needs access to a table in a 3rd party product and it was better to duplicate the table rather than use pass through sanScript via the execute() function.

The first two of the reasons are situations where a Dexterity table trigger is not going to work as Dexterity just never "sees" the table change being made. The only method available for these situations is to use a trigger at the SQL Server level.

The third reason is easily solved by installing the customisation chunk file on all workstations.  It was only in this list to highlight the importance of all workstations having all the same dictionaries installed.

The fourth reason is important, but can be coded around.  Using an unrestricted (no form specified) table trigger is not recommended as it could cause the trigger to fire at unexpected times. (For example: when posting, running table maintenance or history removal.) So most developers use one or more table triggers restricted to specific forms.  However, those triggers will only fire if the table event occurs in the specified form's table buffer.  If the code on that form uses a function or procedure to perform the table event and the form's table buffer is not passed as a parameter, a separate instance of the table buffer will be created for the scope of the function or procedure.  As this separate table buffer is not the same as the form's table buffer, a form restricted table trigger will not fire. In this situation it might require an unrestricted table trigger to be used.

Note: If using an unrestricted table trigger, it is good practice to make sure it will not fire at unexpected times.  It is possible to use additional triggers to enable and disable the trigger using the tag value captured when the table trigger was registered.  For example, the trigger could be disabled immediately after registration and then enabled just before the function or procedure described above is executed and then disabled again afterwards.  This is a similar method to the Three Trigger Technique.

The fifth reason is not so common, but can also be changed in the code. It is most likely to happen when using a read trigger.  The get table command does not apply a lock and so is covered by the TRIGGER_ON_DB_READ operation.  The change table and edit table commands do apply locks and so are covered by the TRIGGER_ON_DB_READ_LOCK operation.  If you wish to capture any read event (with or without a lock) you need to use TRIGGER_ON_DB_READ + TRIGGER_ON_DB_READ_LOCK.  The Add, Update and Delete events are self explanatory.

The sixth reason is also rare, but can happen. There are times, let's say for reporting on data from a 3rd party product where we need to be able to access a table from another dictionary as though it is in our dictionary. In this case the methods using execute() and pass through sanScript are not going to work.  So we can create a Combined Dictionary (KB 930350 Secure Link) and transfer the table definition (without referenced resources) to our dictionary.  This technique works well for accessing the data, but if we make any changes using our duplicate table definition, the trigger on the original table will not see it.  You would need to also register a cross dictionary trigger on the duplicate table.

Let me know if this is helpful.

David

04-Feb-2010: Added additional reason (Duplicate table definitions) for table triggers to fail.