Using Triggers
This topic describes how you can define triggers in InterSystems SQL. Triggers are lines of code that are executed in response to certain SQL events.
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 successful INSERT of data into the MyApp.Person table:
Class MyApp.Person Extends %Persistent [DdlAllowed] { // ... Class Property Definitions Trigger LogEvent [ Event = INSERT, Time = AFTER ] { // Trigger code to log an event } }
Class MyApp.Person Extends %Persistent [DdlAllowed] { // ... Class Property Definitions Trigger LogEvent [ Event = INSERT, Time = AFTER, Language = python ] { // 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. InterSystems IRIS® data platform 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.
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.
InterSystems IRIS does not support triggers that modify the Security.RolesOpens in a new tab and Security.UsersOpens in a new tab tables.
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 using either a class definition or the CREATE TRIGGER command. 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 on this page. You can list the Foreach value for each trigger using the ACTIONORIENTATION property of INFORMATION.SCHEMA.TRIGGERSOpens in a new tab
Python supports only the Foreach = row/object option.
For a full list of trigger keywords, see the Class Definition Reference.
This table shows the available triggers and their corresponding callback methods:
Trigger | Corresponding Callback Method |
---|---|
BEFORE INSERT |
%OnBeforeSave() |
AFTER INSERT |
%OnAfterSave() |
BEFORE UPDATE |
%OnBeforeSave() |
AFTER UPDATE |
%OnAfterSave() |
BEFORE UPDATE OF (specified columns) |
None |
AFTER UPDATE OF (specified columns) |
None |
BEFORE DELETE |
%OnDelete() |
AFTER DELETE |
%OnAfterDelete() |
Triggers and callback methods are similar, but they can execute at different times:
-
Triggers execute either right before or right after modification of stored data.
-
Callback methods execute before or after the %SaveData() or %DeleteData() methods, depending on the type of callback. Therefore, the %OnBeforeSave() method before the object locks or is modified by a referential action.
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.
AFTER Triggers
An AFTER trigger executes after an INSERT, UPDATE, or DELETE event occurs:
-
If SQLCODE=0 (event completed successfully) InterSystems IRIS executes the AFTER trigger.
-
If SQLCODE is a negative number (event failed) InterSystems IRIS does not executes the AFTER trigger.
-
If SQLCODE=100 (no row was found to insert, update, or delete) InterSystems IRIS 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: InterSystems IRIS 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: InterSystems IRIS prevents an AFTER statement trigger from being executed recursively. 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 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.
ObjectScript 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. (InterSystems IRIS converts code written in SQL to ObjectScript in the class definition.) If the trigger is defined in an IDE such as VS Code, 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. With the exception of the system variables %ok, %msg, and %oper (described in the next section), all variables in triggers should therefore 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, InterSystems IRIS 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, InterSystems IRIS 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 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. It is passed by reference to the trigger code to allow the trigger code to update it; therefore, it must not be explicitly declared with a NEW statement within the 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. Similar to the %ok variable, it is passed by reference to the trigger code and must not be explicitly declared with a NEW statement within the trigger code.
-
%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
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 InterSystems IRIS 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 workaround 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 code, refer to the macro, supplying the {fieldname} syntax as an argument:
$$$dtThrowTrigger({%%ID})
{name*O}, {name*N}, and {name*C} Trigger Code Syntax
Three ObjectScript 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, 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:
-
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 InterSystems IRIS 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 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 ObjectScript 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.
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.
Python Trigger Code
Within a trigger block containing embedded Python, you can access a trigger object that contains attributes and methods related to that trigger. You can access these attributes, which are described in more detail in Types of Triggers.
-
trigger.type — Valid values are "row/object" or "row".
-
trigger.operation — Valid values are "BEFORE", "UPDATE", and "DELETE".
-
trigger.time — Valid values are "before" and "after".
The trigger object also provides a getfield() method that enables you to access the names of table fields. You can specify these options:
-
trigger.getfield(fieldName) returns the value of the specified field, fieldName. If the trigger operation changes the field value, then getfield() returns the new value.
-
trigger.getfield(fieldName, new) uses a boolean value, new, to determine whether to return the new or original field value.
-
If new = 1 (default), then getfield() returns the new value resulting from the trigger operation. This option applies to INSERT and UPDATE operations.
-
If new = 0, then getfield() returns the value prior to the trigger operation. This option applies to UPDATE and DELETE operations.
-
This example uses a trigger object to write the results of a trigger operation, the renaming of the Name field, to a log file. Both the Python code and the equivalent ObjectScript code are shown.
Trigger LogRename [ Event = UPDATE, Foreach = row/object, Language = python, Time = AFTER ]
{
with open('C:/temp/log.txt', 'a+') as file:
file.write("Rename Event Occurred")
file.write("\nID: " + str(trigger.getfield("ID")))
file.write("\nOperation: " + trigger.operation)
file.write("\nOld Name: " + trigger.getfield("Name", 0))
file.write("\nNew Name: " + trigger.getfield("Name", 1) + "\n\n")
}
Trigger LogRename [ Event = UPDATE, Foreach = row/object, Time = AFTER ]
{
set file = "C:/temp/log.txt"
open file:("EWA") use file
write "Rename Event Occurred"
write !,"ID: "_{ID}
write !,"Operation: "_{%%OPERATION}
write !,"Old Name: "_{Name*O}
write !,"New Name: "_{Name*N},!!
close file
}
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. An INSERT can prevent pulling of this corresponding trigger by specifying the %NOTRIGGER keyword. An INSERT that specifies the %NOJOURN keyword does not journal the insert or the corresponding INSERT trigger. This means that rollback is not possible for either the insert event or the trigger event.
-
An UPDATE statement pulls the corresponding UPDATE trigger. An UPDATE can prevent pulling of this corresponding trigger by specifying the %NOTRIGGER keyword. An UPDATE that specifies the %NOJOURN keyword does not journal the update or the corresponding UPDATE trigger. This means that rollback is not possible for either the update event or the trigger event.
-
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. A DELETE can prevent pulling of this corresponding trigger by specifying the %NOTRIGGER keyword. A DELETE that specifies the %NOJOURN keyword does not journal the delete or the corresponding DELETE trigger. This means that rollback is not possible for either the delete event or the trigger event.
-
A TRUNCATE TABLE statement does not pull a DELETE trigger.
By default, DDL statements and the corresponding triggered actions are journaled. The %NOJOURN keyword prevents journaling of both the DDL command and the triggered action.
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 the Foreach trigger keyword, see Foreach.
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 that uses %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 using %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. Depending on your automatic transaction setting, transaction rollback might also occur. In SQL, the SET TRANSACTION %COMMITMODE commitmode option controls this setting. In ObjectScript, the "AutoCommit" option of the SetOption()Opens in a new tab method controls this setting, using this syntax: SET status=$SYSTEM.SQL.Util.SetOption("AutoCommit",intval,.oldval). This table shows the effect the automatic transaction setting has on rollback. It also shows the corresponding commitmode option to set for SQL and "AutoCommit" integer value to set for ObjectScript.
Automatic Transaction Setting | %COMMITMODE Option (SQL) | "AutoCommit" Value (ObjectScript) | Transaction Rollback Result |
---|---|---|---|
No automatic transaction processing | NONE | 0 | No transaction was started, so the INSERT cannot be rolled back. |
Automatic transaction commitment is on (default) | IMPLICIT | 1 | The transaction for the INSERT is rolled back. |
Automatic transaction commitment is off | EXPLICIT | 2 | It is up to the application to either rollback or commit the transaction for the INSERT. |
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 System Variables.
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'