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.
SQL Trigger Code
If LANGUAGE SQL (the default), the triggered statement is an
SQL procedure block, consisting of either one SQL procedure statement
followed by a semicolon, or the keyword BEGIN followed by one or more
SQL procedure statements, each followed by a semicolon, concluding
with an END keyword.
A triggered action is atomic, it is either fully applied or
not at all, and cannot contain COMMIT or ROLLBACK statements. The keyword BEGIN ATOMIC is synonymous
with the keyword BEGIN.
If LANGUAGE SQL, the CREATE TRIGGER statement
can optionally contain a REFERENCING clause, a WHEN clause, and/or
an UPDATE OF clause. An UPDATE OF clause specifies that the trigger
should only be executed when an UPDATE is performed
on one or more of the columns specified for this trigger. A CREATE TRIGGER statement with LANGUAGE OBJECTSCRIPT cannot
contain these clauses.
SQL trigger code is executed as embedded SQL. This means that
InterSystems IRIS converts SQL trigger code to ObjectScript; therefore,
if you view the class definition corresponding to your SQL trigger
code, you will see Language=objectscript in the
trigger definition.
When executing SQL trigger code, the system automatically resets
(NEWs) all variable used in the trigger code. After the execution
of each SQL statement, InterSystems IRIS checks SQLCODE. If an error
occurs, InterSystems IRIS sets the %ok variable to 0, aborting and
rolling back both the trigger code operation(s) and the associated INSERT, UPDATE, or DELETE.
ObjectScript Trigger Code
If LANGUAGE OBJECTSCRIPT, the CREATE TRIGGER statement cannot contain a REFERENCING clause, a WHEN clause, or
an UPDATE OF clause. Specifying these SQL-only clauses with LANGUAGE
OBJECTSCRIPT results in compile-time SQLCODE errors -49, -57, or -50,
respectively.
If LANGUAGE OBJECTSCRIPT, the triggered statement is a block
of one or more ObjectScript statements, enclosed by curly braces.
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.
If trigger code contains Macro Preprocessor
statements (# commands, ## functions, or $$$macro references),
these statements are compiled before the CREATE TRIGGER DDL code itself.
ObjectScript trigger code can contain Embedded SQL.
You can issue an error from trigger code by setting the %ok variable to 0. This creates a runtime error that aborts
and rolls back execution of the trigger. It generates the appropriate
SQLCODE error (for example, SQLCODE -131 “After insert trigger
failed”) and returns the user-specified value of the %msg variable as a string to describe the cause of the
trigger code error. Note that setting %ok to a non-numeric value sets
%ok=0.
The system generates trigger code only once, even for a multiple-event
trigger.
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.
Note:
This use of a colon prefix for a label takes precedence over
the use of a colon prefix for a host
variable reference. To avoid this conflict, it is recommended
that embedded SQL trigger code lines never begin with a host variable
reference. If you must begin a trigger code line with a host variable
reference, you can designate it as a host variable (and not a label)
by doubling the colon prefix.
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.
Listing Existing Triggers
You can use the INFORMATION.SCHEMA.TRIGGERSOpens in a new tab 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
the EVENT_MANIPULATION property (INSERT, UPDATE, DELETE, INSERT/UPDATE,
INSERT/UPDATE/DELETE) and ACTION_TIMING property (BEFORE, AFTER).
It also lists the ACTION_STATEMENT, which is the generated SQL trigger
code.
Trigger Runtime Errors
A trigger and its invoking event execute as an atomic operation
on a single row basis. That is:
-
A failed BEFORE trigger is rolled back, the associated INSERT, UPDATE, or DELETE operation is not executed, and all locks on the row are released.
-
A failed AFTER trigger is rolled back, the associated INSERT, UPDATE, or DELETE operation is rolled back, and all locks on the row are released.
-
A failed INSERT, UPDATE, or DELETE operation is rolled back, the associated
BEFORE trigger is rolled back, and all locks on the row are released.
-
A failed INSERT, UPDATE, or DELETE operation is rolled back, the associated
AFTER trigger is not executed, and all locks on the row are released.
Note that integrity is maintained for the current row operation
only. Your application program must handle data integrity issues involving
operation on multiple rows by using transaction processing statements.
Because a trigger is an atomic operation, you cannot code transaction
statements, such as commits and rollbacks, within trigger code.
If an INSERT, UPDATE,
or DELETE operation causes multiple triggers to
execute, the failure of one trigger causes all remaining triggers
to remain unexecuted.
-
SQLCODE -415: If there is an error in the trigger
code (for example, a reference to a non-existent table or an undefined
variable) execution of the trigger code fails at runtime and InterSystems
IRIS issues an SQLCODE -415 error “Fatal error occurred within
the SQL filer”.
-
SQLCODE -130 through -135: When a trigger operation
fails, InterSystems IRIS issues one of the SQLCODE error codes -130
through -135 at runtime indicating the type of trigger that failed.
You can force a trigger to fail by setting the %ok variable to 0 in the trigger code. This issues the appropriate SQLCODE
error (for example, SQLCODE -131 “After insert trigger failed”)
and returns the user-specified value of the %msg variable as a string to describe the cause of the trigger code error.
Examples
The following example demonstrates CREATE TRIGGER with an ObjectScript DELETE trigger. It assumes that there is a
data table (TestDummy) that contains records. It creates a log table
(TestDummyLog) and a DELETE trigger that writes to the log table when
a delete is performed on the data table. The trigger inserts the name
of the data table, the RowId of the deleted row, the current date,
and the type of operation performed (the %oper special variable), in this case “DELETE”:
CREATE TABLE TestDummyLog
(TableName VARCHAR(40),
IDVal INTEGER,
LogDate DATE,
Operation VARCHAR(40))
&sql(CREATE TRIGGER TrigTestDummy AFTER DELETE ON TestDummy
LANGUAGE OBJECTSCRIPT {
NEW id
SET id = {ID}
&sql(INSERT INTO TestDummyLog (TableName,IDVal,LogDate,Operation)
VALUES ('TestDummy',:id,+$HOROLOG,:%oper))
}
)
WRITE !,"SQL trigger code is: ",SQLCODE
The following examples demonstrate CREATE TRIGGER with an SQL
INSERT trigger. The first program creates a table, an INSERT trigger
for that table, and a log table for the trigger's use. The second
program issues an INSERT against the table, which invokes the trigger,
which logs an entry in the log table. After displaying the log entry,
the program drops both tables so that this program can be run repeatedly:
CREATE TABLE TestDummy (
testnum INT NOT NULL,
firstword CHAR (30) NOT NULL,
lastword CHAR (30) NOT NULL,
CONSTRAINT TestDummyPK PRIMARY KEY (testnum))
CREATE TABLE TestDummyLog (
entry CHAR (60) NOT NULL)
)
CREATE TRIGGER TrigTestDummy AFTER INSERT ON TestDummy
BEGIN
INSERT INTO TestDummyLog (entry) VALUES
(CURRENT_TIMESTAMP||' INSERT to TestDummy');
END
INSERT INTO TestDummy (testnum,firstword,lastword) VALUES
(46639,'hello','goodbye'))
SELECT entry FROM TestDummyLog
DROP TABLE TestDummy
DROP TABLE TestDummyLog
The following example includes a WHEN clause that specifies
that the action should only be performed when the
predicate condition in parentheses is met:
CREATE TRIGGER Trigger_2 AFTER INSERT ON Table_1
WHEN (f1 %STARTSWITH 'A')
BEGIN
INSERT INTO Log_Table VALUES (new_row.Category);
END
The following example defines a trigger that returns the old
Name field value and the new Name field value after a row is inserted,
updated, or deleted in Sample.Employee. (You can perform the triggering
event operation from the SQL Shell to view this result):
CREATE TRIGGER EmployNameTrig AFTER INSERT,UPDATE,DELETE ON Sample.Employee
LANGUAGE OBJECTSCRIPT
{WRITE "Employee old name:",{Name*O}," new name:",{Name*N}," ",{%%OPERATION}," on ",{%%TABLENAME},!}
See Also
