CREATE TRIGGER (SQL)

Privileges and Locking

The CREATE TRIGGER command is a privileged operation. The user must have %CREATE_TRIGGER administrative privilege to execute CREATE TRIGGER. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have %CREATE_TRIGGER privileges.

The user must have %ALTER privilege on the specified table. If the user is the Owner (creator) of the table, the user is automatically granted %ALTER privilege for that table. Otherwise, the user must be granted %ALTER privilege for the table. Failing to do so results in an SQLCODE –99 error with the %msg User 'name' does not have required %ALTER privilege needed to create a trigger on table: 'Schema.TableName'.

You can use the GRANT command to assign %CREATE_TRIGGER and %ALTER privileges, if you hold appropriate granting privileges.

In embedded SQL, you can use the $SYSTEM.Security.Login()Opens in a new tab method to log in as a user with appropriate privileges:

   DO $SYSTEM.Security.Login("_SYSTEM","SYS")
   &sql(      )

You must have the %Service_Login:Use privilege to invoke the $SYSTEM.Security.Login method. For further information, refer to %SYSTEM.SecurityOpens in a new tab in the InterSystems Class Reference.

• CREATE TRIGGER cannot be used on a table projected from a persistent class, unless the table class definition includes [DdlAllowed]. Otherwise, the operation fails with an SQLCODE -300 error with the %msg DDL not enabled for class 'Schema.tablename'.

• CREATE TRIGGER cannot be used on a table projected from a deployed persistent class. This operation fails with an SQLCODE -400 error with the %msg Unable to execute DDL that modifies a deployed class: 'classname'.

The CREATE TRIGGER statement acquires a table-level lock on table. This prevents other processes from modifying the table’s data. This lock is automatically released at the conclusion of the CREATE TRIGGER operation.

To create a trigger, the table cannot be locked by another process in either EXCLUSIVE MODE or SHARE MODE. Attempting a CREATE TRIGGER operation on a locked table results in an SQLCODE -110 error, with a %msg such as the following: Unable to acquire exclusive table lock for table 'Sample.MyTest'.

Other Ways of Defining Triggers

You can define an SQL trigger as a class object as described in Trigger Definitions. The following is an example of an Object trigger:

Trigger SQLJournal [ CodeMode = objectgenerator, Event = INSERT/UPDATE, ForEach = ROW/OBJECT, Time = AFTER ]
{  /* ObjectScript trigger code
      that updates a journal file
      after a row is inserted or updated. */
}

trigname

A trigger name follows the same identifier requirements as a table name, but not the same uniqueness requirements. A trigger name should be unique for all tables within a schema. Thus, triggers referencing different tables in a schema should not have the same name. Violating this uniqueness requirement can result in a DROP TRIGGER error.

A trigger and its associated table must reside in the same schema. You cannot use the same name for a trigger and a table in the same schema. Violating trigger naming conventions results in an SQLCODE -400 error at CREATE TRIGGER execution time.

A trigger name may be unqualified or qualified. A qualified trigger name has the form:

schema.trigger

event

The time that the trigger is fired is specified by the BEFORE or AFTER keyword; these keywords specify that the trigger operation should occur either before or after InterSystems IRIS executes the triggering event. A BEFORE trigger is executed before performing the specified event, but after verifying the event. For example, InterSystems IRIS only executes a BEFORE DELETE trigger if the DELETE statement is valid for the specified row(s), and the process has the necessary privileges to perform the DELETE, including any foreign key referential integrity checks. If the process cannot perform the specified event, InterSystems IRIS issues an error code for the event; it does not execute the BEFORE trigger.

The BEFORE or AFTER keyword is followed by the name of a triggering event, or a comma-separated list of triggering events. A trigger specified as INSERT is executed when a row is inserted into the specified table. A trigger specified as DELETE is executed when a row is deleted from the specified table. A trigger specified as UPDATE is executed when a row is updated in the specified table. You can specify a single trigger event or a comma-separated list of INSERT, UPDATE, or DELETE trigger events in any order.

A trigger specified as UPDATE OF is executed only when one or more of the specified columns is updated in a row in the specified table. Column names are specified as a comma-separated list. Column names can be specified in any order. An UPDATE OF trigger has the following restrictions:

• UPDATE OF is only valid if the trigger code language is SQL (the default); an SQLCODE -50 error is issued if the trigger code language is OBJECTSCRIPT.

• UPDATE OF cannot be combined with other triggering events; an SQLCODE -1 error is issued if you specify UPDATE OF in a comma-separated list of triggering events.

• UPDATE OF cannot specify a non-existent field; an SQLCODE -400 error is issued.

• UPDATE OF cannot specify a duplicate field name; an SQLCODE -58 error is issued.

The following are examples of event types:

CREATE TRIGGER TrigBI BEFORE INSERT ON Sample.Person
       INSERT INTO TLog (Text) VALUES ('before insert')

CREATE TRIGGER TrigAU AFTER UPDATE ON Sample.Person
       INSERT INTO TLog (Text) VALUES ('after update')

