Informix Guide to SQL: Syntax SQL Statements

Use the CREATE TRIGGER statement to create a trigger on a table.

Usage

You can use the CREATE TRIGGER statement to define a trigger on a table. A trigger is a database object that automatically sets off a specified set of SQL statements when a specified event occurs.

You cannot create a trigger on a raw or static table. When you create a trigger on an operational table, the table cannot use light appends. For more information on light appends, see your Administrator's Guide.

Information in this statement that discusses nonlogging databases does not apply to Enterprise Decision Server. In Enterprise Decision Server, all databases are logging databases.

You must be either the owner of the table or have the DBA status to create a trigger on a table.

For information about the relationship between the privileges of the trigger owner and the privileges of other users, see Privileges to Execute Triggered Actions.

You can use roles with triggers. Role-related statements (CREATE ROLE, DROP ROLE, and SET ROLE) and SET SESSION AUTHORIZATION statements can be triggered inside a trigger. Privileges that a user has acquired through enabling a role or through a SET SESSION AUTHORIZATION statement are not relinquished when a trigger is executed.

When you create a trigger, the name of the trigger must be unique within a database.

You can create a trigger only on a table in the current database. You cannot create a trigger on a temporary table, a view, or a system catalog table.

You cannot create a trigger inside an SPL routine if the routine is called inside a data manipulation statement. For example, in the following INSERT statement, if the sp_items procedure contains a trigger, the database server returns an error:

INSERT INTO items EXECUTE PROCEDURE sp_items

For a list of data manipulation statements, see Data Manipulation Statements.

You cannot use an SPL variable in a CREATE TRIGGER statement.

In DB-Access, if you want to define a trigger as part of a schema, place the CREATE TRIGGER statement inside a CREATE SCHEMA statement.

If you are embedding the CREATE TRIGGER statement in an ESQL/C program, you cannot use a host variable in the trigger specification.

The trigger event specifies the type of statement that activates a trigger. The trigger event can be an INSERT, DELETE, UPDATE, or SELECT statement. Each trigger can have only one trigger event. The occurrence of the trigger event is the triggering statement.

For each table, you can define only one trigger that is activated by an INSERT statement and only one trigger that is activated by a DELETE statement. For each table, you can define multiple triggers that are activated by UPDATE statements or SELECT statements. For more information about multiple update or select triggers on the same table, see UPDATE Clause and SELECT Clause.

You cannot define a DELETE trigger event on a table with a referential constraint that specifies ON DELETE CASCADE.

You are responsible for guaranteeing that the triggering statement returns the same result with and without the triggered actions. For more information on the behavior of triggered actions, see Action Clause and Triggered Action List.

A triggering statement from an external database server can activate the trigger. As shown in the following example, an insert trigger on newtab, managed by dbserver1, is activated by an INSERT statement from dbserver2. The trigger executes as if the insert originated on dbserver1.

-- Trigger on stores_demo@dbserver1:newtab



CREATE TRIGGER ins_tr INSERT ON newtab

REFERENCING new AS post_ins

FOR EACH ROW(EXECUTE PROCEDURE nt_pct (post_ins.mc));



-- Triggering statement from dbserver2



INSERT INTO stores_demo@dbserver1:newtab

	SELECT item_num, order_num, quantity, stock_num, 
manu_code,

	total_price FROM items;

If the triggering statement uses a cursor, the complete trigger is activated each time the statement executes. That is, each part of the trigger (BEFORE, FOR EACH ROW, and AFTER) is activated for each row that the cursor processes.

This behavior is different from what occurs when a triggering statement does not use a cursor and updates multiple rows. In this case, the set of triggered actions executes only once. For more information on the execution of triggered actions, see Action Clause.

You must have the appropriate Insert, Delete, Update, or Select privilege on the triggering table to execute the INSERT, DELETE, UPDATE, or SELECT statement that is the trigger event. The triggering statement might still fail, however, if you do not have the privileges necessary to execute one of the SQL statements in the action clause. When the triggered actions are executed, the database server checks your privileges for each SQL statement in the trigger definition as if the statement were being executed independently of the trigger. For information on the privileges you need to execute a trigger, see Privileges to Execute Triggered Actions.

The INSERT, DELETE, UPDATE, and SELECT statements that initiate triggers might appear to execute slowly because they activate additional SQL statements, and the user might not know that other actions are occurring.

The execution time for a triggering data manipulation statement depends on the complexity of the triggered action and whether it initiates other triggers. Obviously, the elapsed time for the triggering data manipulation statement increases as the number of cascading triggers increases. For more information on triggers that initiate other triggers, see Cascading Triggers.

If the trigger event is an UPDATE statement, the trigger executes when any column in the triggering column list is updated.

If the trigger event is an UPDATE statement and you do not specify the OF column option in the definition of the trigger event, the trigger executes when any column in the triggering table is updated.

If the triggering UPDATE statement updates more than one of the triggering columns in a trigger, the trigger executes only once.

If you define more than one update trigger event on a table, the column lists of the triggers must be mutually exclusive. The following example shows that trig3 is illegal on the items table because its column list includes stock_num, which is a triggering column in trig1. Multiple update triggers on a table cannot include the same columns.

CREATE TRIGGER trig1 UPDATE OF item_num, stock_num ON items

REFERENCING OLD AS pre NEW AS post

FOR EACH ROW(EXECUTE PROCEDURE proc1());



CREATE TRIGGER trig2 UPDATE OF manu_code ON items

BEFORE(EXECUTE PROCEDURE proc2());



-- Illegal trigger: stock_num occurs in trig1

