Using Caché SQL
Using Triggers
[Home] [Back] [Next]
InterSystems: The power behind what matters   

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:
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.
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:
For a full list of trigger keyword, see the Class Definition Reference.
The following are the available triggers and their equivalent callback methods:
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:
Recursive Triggers
Caché prevents an AFTER trigger from being executed recursively. 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. 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 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.
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.
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.
You can issue an error from trigger code by setting the %ok variable to 0. This creates a runtime error that aborts execution of the trigger. Trigger code can also set the %msg variable to a string describing the cause of the runtime error.
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:
{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:
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:
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:
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:
If a stream property is updated using Caché objects, the {Stream*N} value is always an oid.
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 Caché 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.
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. InterSystems IRIS also supports %CacheSQLStorage 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.
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:
The AUTO_COMMIT mode is established using the SET TRANSACTION %COMMITMODE option, or the $SYSTEM.SQL.SetAutoCommit() 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.
You can use the INFORMATION.SCHEMA.TRIGGERS 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 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.

Send us comments on this page
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA