Skip to main content

Using Triggers

This chapter describes how you can define triggers in Caché SQL. Triggers are lines of code that are executed in response to certain SQL events. This chapter includes the following topics:

Defining Triggers

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

  • Include a trigger definition in the persistent class definition that projects to an SQL 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 data into the MyApp.Person table:

    Class MyApp.Person Extends %Persistent [DdlAllowed]
    {
        // ... Definitions of other class members
    
        Trigger LogEvent [ Event = INSERT, Time = AFTER ]
        {
            // trigger code to log an event 
        }
     }

    For further details, refer to Trigger Definitions.

  • Use the SQL CREATE TRIGGER command to create a trigger. This generates a trigger object definition in the corresponding persistent class. SQL trigger names follow identifier naming conventions. Caché uses the SQL trigger name to generate a corresponding trigger class entity name.

You must have the %CREATE_TRIGGER administrative-level privilege to create a trigger. You must have the %DROP_TRIGGER administrative-level privilege to drop a trigger.

Note:

This chapter describes Caché SQL triggers. Caché MultiValue triggers are completely separate from Caché SQL triggers. An SQL update will not fire a MultiValue trigger; a MultiValue update will not fire an SQL trigger.

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

Note:

Caché SQL does not support triggers on tables projected by collections. A user cannot define such a trigger, and the projection of a collection as a child table does not consider triggers involving that base collection.

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. You cannot specify a multi-event trigger using the SQL CREATE TRIGGER command.

  • 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. You can list the Foreach value for each trigger using the ACTIONORIENTATION property of INFORMATION.SCHEMA.TRIGGERSOpens in a new tab

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

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

Note:

When a trigger is executed, it cannot directly modify the value of a property in the table that is being processed. This is because Caché executes trigger code after field (property) value validation code. For example, a trigger cannot set a LastModified field to the current timestamp in the row being processed. However, the trigger code can issue an UPDATE to a field value in the table. The UPDATE performs its own field value validation.

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

AFTER Triggers

An AFTER trigger executes after an INSERT, UPDATE, or DELETE event occurs:

  • If SQLCODE=0 (event completed successfully) Caché executes the AFTER trigger.

  • If SQLCODE is a negative number (event failed) Caché does not executes the AFTER trigger.

  • If SQLCODE=100 (no row was found to insert, update, or delete) Caché executes the AFTER trigger.

Recursive Triggers

Trigger execution can be recursive. For example, if table T1 has a trigger that performs an insert into table T2 and table T2 has a trigger that performs an insert into table T1. Recursion can also occur when table T1 has a trigger that calls a routine/procedure and that routine/procedure performs an insert into T1. Handling of trigger recursion depends on the type of trigger:

  • Row and Row/Object triggers: Caché does not prevent row triggers and row/object triggers from being executed recursively. It is the programmer’s responsibility to handle trigger recursion. A runtime <FRAMESTACK> error may occur if the trigger code does not handle recursive execution.

  • Statement triggers: Caché prevents an AFTER statement trigger from being executed recursively. Caché will not issue an AFTER trigger if it detects that the trigger has been called previously in the execution stack. No error is issued; the trigger is simply not executed a second time.

    Caché does not prevent a BEFORE statement trigger from being executed recursively. It is the programmer’s responsibility to handle BEFORE trigger recursion. A runtime <FRAMESTACK> error may occur if the BEFORE trigger code does not handle recursive execution.

Trigger Code

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.

Because the code for a trigger is not generated as a procedure, all local variables in a trigger are public variables. This means all variables in triggers should be explicitly declared with a NEW statement; this protects them from conflicting with variables in the code that invokes the trigger.

%ok, %msg, and %oper System Variables

  • %ok: A variable used only in trigger code. If trigger code succeeds, it sets %ok=1. If trigger code fails, it sets %ok=0. If during trigger execution an SQLCODE error is issued, Caché sets %ok=0. When %ok=0, the trigger code aborts and the trigger operation and the operation that invoked the trigger are rolled back. 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.

    Trigger code can explicitly set %ok=0. This creates a runtime error that aborts execution of the trigger and rolls back the operation. Commonly, before setting %ok=0, trigger code explicitly sets the %msg variable to a user-specified string describing this user-defined trigger code error.

    The %ok variable is a public variable which must be explicitly NEWed. %ok is unchanged from its prior value upon the completion of a non-trigger code SELECT, INSERT, UPDATE, or DELETE statement. %ok is only defined by the execution of trigger code.

  • %msg: Trigger code can explicitly set the %msg variable to a string describing the cause of the runtime error. SQLCODE errors set the %msg variable.

  • %oper: A variable used only in trigger code. Trigger code can refer to the variable %oper, which contains the name of the event that fired the trigger (INSERT, UPDATE, or DELETE).

{fieldname} Syntax

Within trigger code, you can refer to field values (for the fields belonging to the table the trigger is associated with) using a special {fieldname} 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,SQLCODE,%msg,%ok,%oper
        SET id = {ID}

        // INSERT value into Log table
        &sql(INSERT INTO LogTable 
            (TableName, IDValue) 
            VALUES ('MyApp.Person', :id))
        IF SQLCODE<0 {SET baderr="SQLCODE ERROR:"_SQLCODE_" "_%msg
                      SET %ok=0
                    RETURN baderr }

      }
   // ... Definitions of other class members

}