CREATE TRIGGER trig3 UPDATE OF order_num, stock_num ON items

BEFORE(EXECUTE PROCEDURE proc3());

When an UPDATE statement updates multiple columns that have different triggers, the column numbers of the triggering columns determine the order of trigger execution. Execution begins with the smallest triggering column number and proceeds in order to the largest triggering column number. The following example shows that table taba has four columns (a, b, c, d):

CREATE TABLE taba (a int, b int, c int, d int)

Define trig1 as an update on columns a and c, and define trig2 as an update on columns b and d, as shown in the following example:

CREATE TRIGGER trig1 UPDATE OF a, c ON taba

	AFTER (UPDATE tabb SET y = y + 1);



CREATE TRIGGER trig2 UPDATE OF b, d ON taba

	AFTER (UPDATE tabb SET z = z + 1);

The triggering statement is shown in the following example:

UPDATE taba SET (b, c) = (b + 1, c + 1)

Then trig1 for columns a and c executes first, and trig2 for columns b and d executes next. In this case, the smallest column number in the two triggers is column 1 (a), and the next is column 2 (b).

If the trigger event is a SELECT statement, the trigger executes when any column in the triggering column list is selected.

If the trigger event is a SELECT statement and you do not specify the OF column option in the definition of the trigger event, the trigger executes when any column in the triggering table is selected.

If the triggering SELECT statement selects more than one of the triggering columns in a trigger, the trigger executes only once.

The triggered action of a select trigger cannot include UPDATE, INSERT, or DELETE actions on the triggering table. However, the triggered action of a select trigger can include UPDATE, INSERT, and DELETE actions on tables other than the triggering table.

The following example shows how a select trigger is defined within a CREATE TRIGGER statement:

CREATE TRIGGER mytrig 

	SELECT OF cola ON mytab

	REFERENCING OLD AS pre

	FOR EACH ROW (INSERT INTO newtab('for each action'))

A SELECT statement on the triggering table activates a select trigger in the following circumstances:

• The SELECT statement is a standalone SELECT statement.
• The SELECT statement occurs within a UDR called in a select list.
• The SELECT statement is a subquery in a select list.
• The SELECT statement occurs within a UDR called by EXECUTE PROCEDURE.
• The SELECT statement occurs within a UDR called by EXECUTE PROCEDURE or EXECUTE FUNCTION.
• The SELECT statement selects data from a supertable in a table hierarchy. In this case the SELECT statement activates select triggers for the supertable and all the subtables in the hierarchy.

For information on the conditions when a SELECT statement on the triggering table does not activate a select trigger, see Circumstances When a Select Trigger is Not Activated.

A select trigger is activated if the triggering column appears in the select list of a standalone SELECT statement. For example, assume that a select trigger was defined such that it will execute whenever column col1 of table tab1 is selected. Both of the following standalone SELECT statements will activate the select trigger.

SELECT * FROM tab1;

SELECT col1 FROM tab1;

A select trigger is activated by a UDR if the UDR contains a SELECT statement within its statement block and if the UDR appears in the select list of a SELECT statement. For example, assume that a UDR named my_rtn contains the following SELECT statement in its statement block:

SELECT col1 FROM tab1

Now suppose that the following SELECT statement invokes the my_rtn UDR in its select list:

SELECT my_rtn() FROM tab2

This SELECT statement activates the select trigger defined on column col1 of table tab1 when the my_rtn UDR is executed.

A select trigger is activated by a UDR if the UDR contains a SELECT statement within its statement block and the UDR is called by an EXECUTE PROCEDURE or EXECUTE FUNCTION statement. For example, assume that the user-defined procedure named my_rtn contains the following SELECT statement in its statement block:

SELECT col1 FROM tab1

Now suppose that the following EXECUTE PROCEDURE statement invokes the my_rtn procedure:

EXECUTE PROCEDURE my_rtn()

This EXECUTE PROCEDURE statement activates the select trigger defined on column col1 of table tab1 when the SELECT statement within the statement block is executed.

A select trigger is activated by a subquery if the subquery appears in the select list of a SELECT statement. For example, if a select trigger was defined on col1 of tab1, the subquery in the following SELECT statement activates the select trigger:

SELECT (SELECT col1 FROM tab1 WHERE col1=1),

	colx, col y

	FROM tabz

Select Triggers in Table Hierarchies

A subtable inherits the select triggers that are defined on its supertable. When you select from a supertable, the SELECT statement activates the select triggers on the supertable and the inherited select triggers on the subtables in the table hierarchy. For example, assume that table tab1 is the supertable and table tab2 is the subtable in a table hierarchy. If the select trigger trig1 is defined on table tab1, a SELECT statement on table tab1 activates the select trigger trig1 for the rows in table tab1 and the inherited select trigger trig1 for the rows in table tab2.

If you add a select trigger to a subtable, this select trigger can override the select trigger that the subtable inherits from its supertable. For example, if the select trigger trig1 is defined on column col1 in supertable tab1, the subtable tab2 inherits this trigger. But if you define a select trigger named trig2 on column col1 in subtable tab2, and a SELECT statement selects from col1 in supertable tab1, this SELECT statement activates trigger trig1 for the rows in table tab1 and trigger trig2 (not trigger trig1) for the rows in table tab2. In other words, the trigger that you add to the subtable overrides the trigger that the subtable inherits from the supertable.

A SELECT statement on the triggering table does not activate a select trigger in certain circumstances:

• If a subquery or UDR containing the triggering SELECT statement appears in any clause of a SELECT statement other than the select list, the select trigger is not activated.

