Triggers

version 6.0

A Trigger is a method attached to a table. It is a property of a table. You do not call triggers; they are automatically invoked by the 4D database engine each time you manipulate table records (add, delete, modify, and load). You can write very simple triggers, and then make them more sophisticated.

Triggers can prevent "illegal" operations on the records of your database. They are a very powerful tool for restricting operations on a table, as well as preventing accidental data loss or tampering. For example, in an invoicing system, you can prevent anyone from adding an invoice without specifying the customer to whom the invoice is billed.

Triggers are a new type of method introduced in version 6. In previous versions of 4th Dimension, table methods (called file procedures) were executed by 4D only when a form for a table was used for data entry, display, or printing—they were rarely used. Note that triggers execute at a much lower level than the old file procedures. No matter what you do to a record via user actions (such as data entry) or programmatically (such as a call to SAVE RECORD), the trigger of a table will be invoked by 4D. Triggers are truly quite different from the old file procedures. If you have converted a version 3 database and you want to take advantage of the new Trigger capability, you must deselect the Use V3.x.x File Procedure Scheme property in the Preferences dialog box shown here.

By default, when you create a table in the Design Environment, it has no trigger.

To use a trigger for a table, you need to:

• Activate the trigger and tell 4D when it has to be invoked.

• Write the code for the trigger.

Activating a trigger that is not yet written or writing a trigger without activating it will not affect the operations performed on a table.

To activate a trigger for a table, you must select one of the Triggers options (database events) for the table in the Table Properties window:

If this option is selected, the trigger will be invoked each time a record is added to the table.

This happens when:

• Adding a record in data entry (User environment or ADD RECORD command).

• Creating and saving a record with CREATE RECORD and SAVE RECORD. Note that the trigger is invoked at the moment you call SAVE RECORD, not when it is created.

• Importing records (User environment or using an import command).

• Calling any other commands that create and/or save new records (i.e., ARRAY TO SELECTION, SAVE RELATED ONE, etc.).

• Using a Plug-in that calls the CREATE RECORD and SAVE RECORD commands.

If this option is selected, the trigger will be invoked each time a record of the table is modified.

This happens when:

• Modifying a record in data entry (User environment or MODIFY RECORD command).

• Saving an already existing record using SAVE RECORD.

• Calling any other commands that save existing records (i.e., ARRAY TO SELECTION, APPLY TO SELECTION, etc.).

• Using a Plug-in that calls the SAVE RECORD command.

If this option is selected, the trigger will be invoked each time a record of the table is deleted.

This happens when:

• Deleting a record (User environment or calling DELETE RECORD or DELETE SELECTION).

• Performing any operation that provokes deletion of related records through the deletion control options of a relation.

• Using a Plug-in that calls the DELETE RECORD command.

If this option is selected, the trigger will be invoked each time a record of the table is loaded. This includes all situations in which a current record is loaded from the data file. You will use this option less often than the three previous ones.

In order to optimize the operation of 4D, the On loading a record option never triggers a call to the trigger when using a command that can take advantage of the index.

In fact, when the index is used, records are not loaded and conversely, if the index is not used (i.e., if the field being processed is not indexed), records are loaded. That would mean that a trigger for which the On loading a record option was selected could either be or not be executed depending on the Indexed attribute for the processed field. Rather than keeping a behavior that is difficult to anticipate, the decision was made to never execute the trigger with the On loading a record option selected when using a command that could take advantage of the index.

Note: If the On loading a record option is selected, the trigger will be executed when a current record is loaded from the data file, except for the following functions:

• Queries: User queries that were prepared in the standard query editor or by using the QUERY or QUERY SELECTION commands.

• Order by: Sorts that were prepared in the standard Order by Editor or by using the ORDER BY command.

• On a series: Sum, Average, Min, Max, Std deviation, Variance, Sum square.

• Commands: RELATE ONE SELECTION, RELATE MANY SELECTION.

IMPORTANT: If you execute an operation or call a command that acts on multiple records, the trigger is called once for each record. For example, if you call APPLY TO SELECTION for a table whose current selection is composed of 100 records, the trigger will be invoked 100 times.

To create a trigger for a table, use the Explorer Window or press Alt (on Windows) or Option (Macintosh) and double-click on the table title in the Structure window. For more information, see the 4th Dimension Design Reference manual.

A trigger can be invoked for one of the four database events described above. Within the trigger, you detect which event is occurring by calling the Database event function. This function returns a numeric value that denotes the database event.

Typically, you write a trigger with a Case of structure on the result returned by Database event. You can use the constants of the Database Events theme:

      ` Trigger for [anyTable]
   C_LONGINT($0)
   $0:=0   ` Assume the database request will be granted
   Case of
      : (Database event=On Saving New Record Event)
         ` Perform appropriate actions for the saving of a newly created record
      : (Database event=On Saving Existing Record Event)
         ` Perform appropriate actions for the saving of an already existing record
      : (Database event=On Deleting Record Event)
         ` Perform appropriate actions for the deletion of a record
      : (Database event=On Loading Record Event)
         ` Perform appropriate actions for the loading into memory of a record
   End case