CREATE TRIGGER TrigBUOF BEFORE UPDATE OF Home_Street,Home_City,Home_State ON Sample.Person
       INSERT INTO TLog (Text) VALUES ('before address update')

CREATE TRIGGER TrigAD AFTER UPDATE,DELETE ON Sample.Person
       INSERT INTO TLog (Text) VALUES ('after update or delete')

ORDER

The ORDER clause determines the order in which triggers are executed when there are multiple triggers for the same table with the same time and event. For example, two AFTER DELETE triggers. The trigger with the lowest ORDER integer is executed first, then the next higher integer, and so on. If the ORDER clause is not specified, a trigger is created with an assigned ORDER number of 0 (zero). Thus, triggers with no ORDER clause are always executed before triggers with ORDER clauses.

You can assign the same order value to multiple triggers. You can also create multiple triggers with an (implicit or explicit) order of 0. Multiple triggers with the same time, event, and order are executed together in random order.

Triggers are executed in the sequence: time > order > event. Thus if you have a BEFORE INSERT trigger and a BEFORE INSERT,UPDATE trigger, the trigger with the lowest ORDER value would be executed first. If you have a BEFORE INSERT trigger and a BEFORE INSERT,UPDATE trigger with the same ORDER value, the INSERT is executed before the INSERT,UPDATE. This is because — time and order being the same — a single-event trigger is always executed before a multi-event trigger. If two (or more) triggers have identical time, order, and event values, the order of execution is random.

The following examples show how ORDER numbers work. All of these CREATE TRIGGER statements create triggers that are executed by the same event:

CREATE TRIGGER TrigA BEFORE DELETE ON doctable
       INSERT INTO TLog (Text) VALUES ('doc deleted')
  /* Assigned ORDER=0 */

CREATE TRIGGER TrigB BEFORE DELETE ORDER 4 ON doctable
       INSERT INTO TReport (Text) VALUES ('doc deleted')
  /* Specified as ORDER=4 */

CREATE TRIGGER TrigC BEFORE DELETE ORDER 2 ON doctable
       INSERT INTO Ttemps (Text) VALUES ('doc deleted')
  /* Specified as ORDER=2 */

CREATE TRIGGER TrigD BEFORE DELETE ON doctable
       INSERT INTO Tflags (Text) VALUES ('doc deleted')
  /* Also assigned ORDER=0 */

These triggers will execute in the sequence: (TrigA, TrigD), TrigC, TrigB. Note that TrigA and TrigD have the same order number, and thus execute in random sequence.

REFERENCING

The REFERENCING clause can specify an alias for the old value of a row, the new value of a row, or both. The old value is the row value before the triggered action of an UPDATE or DELETE trigger. The new value is the row value after the triggered action of an UPDATE or INSERT trigger. For an UPDATE trigger, you can specify aliases for both the before and after row values, as follows:

REFERENCING OLD ROW AS oldalias NEW ROW AS newalias

The keywords ROW and AS are optional. Therefore, the same clause can also be specified as:

REFERENCING OLD oldalias NEW newalias

It is not meaningful to refer to an OLD value before an INSERT or a NEW value after a DELETE. Attempting to do so results in an SQLCODE -48 error at compile time.

A REFERENCING clause can only be used when the action program code is SQL. Specifying a REFERENCING clause with the LANGUAGE OBJECTSCRIPT clause results in an SQLCODE -49 error.

The following is an example of using REFERENCING with an INSERT:

CREATE TRIGGER TrigA AFTER INSERT ON doctable
      REFERENCING NEW ROW AS new_row
BEGIN
      INSERT INTO Log_Table VALUES ('INSERT into doctable');
      INSERT INTO New_Log_Table VALUES ('INSERT into doctable',new_row.ID);
END

action

A triggered action consists of the following elements:

• An optional FOR EACH clause. The available values are FOR EACH ROW, FOR EACH ROW_AND_OBJECT, and FOR EACH STATEMENT. The default is FOR EACH ROW:

  • FOR EACH ROW — This trigger is fired by each row affected by the triggering statement. Note that row-level triggers are not supported for TSQL.

  • FOR EACH ROW_AND_OBJECT — This trigger is fired by each row affected by the triggering statement or by changes via object access. Note that row-level triggers are not supported for TSQL.

    This option defines a unified trigger, so called because it is fired by data changes that occur via SQL or object access. (In contrast, with other triggers, if you want to use the same logic when changes occur via object access, it is necessary to implement callbacks such as %OnDelete().)

  • FOR EACH STATEMENT — This trigger is fired once for the whole statement. Statement-level triggers are supported for both ObjectScript and TSQL triggers.

  For the corresponding trigger class options, see FOREACH.

  You can list the FOR EACH value for each trigger using the ACTIONORIENTATION property of INFORMATION.SCHEMA.TRIGGERSOpens in a new tab.

• An optional WHEN clause, consisting of the WHEN keyword followed by a predicate condition (simple or complex) enclosed in parentheses. If the predicate condition evaluates to TRUE, the trigger is executed. A WHEN clause can only be used when LANGUAGE is SQL. The WHEN clause can reference oldalias or newalias values. For further details on predicate condition expressions and a list of available predicates, refer to the Overview of Predicates page in this document.