For example, if the subquery or UDR appears in the WHERE clause or HAVING clause of a SELECT statement, the SELECT statement within the subquery or UDR does not activate the select trigger.

• If a SELECT statement contains a built-in aggregate or user-defined aggregate in its select list, the select trigger is not activated. For example, the following SELECT statement does not activate a select trigger defined on col1 of tab1:

		SELECT MIN(col1) FROM tab1

• If the triggered action of a select trigger executes a UDR that has a triggering SELECT statement, the select trigger on this SELECT statement is not activated. Cascading select triggers are not supported.
• If a SELECT statement includes the UNION or UNION ALL operator, this statement does not activate a select trigger.
• The SELECT clause of an INSERT statement does not activate a select trigger.
• If the select list of a SELECT statement includes the DISTINCT or UNIQUE keywords, the SELECT statement does not activate a select trigger.
• Select triggers are not supported on scroll cursors.

If the triggered action of a select trigger is a FOR EACH ROW action, and a row appears more than once in the result of the triggering SELECT statement, the database server executes the FOR EACH ROW action for each instance of the row. For example, the same row can appear more than once in the result of a SELECT statement that joins two tables. For more information on FOR EACH ROW actions, see FOR EACH ROW Actions.

The action clause defines the characteristics of triggered actions and specifies the time when these actions occur. You must define at least one triggered action, using the keywords BEFORE, FOR EACH ROW, or AFTER to indicate when the action occurs relative to the triggering statement. You can specify triggered actions for all three options on a single trigger, but you must order them in the following sequence: BEFORE, FOR EACH ROW, and AFTER. You cannot follow a FOR EACH ROW triggered action list with a BEFORE triggered action list. If the first triggered action list is FOR EACH ROW, an AFTER action list is the only option that can follow it. For more information on the action clause when a REFERENCING clause is present, see Action Clause Referencing.

The BEFORE triggered action or actions execute once before the triggering statement executes. If the triggering statement does not process any rows, the BEFORE triggered actions still execute because the database server does not yet know whether any row is affected.

The FOR EACH ROW triggered action or actions execute once for each row that the triggering statement affects. The triggered SQL statement executes after the triggering statement processes each row.

If the triggering statement does not insert, delete, update, or select any rows, the FOR EACH ROW triggered actions do not execute.

You cannot have FOR EACH ROW actions on tables that have globally-detached indexes.

An AFTER triggered action or actions execute once after the action of the triggering statement is complete. If the triggering statement does not process any rows, the AFTER triggered actions still execute.

When an UPDATE statement activates multiple triggers, the triggered actions merge. Assume that taba has columns a, b, c, and d, as shown in the following example:

CREATE TABLE taba (a int, b int, c int, d int)

Next, assume that you define trig1 on columns a and c, and trig2 on columns b and d. If both triggers have triggered actions that are executed BEFORE, FOR EACH ROW, and AFTER, then the triggered actions are executed in the following sequence:

1. BEFORE action list for trigger (a, c)
2. BEFORE action list for trigger (b, d)
3. FOR EACH ROW action list for trigger (a, c)
4. FOR EACH ROW action list for trigger (b, d)
5. AFTER action list for trigger (a, c)
6. AFTER action list for trigger (b, d)

The database server treats the triggers as a single trigger, and the triggered action is the merged-action list. All the rules governing a triggered action apply to the merged list as one list, and no distinction is made between the two original triggers.

In a FOR EACH ROW triggered-action list, the result might depend on the order of the rows being processed. You can ensure that the result is independent of row order by following these suggestions:

• Avoid selecting the triggering table in the FOR EACH ROW section.

If the triggering statement affects multiple rows in the triggering table, the result of the SELECT statement in the FOR EACH ROW section varies as each row is processed. This condition also applies to any cascading triggers. See Cascading Triggers.

• In the FOR EACH ROW section, avoid updating a table with values derived from the current row of the triggering table.

If the triggered actions modify any row in the table more than once, the final result for that row depends on the order in which rows from the triggering table are processed.

• Avoid modifying a table in the FOR EACH ROW section that is selected by another triggered statement in the same FOR EACH ROW section, including any cascading triggered actions.

If you modify a table in this section and refer to it later, the changes to the table might not be complete when you refer to it. Consequently, the result might differ, depending on the order in which rows are processed.

The database server does not enforce rules to prevent these situations because doing so would restrict the set of tables from which a triggered action can select. Furthermore, the result of most triggered actions is independent of row order. Consequently, you are responsible for ensuring that the results of the triggered actions are independent of row order.

Once you assign a correlation name, you can use it only inside the FOR EACH ROW triggered action. See Action Clause Referencing.

To use the correlation name, precede the column name with the correlation name, followed by a period. For example, if the new correlation name is post, refer to the new value for the column fname as post.fname.

If the trigger event is an INSERT statement, using the old correlation name as a qualifier causes an error because no value exists before the row is inserted. For the rules that govern how to use correlation names, see Using Correlation Names in Triggered Actions.

You can use the INSERT REFERENCING clause only if you define a FOR EACH ROW triggered action.

The following example illustrates the use of the INSERT REFERENCING clause. This example inserts a row into backup_table1 for every row that is inserted into table1. The values that are inserted into col1 and col2 of backup_table1 are an exact copy of the values that were just inserted into table1.

CREATE TABLE table1 (col1 INT, col2 INT);

CREATE TABLE backup_table1 (col1 INT, col2 INT);

CREATE TRIGGER before_trig

	INSERT ON table1

	REFERENCING NEW as new

	FOR EACH ROW

	(

	INSERT INTO backup_table1 (col1, col2)

	VALUES (new.col1, new.col2)

	);

