Using Triggers

Defining Triggers

There are several ways to define a trigger for a specific table:

• Use Studio to add an SQL trigger definition to the class definition that corresponds to the table. For example, this definition of the MyApp.Person class includes a definition of the LogEvent trigger, which is invoked after each call to INSERT:

  Class MyApp.Person Extends %Persistent [DdlAllowed]
  {
      // ... Definitions of other class members
  
      /// This trigger updates the LogTable after every insert
      Trigger LogEvent [ Event = INSERT, Time = AFTER ]
      {
          // get row id of inserted row
          NEW id
          SET id = {ID}
  
          // INSERT value into Log table
          &sql(INSERT INTO LogTable 
              (TableName, IDValue) 
              VALUES ('MyApp.Person', :id))
      }
      // ... Definitions of other class members
  
  }

  

• Using the DDL CREATE TRIGGER command to create a trigger.

The maximum number of user-defined triggers for a class is 200.

Types of Triggers

A trigger is defined by the following:

• The type of event that causes it to execute. A trigger may be either a single-event trigger or a multiple-event trigger. A single-event trigger is defined to execute when an INSERT, an UPDATE, or a DELETE event occurs on the specified table. A multiple-event trigger is defined to execute when any one of the multiple specified events occurs on the specified table. You can define an INSERT/UPDATE, an UPDATE/DELETE, or an INSERT/UPDATE/DELETE multiple-event trigger. The type of event is specified in a class definition by the required Event trigger keyword.

• The time that the trigger executes: Before or After the event occurs. This is specified in a class definition by the optional Time trigger keyword. The default is Before.

• You can associate multiple triggers with the same event and time; in this case, you can control the order in which multiple triggers are fired using the Order trigger keyword. Triggers with a lower Order value are fired first. If multiple triggers have the same Order value, then the order in which they are fired is not specified.

• The optional Foreach trigger keyword provides additional granularity. This keyword controls whether the trigger is fired once per row (Foreach = row), once per row or object access (Foreach = row/object), or once per statement (Foreach = statement). A trigger defined with no Foreach trigger keyword is fired once per row. If a trigger is defined with Foreach = row/object, then the trigger is also called at specific points during object access, as described later in this chapter.

For a full list of trigger keyword, see the Class Definition Reference.

The following are the available triggers and their equivalent callback methods:

• BEFORE INSERT (equivalent to %OnBeforeSave())

• AFTER INSERT (equivalent to %OnAfterSave())

• BEFORE UPDATE (equivalent to %OnBeforeSave())

• AFTER UPDATE (equivalent to %OnAfterSave())

• BEFORE UPDATE OF specified column(s)

• AFTER UPDATE OF specified column(s)

• BEFORE DELETE (equivalent to %OnDelete())

• AFTER DELETE (equivalent to %OnAfterDelete())

For further details, refer to CREATE TRIGGER in the InterSystems SQL Reference.

How Trigger Code Works

Each trigger contains one or more lines of code that perform a triggered action. This code is invoked by the SQL Engine whenever the event associated with the trigger occurs. If the trigger is defined using CREATE TRIGGER, this action code can be written in either ObjectScript or SQL. (Caché converts code written in SQL to ObjectScript in the class definition.) If the trigger is defined using Studio, this action code must be written in ObjectScript.

Within trigger code, you can refer to field values (for the fields belonging to the table the trigger is associated with) using a special {field_name} syntax. For example, the following definition of the LogEvent trigger in the MyApp.Person class includes a reference to the ID field, as {ID}:

Class MyApp.Person Extends %Persistent [DdlAllowed]
{
    // ... Definitions of other class members

    /// This trigger updates the LogTable after every insert
    Trigger LogEvent [ Event = INSERT, Time = AFTER ]
    {
        // get row id of inserted row
        NEW id
        SET id = {ID}

        // INSERT value into Log table
        &sql(INSERT INTO LogTable 
            (TableName, IDValue) 
            VALUES ('MyApp.Person', :id))
      }
   // ... Definitions of other class members

}

If trigger code succeeds, it sets %ok=1. If trigger code fails, it sets %ok=0. If INSERT or UPDATE trigger code fails and there is a foreign key constraint defined for the table, Caché releases the lock on the corresponding row in the foreign key table.

The trigger code can also refer to the variable %oper, which contains the name of the event that fired the trigger (INSERT, UPDATE, or DELETE).

Macros within Trigger Code

