CREATE TRIGGER

Privileges and Locking

The CREATE TRIGGER command is a privileged operation. Before using CREATE TRIGGER it is necessary for your process to have %CREATE_TRIGGER privileges. Failing to do so results in an SQLCODE -99 error (Privilege Violation). You can use the GRANT command to assign %CREATE_TRIGGER 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 created by defining 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'.

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.

Other Ways of Defining Triggers

You can define an SQL trigger as a Caché Object by including the FOREACH = ROW/OBJECT statement. 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 must be unique for a table within a schema. Thus, triggers referencing different tables in a schema may have the same name. 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

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.

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, but duplicate column names are not permitted; this results in an SQLCODE -58 error at compile time. The UPDATE OF clause is only valid if the trigger code LANGUAGE is SQL (the default).

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 Caché executes the triggering event. A BEFORE trigger is executed before performing the specified event, but after verifying the event. For example, Caché 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, Caché issues an error code for the event; it does not execute the BEFORE trigger.

The following are examples of four of the eight possible event types:

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

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

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

CREATE TRIGGER TrigAD AFTER DELETE ON Sample.Person
       INSERT INTO TLog VALUES ('after 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 VALUES ('doc deleted');
  -- Assigned ORDER=0

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

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

CREATE TRIGGER TrigD BEFORE DELETE ON doctable
       INSERT INTO Tflags 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

REFERENCING OLD oldalias NEW newalias

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. FOR EACH ROW means that this trigger is invoked by an SQL filing operation. FOR EACH ROW_AND_OBJECT means that this trigger is invoked by either an SQL filing operation or a Caché Objects filing operation. FOR EACH STATEMENT is provided for compatibility with TSQL triggers.

• 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}.

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

You can use the GetColumns()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 Caché 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, returned as a quoted string.

• {%%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. Caché 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.