As the preceding example shows, the advantage of the INSERT REFERENCING clause is that it allows you to refer to the data values that the trigger event in your triggered action produces.

Once you assign a correlation name, you can use it only inside the FOR EACH ROW triggered action. See Action Clause Referencing.

You use the correlation name to refer to an old column value by preceding the column name with the correlation name and a period (.). For example, if the old correlation name is pre, refer to the old value for the column fname as pre.fname.

If the trigger event is a DELETE statement, using the new correlation name as a qualifier causes an error because the column has no value after the row is deleted. For the rules governing the use of correlation names, see Using Correlation Names in Triggered Actions.

You can use the DELETE REFERENCING clause only if you define a FOR EACH ROW triggered action.

The OLD correlation value cannot be a byte or text value. That is, you cannot refer to a byte or text column.

Once you assign a correlation name, you can use it only inside the FOR EACH ROW triggered action. See Action Clause Referencing.

Use the correlation name to refer to an old or new column value by preceding the column name with the correlation name and a period (.). For example, if the new correlation name is post, you refer to the new value for the column fname as post.fname.

If the trigger event is an UPDATE statement, you can define both old and new correlation names to refer to column values before and after the triggering update. For the rules that govern the use of correlation names, see Using Correlation Names in Triggered Actions.

You can use the UPDATE REFERENCING clause only if you define a FOR EACH ROW triggered action.

The OLD correlation value cannot be a byte or text value. That is, you cannot refer to a byte or text column.

Once you assign a correlation name, you can use it only inside the FOR EACH ROW triggered action. See Action Clause Referencing.

You use the correlation name to refer to an old column value by preceding the column name with the correlation name and a period (.). For example, if the old correlation name is pre, refer to the old value for the column fname as pre.fname.

If the trigger event is a SELECT statement, using the new correlation name as a qualifier causes an error because the column does not have a new value after the column is selected. For the rules governing the use of correlation names, see Using Correlation Names in Triggered Actions.

You can use the SELECT REFERENCING clause only if you define a FOR EACH ROW triggered action.

The OLD correlation value cannot be a byte or text value. That is, you cannot refer to a byte or text column.

If the CREATE TRIGGER statement contains an INSERT REFERENCING clause, a DELETE REFERENCING clause, an UPDATE REFERENCING clause, or a SELECT REFERENCING clause, you must include a FOR EACH ROW triggered-action list in the action clause. You can also include BEFORE and AFTER triggered-action lists, but they are optional. For information on the BEFORE, FOR EACH ROW, and AFTER triggered-action lists, see Action Clause.

You cannot have FOR EACH ROW actions on tables that have globally-detached indexes.

The triggered action consists of an optional WHEN condition and the action statements. Database objects that are referenced in the triggered action, that is, tables, columns, and UDRs, must exist when the CREATE TRIGGER statement is executed. This rule applies only to database objects that are referenced directly in the trigger definition.

Warning: When you specify a date expression in the WHEN condition or in an action statement, make sure to specify 4 digits instead of 2 digits for the year. When you specify a 4-digit year, the DBCENTURY environment variable has no effect on how the database server interprets the date expression. When you specify a 2-digit year, the DBCENTURY environment variable can affect how the database server interprets the date expression, so the triggered action might produce unpredictable results. See the "Informix Guide to SQL: Reference" for more information on the DBCENTURY environment variable.

WHEN Condition

The WHEN condition lets you make the triggered action dependent on the outcome of a test. When you include a WHEN condition in a triggered action, if the triggered action evaluates to true, the actions in the triggered action list execute in the order in which they appear. If the WHEN condition evaluates to false or unknown, the actions in the triggered action list are not executed. If the triggered action is in a FOR EACH ROW section, its search condition is evaluated for each row.

For example, the triggered action in the following trigger executes only if the condition in the WHEN clause is true:

CREATE TRIGGER up_price

UPDATE OF unit_price ON stock

REFERENCING OLD AS pre NEW AS post

FOR EACH ROW WHEN(post.unit_price > pre.unit_price * 2)

	(INSERT INTO warn_tab VALUES(pre.stock_num,

		pre.order_num, pre.unit_price, post.unit_price,

		CURRENT))

An SPL routine that executes inside the WHEN condition carries the same restrictions as a UDR that is called in a data-manipulation statement.That is, the called SPL routine cannot contain certain SQL statements. For information on which statements are restricted, see Restrictions on an SPL Routine Called in a Data Manipulation Statement.

The triggered-action statements can be INSERT, DELETE, UPDATE, EXECUTE FUNCTION, or EXECUTE PROCEDURE statements. If a triggered-action list contains multiple statements, these statements execute in the order in which they appear in the list.

You can use both user-defined functions and user-defined procedures as triggered actions.

Use the EXECUTE FUNCTION statement to execute any user-defined function. Use the EXECUTE PROCEDURE statement to execute any user-defined procedure.

Use the EXECUTE PROCEDURE statement to execute any SPL routine.

For restrictions that apply to using SPL routines as triggered actions, see Rules for SPL Routines.

To guarantee that the triggering statement returns the same result with and without the triggered actions, make sure that the triggered actions in the BEFORE and FOR EACH ROW sections do not modify any table referenced in the following clauses:

• WHERE clause
• SET clause in the UPDATE statement
• SELECT clause
• EXECUTE PROCEDURE clause or EXECUTE FUNCTION clause in a multiple-row INSERT statement

If you use the INSERT, DELETE, UPDATE, or EXECUTE reserved words as an identifier in any of the following clauses inside a triggered action list, you must qualify them by the owner name, the table name, or both:

• FROM clause of a SELECT statement
• INTO clause of the EXECUTE PROCEDURE or EXECUTE FUNCTION statement
• GROUP BY clause
• SET clause of the UPDATE statement

You get a syntax error if these keywords are not qualified when you use these clauses inside a triggered action.

If you use the keyword as a column name, it must be qualified by the table name-for example, table.update. If both the table name and the column name are keywords, they must be qualified by the owner name-for example, owner.insert.update. If the owner name, table name, and column name are all keywords, the owner name must be in quotes-for example, 'delete'.insert.update. The only exception is when these keywords are the first table or column name in the list, and you do not have to qualify them. For example, delete in the following statement does not need to be qualified because it is the first column listed in the INTO clause:

CREATE TRIGGER t1 UPDATE OF b ON tab1

	FOR EACH ROW (EXECUTE PROCEDURE p2()

	INTO delete, d)

The following statements show examples in which you must qualify the column name or the table name:

• FROM clause of a SELECT statement
CREATE TRIGGER t1 INSERT ON tab1
BEFORE (INSERT INTO tab2 SELECT * FROM tab3,
'owner1'.update)
• INTO clause of an EXECUTE PROCEDURE statement
CREATE TRIGGER t3 UPDATE OF b ON tab1
FOR EACH ROW (EXECUTE PROCEDURE p2() INTO
d, tab1.delete)
• GROUP BY clause of a SELECT statement
CREATE TRIGGER t4 DELETE ON tab1
BEFORE (INSERT INTO tab3 SELECT deptno, SUM(exp)
FROM budget GROUP BY deptno, budget.update)
• SET clause of an UPDATE statement
CREATE TRIGGER t2 UPDATE OF a ON tab1
BEFORE (UPDATE tab2 SET a = 10, tab2.insert = 5)

The following rules apply when you use correlation names in triggered actions:

• You can use the correlation names for the old and new column values only in statements in the FOR EACH ROW triggered-action list. You can use the old and new correlation names to qualify any column in the triggering table in either the WHEN condition or the triggered SQL statements.
• The old and new correlation names refer to all rows affected by the triggering statement.
• You cannot use the correlation name to qualify a column name in the GROUP BY, the SET, or the COUNT DISTINCT clause.
• The scope of the correlation names is the entire trigger definition. This scope is statically determined, meaning that it is limited to the trigger definition; it does not encompass cascading triggers or columns that are qualified by a table name in a UDR that is a triggered action.

In an SQL statement in a FOR EACH ROW triggered action, you must qualify all references to columns in the triggering table with either the old or new correlation name, unless the statement is valid independent of the triggered action.

In other words, if a column name inside a FOR EACH ROW triggered action list is not qualified by a correlation name, even if it is qualified by the triggering table name, it is interpreted as if the statement is independent of the triggered action. No special effort is made to search the definition of the triggering table for the non-qualified column name.

For example, assume that the following DELETE statement is a triggered action inside the FOR EACH ROW section of a trigger:

DELETE FROM tab1 WHERE col_c = col_c2

For the statement to be valid, both col_c and col_c2 must be columns from tab1. If col_c2 is intended to be a correlation reference to a column in the triggering table, it must be qualified by either the old or the new correlation name. If col_c2 is not a column in tab1 and is not qualified by either the old or new correlation name, you get an error.

When a column is not qualified by a correlation name, and the statement is valid independent of the triggered action, the column name refers to the current value in the database. In the triggered action for trigger t1 in the following example, mgr in the WHERE clause of the correlated subquery is an unqualified column from the triggering table. In this case, mgr refers to the current column value in empsal because the INSERT statement is valid independent of the triggered action.

CREATE DATABASE db1;

CREATE TABLE empsal (empno INT, salary INT, mgr INT);

CREATE TABLE mgr (eno INT, bonus INT);

CREATE TABLE biggap (empno INT, salary INT, mgr INT);



CREATE TRIGGER t1 UPDATE OF salary ON empsal

AFTER (INSERT INTO biggap SELECT * FROM empsal WHERE salary <

	(SELECT bonus FROM mgr WHERE eno = mgr));

In a triggered action, an unqualified column name from the triggering table refers to the current column value, but only when the triggered statement is valid independent of the triggered action.

The following table summarizes the value retrieved when you use the column name qualified by the old correlation name and the column name qualified by the new correlation name.

Refer to the following key when you read the table.

Outside a FOR EACH ROW triggered-action list, you cannot qualify a column from the triggering table with either the old correlation name or the new correlation name; it always refers to the current value in the database.

In some cases a trigger can be reentrant. In these cases the triggered action can reference the triggering table. In other words, both the trigger event and the triggered action can operate on the same table. The following list summarizes the situations in which triggers can be reentrant and the situations in which triggers cannot be reentrant:

• If the trigger event is an UPDATE statement, the triggered action cannot be an INSERT or DELETE statement that references the table that was updated by the trigger event.
• If the trigger event is an UPDATE statement, the triggered action cannot be an UPDATE statement that references a column that was updated by the trigger event.

However, if the trigger event is an UPDATE statement, and the triggered action is also an UPDATE statement, the triggered action can update a column that was not updated by the trigger event.

For example, assume that the following UPDATE statement, which updates columns a and b of tab1, is the triggering statement:

UPDATE tab1 SET (a, b) = (a + 1, b + 1)

Now consider the 1vbtriggered actions in the following example. The first UPDATE statement is a valid triggered action, but the second one is not because it updates column b again.

UPDATE tab1 SET c = c + 1; -- OK
UPDATE tab1 SET b = b + 1; -- ILLEGAL