• An optional LANGUAGE clause, either LANGUAGE SQL or LANGUAGE OBJECTSCRIPT. The default is LANGUAGE SQL.

• User-written code that is executed when the trigger is executed.

Field References and Pseudo-field References

Trigger code written in ObjectScript can contain field references, specified as {fieldname}, where fieldname specifies an existing field in the current table. No blank spaces are permitted within the curly braces.

You can follow the fieldname with *N (new), *O (old), or *C (compare) to specify how to handle an inserted, updated, or deleted field data value, as follows:

• {fieldname*N}

  • For UPDATE, returns the new field value after the specified change is made.

  • For INSERT, returns the value inserted.

  • For DELETE, returns the value of the field before the delete.

• {fieldname*O}

  • For UPDATE, returns the old field value before the specified change is made.

  • For INSERT, returns NULL.

  • For DELETE, returns the value of the field before the delete.

• {fieldname*C}

  • For UPDATE, returns 1 (TRUE) if the new value differs from the old value, otherwise returns 0 (FALSE).

  • For INSERT, returns 1 (TRUE) if the inserted value is non-NULL, otherwise returns 0 (FALSE).

  • For DELETE, returns 1 (TRUE) if the value being deleted is non-NULL, otherwise returns 0 (FALSE).

For UPDATE, INSERT, or DELETE, {fieldname} returns the same value as {fieldname*N}.

For example, the following trigger returns the Name field value for a new row inserted into Sample.Employee. (You can perform the INSERT from the SQL Shell to view this result):

CREATE TRIGGER InsertNameTrig AFTER INSERT ON Sample.Employee
   LANGUAGE OBJECTSCRIPT
   {WRITE "The employee ",{Name*N}," was ",{%%OPERATION},"ed on ",{%%TABLENAME},!}

Line returns are not permitted within a statement that sets a field value. For further details, refer to the SqlComputeCode property keyword in the Class Definition Reference.

You can use the GetAllColumns()Opens in a new tab method to list the field names defined for a table. For further details, refer to Column Names and Numbers in the “Defining Tables” chapter of Using InterSystems SQL.

Trigger code written in ObjectScript can also contain the pseudo-field reference variables {%%CLASSNAME}, {%%CLASSNAMEQ}, {%%OPERATION}, {%%TABLENAME}, and {%%ID}. The pseudo-fields are translated into a specific value at class compilation time. All of these pseudo-field keywords are not case-sensitive.

• {%%CLASSNAME} and {%%CLASSNAMEQ} both translate to the name of the class which projected the SQL table definition. {%%CLASSNAME} returns an unquoted string and {%%CLASSNAMEQ} returns a quoted string.

• {%%OPERATION} translates to a string literal, either INSERT, UPDATE, or DELETE, depending on the operation that invoked the trigger.

• {%%TABLENAME} translates to the fully qualified name of the table.

• {%%ID} translates to the RowID name. This reference is useful when you do not know the name of the RowID field.

Referencing Stream Property

When a Stream field/property is referenced in a trigger definition, like {StreamField}, {StreamField*O}, or {StreamField*N}, the value of the {StreamField} reference is the stream's OID (object ID) value.

For a BEFORE INSERT or BEFORE UPDATE trigger, if a new value is specified by the INSERT/UPDATE/ObjectSave, the {StreamField*N} value will be either the OID of the temporary stream object, or the new literal stream value. For a BEFORE UPDATE trigger, if a new value is not specified for the stream field/property, {StreamField*O} and {StreamField*N} will both be the OID of the current field/property stream object.

Referencing SQLComputed Property

When a transient SqlComputed field/property (either "Calculated" or explicitly "Transient") is referenced in a trigger definition, Get()/Set() method overrides are not recognized by the trigger. Use SQLCOMPUTED/SQLCOMPUTONCHANGE, rather than overriding the property's Get() or Set() method.

Using Get()/Set() method overrides can result in the following erroneous result: The {property*O} value is determined using SQL and does not use the overridden Get()/Set() methods. Because the property is not stored on disk, {property*O} uses the SqlComputeCode to "recreate" the old value. However, {property*N} uses the overridden Get()/Set() methods to access the property's value. As a result, there is a possibility for {property*O} and {property*N} to be different (and thus {property*C}=1) even though the property did not actually change.

Labels

Trigger code may contain line labels (tags). To specify a label in trigger code, prefix the label line with a colon to indicate that this line should begin in the first column. InterSystems IRIS strips out the colon and treats the remaining line as a label. However, because trigger code is generated outside the scope of any procedure blocks, every label must be unique throughout the class definition. Any other code compiled into the class's routine must not have the same label defined, including in other triggers, in non-procedure block methods, SqlComputeCode, and other code.

Method Calls

You can call class methods from within trigger code, because class methods do not depend on having an open object. You must use the ##class(classname).Method() syntax to invoke a method. You cannot use the ..Method() 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.