A trigger has two purposes:

• Performing actions on the record just before it is saved or deleted, or just after it has been loaded.

• Granting or rejecting a database operation.

Each time a record is saved (added or modified) to a [Documents] table, you want to "mark" the record with a time stamp for creation and another one for the most recent modification. You can write the following trigger:

      ` Trigger for table [Documents]
   Case of 
      : (Database event=On Saving New Record Event)
         [Documents]Creation Stamp:=Time stamp 
         [Documents]Modification Stamp:=Time stamp 
      : (Database event=On Saving Existing Record Event)
         [Documents]Modification Stamp:=Time stamp 
   End case 

Note: The Time stamp function used in this example is a small project method that returns the number of seconds elapsed since a fixed date was chosen arbitrarily.

After this trigger has been written and activated, no matter what way you add or modify a record to the [Documents] table (data entry, import, project method, 4D plug-in), the fields [Documents]Creation Stamp and [Documents]Modification Stamp will automatically be assigned by the trigger before the record is eventually written to the disk.

Note: See the example for the GET DOCUMENT PROPERTIES command for a complete study of this example.

To grant or reject a database operation, the trigger must return a trigger error code in the $0 function result.

Let's take the case of an [Employees] table. During data entry, you enforce a rule on the field [Employees]Social Security Number. When you click the validation button, you check the field using the object method of the button:

      ` bAccept button object method
   If (Good SS number ([Employees]SS number))
      ACCEPT
   Else
      BEEP
      ALERT ("Enter a Social Security Number then click OK again.")
   End if

If the field value is valid, you accept the data entry; if the field value is not valid, you display an alert and you stay in data entry.

If you also create [Employees] records programmatically, the following piece of code would be programmatically valid, but would violate the rule expressed in the previous object method:

      ` Extract from a project method
      ` ...
   CREATE RECORD ([Employees])
   [Employees]Name :="DOE"
   SAVE RECORD ([Employees]) `  DB rule violation! The SS number has not been assigned!
      ` ...

