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

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 type of event that causes it to execute (the trigger Event keyword), and the time that the trigger executes: before or after the event occurs (the trigger Time keyword). If more than one trigger exists for the same event and time, you can define the order in which these triggers execute (the trigger Order keyword).
The trigger Foreach keyword provides additional granularity. This keyword controls whether the trigger is fired once per row, once per row or object access, or once per statement. For details, see the Caché Class Definition Reference. 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.
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 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 following are the available triggers:
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 trigger Order 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.
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 Caché 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 
    // ... 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.
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
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.