• If the trigger event is an UPDATE statement, the triggered action can be an EXECUTE PROCEDURE or EXECUTE FUNCTION statement with an INTO clause that references a column that was updated by the trigger event or any other column in the triggering table.

When an EXECUTE PROCEDURE or EXECUTE FUNCTION statement is the triggered action, you can specify the INTO clause for an UPDATE trigger only when the triggered action occurs in the FOR EACH ROW section. In this case, the INTO clause can contain only column names from the triggering table. The following statement illustrates the appropriate use of the INTO clause:

CREATE TRIGGER upd_totpr UPDATE OF quantity ON items
REFERENCING OLD AS pre_upd NEW AS post_upd
FOR EACH ROW(EXECUTE PROCEDURE
calc_totpr(pre_upd.quantity,
post_upd.quantity, pre_upd.total_price)
INTO total_price)

The column that follows the INTO keyword can be a column in the triggering table that was updated by the trigger event, or a column in the triggering table that was not updated by the trigger event.

When the INTO clause appears in the EXECUTE PROCEDURE or EXECUTE FUNCTION statement, the database server updates the columns named there with the values returned from the UDR. The database server performs the update immediately upon returning from the UDR.

• If the trigger event is an INSERT statement, the triggered action cannot be an INSERT or DELETE statement that references the triggering table.
• If the trigger event is an INSERT statement, the triggered action can be an UPDATE statement that references a column in the triggering table. However, this column cannot be a column for which a value was supplied by the trigger event.

If the trigger event is an INSERT, and the triggered action is an UPDATE on the triggering table, the columns in both statements must be mutually exclusive. For example, assume that the trigger event is an INSERT statement that inserts values for columns cola and colb of table tab1:

INSERT INTO tab1 (cola, colb) VALUES (1,10)

Now consider the triggered actions. The first UPDATE statement is valid, but the second one is not because it updates column colb even though the trigger event already supplied a value for column colb:

UPDATE tab1 SET colc=100; --OK
UPDATE tab1 SET colb=100; --ILLEGAL

• If the trigger event is an INSERT statement, the triggered action can be an EXECUTE PROCEDURE or EXECUTE FUNCTION statement with an INTO clause that references a column that was supplied by the trigger event or a column that was not supplied by the trigger event.

When an EXECUTE PROCEDURE or EXECUTE FUNCTION statement is the triggered action, you can specify the INTO clause for an INSERT trigger only when the triggered action occurs in the FOR EACH ROW section. In this case, the INTO clause can contain only column names from the triggering table. The following statement illustrates the appropriate use of the INTO clause:

CREATE TRIGGER ins_totpr INSERT ON items
REFERENCING NEW as new_ins
FOR EACH ROW (EXECUTE PROCEDURE
calc_totpr(0, new_ins.quantity, 0)
INTO total_price).

The column that follows the INTO keyword can be a column in the triggering table that was supplied by the trigger event, or a column in the triggering table that was not supplied by the trigger event.

When the INTO clause appears in the EXECUTE PROCEDURE or EXECUTE FUNCTION statement, the database server updates the columns named there with the values returned from the UDR. The database server performs the update immediately upon returning from the UDR.

• If the triggered action is a SELECT statement, the SELECT statement can reference the triggering table. The SELECT statement can be a triggered statement in the following instances:
• The SELECT statement appears in a subquery in the WHEN clause or a triggered-action statement.
• The triggered action is a UDR, and the SELECT statement appears inside the UDR.

The cases when a trigger cannot be reentrant apply recursively to all cascading triggers, which are considered part of the initial trigger. In particular, this rule means that a cascading trigger cannot update any columns in the triggering table that were updated by the original triggering statement, including any nontriggering columns affected by that statement. For example, assume the following UPDATE statement is the triggering statement:

UPDATE tab1 SET (a, b) = (a + 1, b + 1)

Then in the cascading triggers shown in the following example, trig2 fails at runtime because it references column b, which is updated by the triggering UPDATE statement:

CREATE TRIGGER trig1 UPDATE OF a ON tab1											-- Valid

	AFTER (UPDATE tab2 set e = e + 1);



CREATE TRIGGER trig2 UPDATE of e ON tab2											-- Invalid

	AFTER (UPDATE tab1 set b = b + 1);

Now consider the following SQL statements. When the final UPDATE statement is executed, column a is updated and the trigger trig1 is activated. The triggered action again updates column a with an EXECUTE PROCEDURE INTO statement.

CREATE TABLE temp1 (a int, b int, e int);

INSERT INTO temp1 VALUES (10, 20, 30);

CREATE PROCEDURE proc(val int)
RETURNING int,int;
RETURN val+10, val+20;
END PROCEDURE;

CREATE TRIGGER trig1 UPDATE OF a ON temp1
FOR EACH ROW (EXECUTE PROCEDURE proc(50) INTO a, e);

CREATE TRIGGER trig2 UPDATE OF e ON temp1
FOR EACH ROW (EXECUTE PROCEDURE proc(100) INTO a, e);

UPDATE temp1 SET (a,b) = (40,50);

In Enterprise Decision Server, to recreate this example use the CREATE PROCEDURE statement instead of the CREATE FUNCTION statement.

Several questions arise from this example of cascading triggers. First, should the update of column a activate trigger trig1 again? The answer is no. Because the trigger was activated, it is stopped from being activated a second time. Whenever the triggered action is an EXECUTE PROCEDURE INTO statement or EXECUTE FUNCTION INTO statement, the only triggers that are activated are those that are defined on columns that are mutually exclusive from the columns in that table updated until then (in the cascade of triggers). Other triggers are ignored.