Your trigger code can contain a macro definition that references a field name (using {field_name} syntax). However, if your trigger code contains a #Include preprocessor directive for a macro that references a field name (using {field_name} syntax), the field name cannot be accessed. This is because Caché translates {field_name} references in the trigger code before the code is passed to the macro preprocessor. If a {field_name} reference is in the #Include file, it is not “seen” in the trigger code, and is therefore not translated.

The work-around for this situation is to define the macro with an argument, then pass the {field_name} in to the macro in the trigger. For example, the #Include file could contain a line such as the following:

#Define dtThrowTrigger(%val) SET x=$GET(%val,"?")

And then within the trigger invoke the macro supplying the {field_name} syntax as an argument:

  $$$dtThrowTrigger({%%ID})   

{name*O}, {name*N}, and {name*C} Trigger Code Syntax

Three syntax shortcuts are available in UPDATE trigger code.

You can reference the old (pre-update) value using the following syntax:

{fieldname*O}

where fieldname is the name of the field and the character after the asterisk is the letter “O” (for Old). For an INSERT trigger, {fieldname*O} is always the empty string ("").

You can reference the new (post-update) value using the following syntax:

{fieldname*N}

where fieldname is the name of the field and the character after the asterisk is the letter “N” (for New). This {fieldname*N} syntax can be used only to reference a value to be stored; it cannot be used to change the value. You cannot set {fieldname*N} in trigger code. Computing the value of a field on INSERT or UPDATE should be achieved by other means, such as SqlComputeOnChange.

You can test whether a field value has been changed (updated) using the following syntax:

{fieldname*C}

where fieldname is the name of the field and the character after the asterisk is the letter “C” (for Changed). {fieldname*C} evaluates to 1 if the field has been changed and 0 if it has not been changed. For an INSERT trigger, Caché sets {fieldname*C} to 1.

For a class with stream properties, an SQL trigger reference to the stream property {Stream*N} and {Stream*O} returns the OID for the stream, if the SQL statement (INSERT or UPDATE) did not insert/update the stream property itself. However, if the SQL statement did insert/update the stream property, {Stream*O} remains the OID, but the {Stream*N} value is set to one of the following:

• BEFORE trigger returns the value of the stream field in whatever format it was passed to the UPDATE or INSERT. This could be the literal data value that was entered into the stream property, or the oref or oid of a temporary stream object.

• AFTER trigger returns the Id of the stream as the {Stream*N} value. This is the Id value Caché stored in the ^classnameD global for the stream field. This value is in the appropriate Id format based on the CLASSNAME type parameter for the stream property.

If a stream property is updated using Caché objects, the {Stream*N} value is always an oid.

Note:

For a trigger for child-tables created by an array collection of serial objects, trigger logic works with object access/save but does not work with SQL access (INSERT or UPDATE).

Triggers and Object Access

If a trigger is defined with Foreach = row/object, then the trigger is also called at specific points during object access, depending on the Event and Time keywords of the trigger definition, as follows:

As a consequence, it is not necessary to also implement callback methods in order to keep SQL and object behavior synchronized,

For information on Foreach trigger keyword, see the Caché Class Definition Reference.

Triggers and Transactions

A trigger executes trigger code within a transaction. It sets the transaction level, then executes the trigger code. Upon successful completion of trigger code, the trigger commits the transaction.

With an AFTER INSERT statement level ObjectScript trigger, if the trigger sets %ok=0 the insert of the row fails with an SQLCODE -131 error. Transaction rollback may occur, as follows:

• If AUTO_COMMIT=ON, the transaction for the INSERT will be rolled back.

• If AUTO_COMMIT=OFF, it is up to the application to either rollback or commit the transaction for the INSERT.

• If NO_AUTO_COMMIT mode was used, no transaction was started, so the INSERT cannot be rolled back.

The AUTO_COMMIT mode is established using the SET TRANSACTION %COMMITMODE option, or the $SYSTEM.SQL.SetAutoCommit()Opens in a new tab method.

The trigger can set an error message in the %msg variable in the trigger. This message will be returned to the caller, giving information why the trigger failed.

The %ok and %msg system variables are described in the System Variables section of the “Using Embedded SQL” chapter of this manual.

Listing Triggers

You can use the INFORMATION.SCHEMA.TRIGGERSOpens in a new tab class to list the currently defined triggers. This class lists for each trigger the name of the trigger, the associated schema and table name, and the trigger creation timestamp. For each trigger it lists various properties, including the EVENTMANIPULATION property (INSERT, UPDATE, DELETE, INSERT/UPDATE, INSERT/UPDATE/DELETE), the ACTIONTIMING property (BEFORE, AFTER), and the ACTIONSTATEMENT property, which is the generated SQL trigger code.