CREATE TRIGGER (SQL)
Synopsis
CREATE [OR REPLACE] TRIGGER trigname {BEFORE | AFTER} event [,event]
[ORDER integer] ON table
[REFERENCING {OLD | NEW} [ROW] [AS] alias] action
Arguments
Argument | Description |
---|---|
trigname | The name of the trigger to be created, which is an identifier. A trigger name may be qualified or unqualified; if qualified, its schema name must match the table’s schema name. |
BEFORE event AFTER event |
The time (BEFORE or AFTER) the event to execute the trigger. The trigger event, or a comma-separated list of trigger events. Available event list options are INSERT, DELETE, and UPDATE. You can specify a single UPDATE OF event. The UPDATE OF clause is followed by a column name or a comma-separated list of column names. The UPDATE OF clause can only be specified when LANGUAGE is SQL. The UPDATE OF clause cannot be specified in a comma-separated event list. |
ORDER integer | Optional — The order in which triggers should be executed when there are multiple triggers for a table with the same time and event. If order is omitted, a trigger is assigned an order of 0. |
ON table | The table the trigger is created for. A table name may be qualified or unqualified; if qualified, the trigger must reside in the same schema as the table. |
REFERENCING OLD ROW AS alias REFERENCING NEW ROW AS alias |
Optional — A REFERENCING clause can only be used when LANGUAGE is SQL. A REFERENCING clause allows you to specify an alias that you can use to reference a column. REFERENCING OLD ROW allows you reference the old value of a column during an UPDATE or DELETE trigger. REFERENCING NEW ROW allows you to reference the new value of a column during an INSERT or UPDATE trigger. The ROW AS keywords are optional. For an UPDATE, you can specify both OLD and NEW in the same REFERENCING clause, as follows: REFERENCING OLD oldalias NEW newalias. |
action | The program code for the trigger. The action argument can contain various optional keyword clauses, including (in order): a FOR EACH clause; a WHEN clause with a predicate condition governing execution of the triggered action; and a LANGUAGE clause which specifies either LANGUAGE SQL or LANGUAGE OBJECTSCRIPT. If the LANGUAGE clause is omitted, SQL is the default. Following these clauses, you specify one or more lines of either SQL trigger code or ObjectScript trigger code specifying the action to perform when the trigger is executed. |
Description
The CREATE TRIGGER command defines a trigger, a block of code to be executed when data in a specific table is modified. A trigger is executed (“fired” or “pulled”) when a specific triggering event occurs, such as a new row being inserted into a specified table. A trigger executes user-specified trigger code. You can specify that the trigger should execute this code before or after the execution of the triggering event. A trigger is specific to a specified table.
-
A trigger is fired by a specified event: an INSERT, DELETE, or UPDATE operation. You can specify a comma-separated list of events to execute the trigger when any one of the specified events occurs on the specified table.
-
A trigger is fired by an event either (potentially) multiple times or just once. A row-level trigger is fired once for each row modified. A statement-level trigger is fired once for an event. This trigger type is specified using the FOR EACH clause. A row-level trigger is the default trigger type.
-
Commonly, firing a trigger code performs an operation on another table or file, such as performing a logging operation or displaying a message. Firing a trigger cannot modify data in the triggering record. For example, if an update to Record 7 fires a trigger, that trigger’s code block cannot update or delete Record 7. A trigger can modify the same table that invoked the trigger, but the triggering event and the trigger code operation must be different to prevent a recursive trigger infinite loop.
The optional keyword OR REPLACE allows you to modify or replace an existing trigger. CREATE OR REPLACE TRIGGER has the same effect as invoking DROP TRIGGER to delete the old version of the trigger and then invoking CREATE TRIGGER. The command DROP TABLE drops all triggers associated with that table.
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. */
}
Arguments
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
If the trigger name is unqualified, the trigger schema name defaults to the same schema as the specified table schema. If the table name is unqualified, the table schema name defaults to the same schema as the specified trigger schema. If both are unqualified, the default schema name is used; schema search paths are not used. If both are qualified, the trigger schema name must be the same as the table schema name. A schema name mismatch results in an SQLCODE -366 error; this should only occur when both the trigger name and the table name are qualified and they specify different schema names.
Trigger names follow identifier conventions, subject to the restrictions below. By default, trigger names are simple identifiers. A trigger name should not exceed 128 characters. Trigger names are not case-sensitive.
InterSystems IRIS uses trigname to generate a corresponding trigger name in the InterSystems IRIS class. The corresponding class trigger name contains only alphanumeric characters (letters and numbers) and is a maximum of 96 characters in length. To generate this identifier name, InterSystems IRIS first strips punctuation characters from the trigger name, and then generates a unique identifier of 96 (or less) characters, substituting a number for the 96th character when needed to create a unique name. This name generation imposes the following restrictions on the naming of triggers:
-
A trigger name must include at least one letter. Either the first character of the trigger name or the first character after initial punctuation characters must be a letter.
-
InterSystems IRIS supports 16-bit (wide) characters for trigger names. A character is a valid letter if it passes the $ZNAME test.
-
Because names generated for an InterSystems IRIS class do not include punctuation characters, it is not advisable (though possible) to create trigger names that differ only in their punctuation characters.
-
A trigger name may be much longer than 96 characters, but trigger names that differ in their first 96 alphanumeric characters are much easier to work with.
Issuing a CREATE TRIGGER with the name of an existing trigger issues an SQLCODE -365 “Trigger name not unique” error. Use the optional OR REPLACE keyword or drop the old trigger first with DROP TRIGGER.
If two triggers referencing different tables in a schema have the same name, a DROP TRIGGER may issue an SQLCODE -365 “Trigger name not unique” error with the message “Trigger 'MyTrigName' found in 2 classes”
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.
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.
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.
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},!}