InterSystems IRIS Data Platform 2019.2  /  Using InterSystems SQL

Using InterSystems SQL
Using Triggers
Previous section           Next section
InterSystems: The power behind what matters   

This chapter describes how you can define triggers in InterSystems 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:
The maximum number of user-defined triggers for a class is 200.
InterSystems IRIS 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 InterSystems IRIS 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
InterSystems IRIS 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. InterSystems IRIS 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.
InterSystems IRIS 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. (InterSystems IRIS 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, InterSystems IRIS 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 InterSystems IRIS 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, InterSystems IRIS 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 InterSystems IRIS 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 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.
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 Class Definition Reference.
Not Pulling Triggers During Object Access
By default, SQL objects are stored using %Storage.Persistent. InterSystems IRIS also supports %Storage.SQL storage.
When saving or deleting objects in a class using %Storage.SQL storage, 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 that uses %Storage.SQL, 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 %Storage.SQL.
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 a Business Service of a Production.
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.

Previous section           Next section
Send us comments on this page
View this book as PDF   |  Download all PDFs
Copyright © 1997-2019 InterSystems Corporation, Cambridge, MA
Content Date/Time: 2019-09-20 05:47:55