This {fieldname} syntax supports unitary fields. It does not support %SerialObject collection properties. For example, if a table references the embedded serial object class Address, which contains the property City, the trigger syntax {Address_City} is a valid reference to a field. The trigger syntax {Address} is a reference to a collection property, and cannot be used.

Macros within Trigger Code

Your trigger code can contain a macro definition that references a field name (using {fieldname} syntax). However, if your trigger code contains a #include preprocessor directive for a macro that references a field name (using {fieldname} syntax), the field name cannot be accessed. This is because Caché translates {fieldname} references in the trigger code before the code is passed to the macro preprocessor. If a {fieldname} 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 {fieldname} 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 {fieldname} 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).

Additional Trigger Code Syntax

Trigger code written in ObjectScript can contain the pseudo-field reference variables {%%CLASSNAME}, {%%CLASSNAMEQ}, {%%OPERATION}, {%%TABLENAME}, and {%%ID}. These pseudo-fields are translated into a specific value at class compilation time. For further details, refer to CREATE TRIGGER in the InterSystems SQL Reference.

You can use class methods from within trigger code, SQL computed code, and SQL map definitions since class methods do not depend on having an open object. You must use the ##class(classname).Methodname() syntax to invoke a method from within trigger code. You cannot use the ..Methodname() syntax, because this syntax requires a current open object.

You can pass the value of a field of the current row as an argument of the class method, but the class method itself cannot use field syntax.

Pulling Triggers

A defined trigger is “pulled” (executed) if the corresponding DML command for that table is invoked.

A Row or Row/Object trigger is pulled for each row that the DML command successfully inserts, updates, or deletes.

A Statement trigger is pulled once for each INSERT, UPDATE, or DELETE statement that successfully executes, regardless of whether the statement actually changes any rows of the table data.

  • An INSERT statement pulls the corresponding INSERT trigger. To prevent pulling of this corresponding trigger, specify the %NOTRIGGER keyword.

  • An UPDATE statement pulls the corresponding UPDATE trigger. To prevent pulling of this corresponding trigger, specify the %NOTRIGGER keyword.

  • An INSERT OR UPDATE statement pulls the corresponding INSERT trigger or UPDATE trigger, depending on the type of DDL operation performed. To prevent pulling of either type of trigger, specify the %NOTRIGGER keyword.

  • A DELETE statement pulls the corresponding DELETE trigger. To prevent pulling of this corresponding trigger, specify the %NOTRIGGER keyword.

  • A TRUNCATE TABLE statement does not pull a DELETE trigger.

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:

Event Time Trigger is also called at this time
INSERT BEFORE Just before %Save() for a new object
INSERT AFTER Just after %Save() for a new object
UPDATE BEFORE Just before %Save() for an existing object
UPDATE AFTER Just after %Save() for an existing object
DELETE BEFORE Just before %DeleteId() for an existing object
DELETE AFTER Just after %DeleteId() for an existing object

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.

Not Pulling Triggers During Object Access

By default, SQL objects are stored using %Storage.Persistent. Caché also supports %CacheSQLStorageOpens in a new tab storage.

When saving or deleting objects in a class that uses %CacheSQLStorage, all statement (Foreach = statement), row (Foreach = row), and row/object (Foreach = row/object) triggers are pulled. A trigger defined with no Foreach trigger keyword is a row trigger. Pulling all triggers is the default behavior.

However, when saving or deleting objects in a class using %CacheSQLStorage, you can specify that only triggers defined as Foreach = row/object should be pulled. Triggers defined as Foreach = statement or Foreach = row are not pulled. This done by specifying the class parameter OBJECTSPULLTRIGGERS = 0. The default is OBJECTSPULLTRIGGERS = 1.

This parameter only applies to classes defined as using %CacheSQLStorage.

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.

Note:

A consequence of triggers using transactions is that if a trigger invokes code that commits a transaction, completion of the trigger fails because the transaction level has already been decremented to 0. This situation can occur when invoking an Ensemble Business Service.

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

Triggers defined for a specified table are listed in the Management Portal SQL interface Catalog Details. This lists basic information for each trigger. To list more detailed information, use INFORMATION.SCHEMA.TRIGGERSOpens in a new tab.

The INFORMATION.SCHEMA.TRIGGERSOpens in a new tab class lists the defined triggers in the current namespace. For each trigger INFORMATION.SCHEMA.TRIGGERSOpens in a new tab lists various properties, including the name of the trigger, the associated schema and table name, the EVENTMANIPULATION property (INSERT, UPDATE, DELETE, INSERT/UPDATE, INSERT/UPDATE/DELETE), the ACTIONTIMING property (BEFORE, AFTER), the CREATED property (trigger creation timestamp), and the ACTIONSTATEMENT property, which is the generated SQL trigger code.

The CREATED property derives the trigger creation timestamp from when the class definition was last modified. Therefore, subsequent use of this class (for example, to define other triggers) may result in unintended updating of the CREATED property value.

You can access this INFORMATION.SCHEMA.TRIGGERSOpens in a new tab information from an SQL query, as shown in the following example:

SELECT TABLE_NAME,TRIGGER_NAME,CREATED,EVENT_MANIPULATION,ACTION_TIMING,ACTION_ORIENTATION,ACTION_STATEMENT 
FROM INFORMATION_SCHEMA.TRIGGERS WHERE TABLE_SCHEMA='Sample'
FeedbackOpens in a new tab