Another question that arises from the example is whether trigger trig2 should be activated. The answer is yes. The trigger trig2 is defined on column e. Until now, column e in table temp1 has not been modified. Trigger trig2 is activated.

A final question that arises from the example is whether triggers trig1 and trig2 should be activated after the triggered action in trig2 is performed. The answer is no. Neither trigger is activated. By this time columns a and e have been updated once, and triggers trig1 and trig2 have been executed once. The database server ignores these triggers instead of firing them.

For more information about cascading triggers, see Cascading Triggers.

In addition to the rules listed in Reentrancy of Triggers, the following rules apply to an SPL routine that is used as a triggered action:

• The SPL routine cannot be a cursor function (that is, a function that returns more than one row) in a place where only one row is expected.
• You cannot use the old or new correlation name inside the SPL routine.

If you need to use the corresponding values in the routine, you must pass them as parameters. The routine should be independent of triggers, and the old or new correlation name do not have any meaning outside the trigger.

When you use an SPL routine as a triggered action, the database objects that the routine references are not checked until the routine is executed.

If you are not the trigger owner, but the privileges of the trigger owner include the WITH GRANT OPTION privilege, you inherit the privileges of the owner as well as the WITH GRANT OPTION privilege for each triggered SQL statement. You have these privileges in addition to your privileges.

If the triggered action is a UDR you must have the Execute privilege on the UDR or the owner of the trigger must have the Execute privilege and the WITH GRANT OPTION privilege.

While executing the UDR, you do not carry the privileges of the trigger owner; instead you receive the privileges granted with the UDR, as follows:

1. Privileges for a DBA UDR

When a UDR is registered with the CREATE DBA keywords and you are granted the Execute privilege on the UDR, the database server automatically grants you temporary DBA privileges while the UDR executes. These DBA privileges are available only when you are executing the UDR.

2. Privileges for a UDR without DBA restrictions

If the UDR owner has the WITH GRANT OPTION right for the necessary privileges on the underlying database objects, you inherit these privileges when you are granted the Execute privilege. In this case, all the non-qualified database objects that the UDR references are qualified by the name of the UDR owner.

If the UDR owner does not have the WITH GRANT OPTION right, you have your original privileges on the underlying database objects when the UDR executes.

For more information on privileges on SPL routines, refer to the Informix Guide to SQL: Tutorial.

To create a trigger that is executable by anyone who has the privileges to execute the triggering statement, you can ask the DBA to create a DBA-privileged UDR and grant you the Execute privilege with the WITH GRANT OPTION right. You then use the DBA-privileged UDR as the triggered action. Anyone can execute the triggered action because the DBA-privileged UDR carries the WITH GRANT OPTION right. When you activate the UDR, the database server applies privilege-checking rules for a DBA.

The database server allows triggers other than select triggers to cascade, meaning that the triggered actions of one trigger can activate another trigger. For further information on the restriction against cascading select triggers, see Circumstances When a Select Trigger is Not Activated.

The maximum number of triggers in a cascading sequence is 61; the initial trigger plus a maximum of 60 cascading triggers. When the number of cascading triggers in a series exceeds the maximum, the database server returns error number -748, as the following example shows:

Exceeded limit on maximum number of cascaded triggers.

The following example illustrates a series of cascading triggers that enforce referential integrity on the manufact, stock, and items tables in the stores_demo database. When a manufacturer is deleted from the manufact table, the first trigger, del_manu, deletes all the items from that manufacturer from the stock table. Each delete in the stock table activates a second trigger, del_items, that deletes all the items from that manufacturer from the items table. Finally, each delete in the items table triggers the SPL routine log_order, which creates a record of any orders in the orders table that can no longer be filled.

CREATE TRIGGER del_manu

DELETE ON manufact

REFERENCING OLD AS pre_del

FOR EACH ROW(DELETE FROM stock 

	WHERE manu_code = pre_del.manu_code);



CREATE TRIGGER del_stock

DELETE ON stock

REFERENCING OLD AS pre_del

FOR EACH ROW(DELETE FROM items 

	WHERE manu_code = pre_del.manu_code);



CREATE TRIGGER del_items

DELETE ON items

REFERENCING OLD AS pre_del

FOR EACH ROW(EXECUTE PROCEDURE log_order(pre_del.order_num));

When you are not using logging, referential integrity constraints on both the manufact and stock tables prohibit the triggers in this example from executing. When you use logging, however, the triggers execute successfully because constraint checking is deferred until all the triggered actions are complete, including the actions of cascading triggers. For more information about how constraints are handled when triggers execute, see Constraint Checking.

The database server prevents loops of cascading triggers by not allowing you to modify the triggering table in any cascading triggered action, except an UPDATE statement, which does not modify any column that the triggering UPDATE statement updated, or an INSERT statement. INSERT trigger statements can have UPDATE triggered actions on the same table.

When you use logging, the database server defers constraint checking on the triggering statement until after the statements in the triggered-action list execute. The database server effectively executes a SET CONSTRAINTS ALL DEFERRED statement before it executes the triggering statement. After the triggered action is completed, it effectively executes a SET CONSTRAINTS constraint IMMEDIATE statement to check the constraints that were deferred. This action allows you to write triggers so that the triggered action can resolve any constraint violations that the triggering statement creates. For more information, see SET Database Object Mode.

Consider the following example, in which the table child has constraint r1, which references the table parent. You define trigger trig1 and activate it with an INSERT statement. In the triggered action, trig1 checks to see if parent has a row with the value of the current cola in child; if not, it inserts it.

