Creates a trigger.
Synopsis
CREATE TRIGGER name BEFORE event ORDER integer ON table REFERENCING alias action
CREATE TRIGGER name AFTER event ORDER integer ON table REFERENCING alias action
The
CREATE TRIGGER command creates a trigger. A trigger is
pulled (executed) when a triggering event occurs, such as a new row being inserted
into a specified table. Pulling a trigger executes the user-specified trigger code
in conjunction with the
INSERT,
DELETE, or
UPDATE command
that invoked it. You can specify trigger code execution before or after the execution
of the invoking action. A trigger is specific to a specified table and a specified
INSERT,
DELETE,
or
UPDATE operation.
In order to create a trigger, you must have %CREATE_TRIGGER administrative privilege,
as specified by the
GRANT command.
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 a SQLCODE -400 error at
CREATE TRIGGER execution
time.
A trigger name may be unqualified or qualified. A qualified trigger name has
the form:
If the trigger name is unqualified, the trigger schema name defaults to the
same schema as the
table. If both are unqualified, the system default
schema is assumed. If the trigger name is qualified, the trigger schema name must
be the same as the table schema name. If no table schema name is specified, the trigger
schema name is assumed. A schema name mismatch results in a SQLCODE -366; 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.
Caché uses the trigger name (SQLNAME) to generate a corresponding Caché
identifier. A Caché identifier contains only alphanumeric characters (letters
and numbers) and is a maximum of 25 characters in length. To generate this Caché
identifier name, Caché first strips punctuation characters from the trigger
name, and then generates a unique identifier of 25 (or less) characters, substituting
a number for the 25th 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.
-
Caché supports 16-bit (wide) characters for trigger names on
Unicode systems. A character is a valid letter if it passes the
$ZNAME test.
-
Because generated Caché names 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 25 characters, but trigger
names that differ in their first 25 alphanumeric characters are much easier to work
with.
A trigger specified as INSERT is pulled (executed) when a row is inserted into
the specified table. A trigger specified as DELETE is pulled when a row is deleted
from the specified table. A trigger specified as UPDATE is pulled when a row is updated
in the specified table.
A trigger specified as UPDATE OF is pulled 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).
A BEFORE trigger is pulled (executed) before performing the specified
event,
but
after verifying the
event. For example,
Caché only pulls 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 pull the BEFORE
trigger.
The ORDER clause determines the order in which triggers are pulled 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, etc. 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.
The following examples show how ORDER numbers work. All of these CREATE TRIGGER
statements create triggers that are pulled 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.
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 prior to 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 prior to an INSERT or a NEW value
after a DELETE. Attempting to do so results in an SQLCODE -48.
A triggered action consists of the following elements
-
An optional FOR EACH ROW clause.
-
An optional WHEN clause, consisting of the WHEN keyword followed by
a search condition (simple or complex) enclosed in parentheses. If the search condition
evaluates to TRUE, the trigger is executed. A WHEN clause can only be used when LANGUAGE
is SQL.
-
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 pulled.
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 pulled 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. Caché automatically resets
(NEWs) all variable used in the trigger code. After the execution of each SQL statement,
Caché checks SQLCODE. If an error occurs, Caché 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
Caché ObjectScript statements, enclosed by curly braces. To specify a label
in such 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.
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.
Trigger code written in Caché 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 specify the current field using an asterisk, as follows: {*}. For
UPDATE trigger
code, you can follow the
fieldname with *N, *O, or *C to specify
how to handle a changing field data value. {
fieldname*N} returns
the new field value, after the specified change is made; this is the default. {
fieldname*O}
returns the old field value, the value prior to the specified change. {
fieldname*C}
returns a boolean value indicating whether the field value has been changed (1), or
not changed (0).
Trigger code written in Caché ObjectScript can also contain the pseudo-field
reference variables {%%CLASSNAME}, {%%CLASSNAMEQ}, {%%TABLENAME}, and {%%ID}. The
pseudo-fields are translated into a specific value at class compilation time. {%%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. {%%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 don't know the name of the RowID field. All of these pseudo-field
keywords are case-insensitive.
Trigger code can set the
%ok variable to 0. This creates
a runtime error that aborts execution of the trigger. Trigger code can also set the
%msg variable
to a string describing the cause of the runtime error.
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
pulls multiple triggers, the failure of one trigger causes all remaining triggers
to remain unpulled.
When a database operation fails because of a fatal runtime error, Caché
issues an SQLCODE -415 error. When a trigger operation fails, Caché issues
one of the SQLCODE error codes -130 through -135 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. You can also set the
%msg variable to
a string containing a message to be returned upon trigger failure.
The following example creates an SQL insert trigger:
SET %msql="_SYSTEM"
&sql(CREATE TABLE TestDummy (
TESTNUM INT NOT NULL,
FIRSTWORD CHAR (30) NOT NULL,
LASTWORD CHAR (30) NOT NULL,
CONSTRAINT TestDummyPK PRIMARY KEY (TESTNUM))
)
WRITE !,"SQL table code is: ",SQLCODE
&sql(CREATE TRIGGER TestDummy AFTER INSERT ON TestDummy
BEGIN
INSERT INTO LogFile VALUES ('INSERT into TestDummy');
END)
WRITE !,"SQL trigger code is: ",SQLCODE
CREATE TRIGGER Trigger_1 AFTER INSERT ON Table_1
REFERENCING NEW ROW AS new_row
BEGIN
INSERT INTO Log_Table VALUES ('INSERT into Table_1');
INSERT INTO New_Log_Table VALUES
('INSERT into Table_1', new_row.ID);
END
CREATE TRIGGER Trigger_2 AFTER INSERT ON Table_1
REFERENCING NEW ROW AS new_row
BEGIN
INSERT INTO Log_Table VALUES (new_row.Category);
END