Using a trigger for the [Employees]table, you can enforce the [Employees]SS number rule at all the levels of the database. The trigger would look like this:

      ` Trigger for [Employees]
   $0:=0
   $dbEvent:=Database event
   Case of
      : (($dbEvent=On Saving New Record Event) | ($dbEvent=On Saving Existing Record Event))
      If (Not(Good SS number ([Employees]SS number)))
         $0:=-15050
      Else
         ` ...
      End if
         ` ...
   End case

Once this trigger is written and activated, the line SAVE RECORD ([Employees]) will generate a database engine error -15050, and the record will NOT be saved.

Similarly, if a 4D Plug-in attempted to save an [Employees] record with an invalid social security number, the trigger will generate the same error and the record will not be saved.

The trigger guarantees that nobody (user, database designer, Plug-in, 4D Open client with 4D Server) can violate the social security number rule, either deliberately or accidentally.

Note that even if you do not have a trigger for a table, you can get database engine errors while attempting to save or delete a record. For example, if you attempt to save a record with a duplicated value in a unique indexed field, the error -9998 is returned.

Therefore, triggers returning errors add new database engine errors to your application:

• 4D manages the "regular" errors: unique index, relational data control, and so on.

• Using triggers, you manage the custom errors unique to your application.

Important: You can return an error code value of your choice. However, do NOT use error codes already taken by the 4D database engine. We strongly recommend that you use error codes between -32000 and -15000. We reserve error codes above -15000 for the database engine.

At the process level, you handle trigger errors the same way you handle database engine errors:

• You can let 4D display the standard error dialog box, then the method is halted.

• You can use an error-handling method installed using ON ERR CALL and recover the error the appropriate way.

Notes:

• During data entry, if a trigger error is returned while attempting to validate or delete a record, the error is handled like a unique indexed error. The error dialog is displayed, and you stay in data entry. Even if you only use a database in the User environment (not in Custom Menus), you have the benefit of using triggers.

• When an error is generated by a trigger within the framework of a command acting on a selection of records (like DELETE SELECTION), the execution of the command is immediately stopped, without the selection having necessarily been completely processed. This case requires appropriate handling by the developer, based, for instance, on the temporary preservation of the selection, the processing and elimination of the error before trigger execution, etc.

Even when a trigger returns no error ($0:=0), this does not mean that a database operation will be successful—a unique index violation may occur. If the operation is the update of a record, the record may be locked, an I/O error may occur, and so on. The checking is done after the execution of the trigger. However, at the higher level of the executing process, errors returned by the database engine or a trigger are the same—a trigger error is a database engine error.

Triggers execute at the database engine level. This is summarized in the following diagram:

Triggers are executed on the machine where the database engine is actually located. This is obvious with a 4D single-user version. On 4D Server, triggers are executed within the acting process on the server machine, not on the client machine.

When a trigger is invoked, it executes within the context of the process that attempts the database operation. This process, which invokes the trigger execution, is called the invoking process.

In particular, the trigger works with the current selections, current records, table read/write states, and record locking operations of the invoking process.

Warning: A trigger cannot and must not change the current record of the table to which it is attached. Within a trigger, if you need to check a unique value on multiple fields, use the SET QUERY DESTINATION command, which allows you to query a table without changing the current selection or current record of the table.

Be careful about using other database or language objects of the 4D environment, because a trigger may execute on a machine other than that of the invoking process—this is the case with 4D Server!

• Interprocess variables: A trigger has access to the interprocess variables of the machine where it executes. With 4D Server, it can access a machine other than that of the invoking process.

• Process variables: An independent process variables table is shared by all the triggers. A trigger has no access to the process variables of the invoking process.

• Local variables: You can use local variables in a trigger. Their scope is the trigger execution; they are created/deleted at each execution.

• Semaphores: A trigger can test or set global semaphores as well as local semaphores (on the machine where it executes). However, a trigger must execute quickly, so you must be very careful when testing or setting semaphores from within triggers.

• Sets and Named selections: If you use a set or a named selection from within a trigger, you work on the machine where the trigger executes.

• User Interface: Do NOT use user interface elements in a trigger (no alerts, no messages, no dialog boxes). Accordingly, you should limit any tracing of triggers in the Debugger window. Remember that in Client/Server, triggers execute on the 4D Server machine. An alert message on the server machine does not help a user on a client machine. Let the invoking process handle the user interface.

Transactions must be handled at the invoking process level. They must not be managed at the trigger level. During one trigger execution, if you have to add, modify or delete multiple records (see the following case study), you must first use the In transaction command from within the trigger to test if the invoking process is currently in transaction. If this is not the case, the trigger may potentially encounter a locked record. Therefore, if the invoking process is not in transaction, do not even start the operations on the records. Just return an error in $0 in order to signal to the invoking process that the database operation it is trying to perform must be executed in a transaction. Otherwise, if locked records are met, the invoking process will have no means to roll back the actions of the trigger.

Note: In order to optimize the combined operation of triggers and transactions, 4D does not call triggers after the execution of VALIDATE TRANSACTION. This prevents the triggers from being executed twice.

Given the following example structure:

Note: The tables have been collapsed; they have more fields than shown here.

Let's say that the database "authorizes" the deletion of an invoice. We can examine how such an operation would be handled at the trigger level (because you could also perform deletions at the process level).

In order to maintain the relational integrity of the data, deleting an invoice requires the following actions to be performed in the trigger for [Invoices]:

• In the [Customer] record, decrement the Gross Sales field by the amount of the invoice.

• Delete all the [Line Items] records related to the invoice.

• This also implies that the [Line Items] trigger decrements the Quantity Sold field of the [Products] record related to the line item to be deleted.

• Delete all the [Payments] records related to the deleted invoice.

First, the trigger for [Invoices] must perform these actions only if the invoking process is in transaction, so that a roll-back is possible if a locked record is met.

Second, the trigger for [Line Items] is cascading with the trigger for [Invoices]. The [Line Items] trigger executes "within" the execution of the [Invoices] trigger, because the deletion of the list items are consequent to a call to DELETE SELECTION from within the [Invoices] trigger.

Consider that all tables in this example have triggers activated for all database events. The cascade of triggers will be:

   • [Invoices] trigger is invoked because the invoking process deletes an invoice
      • [Customers] trigger is invoked because the [Invoices] trigger updates the Gross Sales field
      • [Line Items] trigger is invoked because the [Invoices] trigger deletes a line item (repeated)
         • [Products] trigger is invoked because the [Line Items] trigger updates the Quantity Sold field
      • [Payments] trigger is invoked because the [Invoices] trigger deletes a payment (repeated)

In this cascade relationship, the [Invoices] trigger is said to be executing at level 1, the [Customers], [Line Items], and [Payments] triggers at level 2, and the [Products] trigger at level 3.

From within the triggers, you can use the Trigger level command to detect the level at which a trigger is executed. In addition, you can use the TRIGGER PROPERTIES command to get information about the other levels.

For example, if a [Products] record is being deleted at a process level, the [Products] trigger would be executed at level 1, not at level 3.

Using Trigger level and TRIGGER PROPERTIES, you can detect the cause of an action. In our example, an invoice is deleted at a process level. If we delete a [Customers] record at a process level, then the [Customers] trigger should attempt to delete all the invoices related to that customer. This means that the [Invoices] trigger will be invoked as above, but for another reason. From within the [Invoices] trigger, you can detect if it executed at level 1 or 2. If it did execute at level 2, you can then check whether or not it is because the [Customers] record is deleted. If this is the case, you do not even need to bother updating the Gross Sales field.

While handling an On Saving New Record Event database event, you can call the Sequence number command to maintain a unique ID number for the records of a table.

      ` Trigger for table [Invoices]
   Case of
      : (Database event=On Saving New Record Event)
            ` ...
         [Invoices]Invoice ID Number:=Sequence number ([Invoices])
            ` ...
   End case

Database event, Methods, Record number, Trigger level, TRIGGER PROPERTIES.