CREATE TABLE parent (cola INT PRIMARY KEY);

CREATE TABLE child (cola INT REFERENCES parent CONSTRAINT r1);

CREATE TRIGGER trig1 INSERT ON child

	REFERENCING NEW AS new

	FOR EACH ROW

	WHEN((SELECT COUNT (*) FROM parent 

		WHERE cola = new.cola) = 0)

-- parent row does not exist

	(INSERT INTO parent VALUES (new.cola));

When you insert a row into a table that is the child table in a referential constraint, the row might not exist in the parent table. The database server does not immediately return this error on a triggering statement. Instead, it allows the triggered action to resolve the constraint violation by inserting the corresponding row into the parent table. As the previous example shows, you can check within the triggered action to see whether the parent row exists, and if so, bypass the insert.

For a database without logging, the database server does not defer constraint checking on the triggering statement. In this case, it immediately returns an error if the triggering statement violates a constraint.

You cannot use the SET Transaction Mode statement in a triggered action. The database server checks this restriction when you activate a trigger, because the statement could occur inside a UDR.

Rows that cause constraint violations might appear in the violations table even if a later trigger action corrects the violation.

When you activate multiple triggers with an UPDATE statement, a trigger can possibly override the changes that an earlier trigger made. If you do not want the triggered actions to interact, you can split the UPDATE statement into multiple UPDATE statements, each of which updates an individual column. As another alternative, you can create a single update trigger for all columns that require a triggered action. Then, inside the triggered action, you can test for the column being updated and apply the actions in the desired order. This approach, however, is different than having the database server apply the actions of individual triggers, and it has the following disadvantages:

• If the trigger has a BEFORE action, it applies to all columns because you cannot yet detect whether a column has changed.
• If the triggering UPDATE statement sets a column to the current value, you cannot detect the update, so the triggered action is skipped. You might want to execute the triggered action even though the value of the column has not changed.

The statements inside the triggered action can affect tables in external databases. The following example shows an update trigger on dbserver1, which triggers an update to items on dbserver2:

CREATE TRIGGER upd_nt UPDATE ON newtab

REFERENCING new AS post

FOR EACH ROW(UPDATE stores_demo@dbserver2:items 

	SET quantity = post.qty WHERE stock_num = post.stock 

	AND manu_code = post.mc)

If a statement from an external database server initiates the trigger, however, and the triggered action affects tables in an external database, the triggered actions fail. For example, the following combination of triggered action and triggering statement results in an error when the triggering statement executes:

-- Triggered action from dbserver1 to dbserver3:



CREATE TRIGGER upd_nt UPDATE ON newtab

REFERENCING new AS post

FOR EACH ROW(UPDATE stores_demo@dbserver3:items 

	SET quantity = post.qty WHERE stock_num = post.stock 

	AND manu_code = post.mc);



-- Triggering statement from dbserver2:



UPDATE stores_demo@dbserver1:newtab 

	SET qty = qty * 2 WHERE s_num = 5 

	AND mc = 'ANZ'; 

You can create triggers for databases, with and without logging. However, when the database does not have logging, you cannot roll back when the triggering statement fails. In this case, you are responsible for maintaining data integrity in the database.

If the trigger fails and the database has transactions, all triggered actions and the triggering statement are rolled back because the triggered actions are an extension of the triggering statement. The rest of the transaction, however, is not rolled back.

The row action of the triggering statement occurs before the triggered actions in the FOR EACH ROW section. If the triggered action fails for a database without logging, the application must restore the row that was changed by the triggering statement to its previous value.

When you use a UDR as a triggered action, if you terminate the UDR in an exception-handling section, any actions that modify data inside that section are rolled back along with the triggering statement. In the following partial example, when the exception handler traps an error, it inserts a row into the table logtab:

ON EXCEPTION IN (-201)

	INSERT INTO logtab values (errno, errstr);

	RAISE EXCEPTION -201

END EXCEPTION

When the RAISE EXCEPTION statement returns the error, however, the database server rolls back this insert because it is part of the triggered actions. If the UDR is executed outside a triggered action, the insert is not rolled back.

The UDR that implements a triggered action cannot contain any BEGIN WORK, COMMIT WORK, or ROLLBACK WORK statements. If the database has logging, you must either begin an explicit transaction before the triggering statement, or the statement itself must be an implicit transaction. In any case, another transaction-related statement cannot appear inside the UDR.

You can use triggers to enforce referential actions that the database server does not currently support. For any database without logging, you are responsible for maintaining data integrity when the triggering statement fails.

Use the trigger-modes to enable or disable a trigger when you create it.

You can create triggers in the following modes.

You must observe the following rules when you specify the mode for a trigger in the CREATE TRIGGER statement:

• If you do not specify a mode, the trigger is enabled by default.
• You can use the SET Database Object Mode statement to switch the mode of a disabled trigger to the enabled mode. Once the trigger is re-enabled, the database server executes the triggered action whenever the trigger event takes place. However, the re-enabled trigger does not perform retroactively. The database server does not attempt to execute the trigger for rows that were inserted, deleted, or updated after the trigger was disabled and before it was enabled; therefore, be cautious about disabling a trigger. If disabling a trigger will eventually destroy the semantic integrity of the database, do not disable the trigger.
• You cannot create a trigger on a violations table or a diagnostics table.

Related statements: DROP TRIGGER, CREATE PROCEDURE, EXECUTE PROCEDURE, and SET Database Object Mode

For a task-oriented discussion of triggers, see the Informix Guide to SQL: Tutorial.

For performance implications of triggers, see your Performance Guide.