Informix Guide to SQL: Syntax

Informix Guide to SQL: Syntax
Chapter 1: SQL Statements

Home Contents Index Master Index New Book

SQL Statements

CREATE TRIGGER

Use the CREATE TRIGGER statement to create a trigger on a table in the database. A trigger is a database object that automatically sets off a specified set of SQL statements when a specified event occurs.

Usage

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

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.

You can define a trigger with a stand-alone CREATE TRIGGER statement.

DB

You can define a trigger as part of a schema by placing the CREATE TRIGGER statement inside a CREATE SCHEMA statement.

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 a stored procedure if the procedure is called inside a data manipulation statement. For example, you cannot create a trigger inside the stored procedure sp_items in the following INSERT statement:

INSERT INTO items EXECUTE PROCEDURE sp_items

See "Data Manipulation Statements" for a list of data manipulation statements.

ESQL

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

You cannot use a stored procedure variable in a CREATE TRIGGER statement.

Trigger Event

The trigger event specifies the type of statement that activates a trigger. The trigger event can be an INSERT, DELETE, or UPDATE 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. See "UPDATE Clause" for more information about multiple triggers on the same table.

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. See "Action Clause" and "Triggered Action List" for more information on the behavior of triggered actions.

If Universal Server is the database server, 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 stores7@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 stores7@dbserver1:newtab

	SELECT item_num, order_num, quantity, stock_num, 
manu_code,

	total_price FROM items;

Trigger Name

Element Purpose Restrictions Syntax
owner
The user name of the owner of the trigger
The specified name must be a valid user name.
Identifier, p. 1-966

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

ANSI

When you create a trigger, the owner.name combination (the combination of the owner name and trigger name) must be unique within a database.

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

UPDATE Clause

Element Purpose Restrictions Syntax
column name
The name of a column or columns that activate the trigger. The default is all the columns in the table on which you create the trigger.
The specified columns must belong to the table on which you create the trigger. If you define more than one update trigger on a table, the column lists of the triggering statements must be mutually exclusive.
Identifier, p. 1-966

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

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

Defining Multiple Update Triggers

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 Activates Multiple Triggers

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 the following example shows:

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).

Action Clause

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. See "Action Clause Referencing" for more information on the action clause when a REFERENCING clause is present.

BEFORE Actions

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.

FOR EACH ROW Actions

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, or update any rows, the FOR EACH ROW triggered actions do not execute.

AFTER Actions

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.

Actions of Multiple Triggers

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

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, 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.

Guaranteeing Row-Order Independence

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.

INSERT REFERENCING Clause

Element Purpose Restrictions Syntax
correlation name
A name that you assign to a new column value so that you can refer to it within the triggered action. The new column value in the triggering table is the value of the column after execution of the triggering statement.
The correlation name must be unique within the `CREATE TRIGGER` statement.
Identifier, p. 1-966

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 the use of 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.

DELETE REFERENCING Clause

Element Purpose Restrictions Syntax
correlation name
A name that you assign to an old column value so that you can refer to it within the triggered action. The old column value in the triggering table is the value of the column before execution of the triggering statement.
The correlation name must be unique within the `CREATE TRIGGER` statement.
Identifier, p. 1-966

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 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. See "Using Correlation Names in Triggered Actions" for the rules governing the use of correlation names.

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

UPDATE REFERENCING Clause

Element Purpose Restrictions Syntax
correlation name
A name that you assign to an old or new column value so that you can refer to it within the triggered action. The old column value in the triggering table is the value of the column before execution of the triggering statement. The new column value in the triggering table is the value of the column after the statement executes.
You can specify a correlation name for an old column value only (`OLD` option), for a new column value only (`NEW` option), or for both the old and new column values. Each correlation name you specify must be unique within the `CREATE TRIGGER` statement.
Identifier, p. 1-966

After 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. See "Using Correlation Names in Triggered Actions" for the rules that govern the use of correlation names.

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

Action Clause Referencing

If the CREATE TRIGGER statement contains an INSERT REFERENCING clause, a DELETE REFERENCING clause, or an UPDATE 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. See "Action Clause" for information on the BEFORE, FOR EACH ROW, and AFTER triggered-action lists.

Triggered Action List

The triggered action consists of an optional WHEN condition and the action statements. Objects that are referenced in the triggered action, that is, tables, columns, and stored procedures, must exist when the CREATE TRIGGER statement is executed. This rule applies only to 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: Syntax" 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))

A routine that executes inside the WHEN condition carries the same restrictions as a routine that is called in a data manipulation statement. See the Extending INFORMIX-Universal Server: User-Defined Routines manual for more information about a routine that is called within a data manipulation statement.

Action Statements

The triggered-action statements can be INSERT, DELETE, UPDATE, 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.

Achieving a Consistent Result

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 in a multiple-row INSERT statement

Using Keywords

If you use the INSERT, DELETE, UPDATE, or EXECUTE keywords 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 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)

Using Correlation Names in Triggered Actions

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 routine that is a triggered action.

When to Use Correlation Names

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 nonqualified 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.

Qualified Versus Unqualified Value

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.


Trigger Event	old.col	new.col
`INSERT`	no value (error)	inserted value
`UPDATE` (column updated)	original value	current value (N)
`UPDATE` (column not updated)	original value	current value (U)
`DELETE`	original value	no value (error)

Refer to the following key when you read the table.

Term Meaning
original value

is the value before the triggering statement.

current value

is the value after the triggering statement.

(N)

cannot be changed by triggered action.

(U)

can be updated by triggered statements; value may be different from original value because of preceding triggered actions.

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.

Action on the Triggering Table

You cannot reference the triggering table in any triggered SQL statement, with the following exceptions:

The trigger event is UPDATE and the triggered SQL statement is also UPDATE, and the columns in both statements, including any nontriggering columns in the triggering UPDATE, are mutually exclusive.

UPDATE

tab1

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

UPDATE

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

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

The triggered SQL statement is a SELECT statement. 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 stored procedure, and the SELECT statement appears inside the stored procedure.

This rule, which states that a triggered SQL statement cannot reference the triggering table, with the two noted exceptions, applies recursively to all cascading triggers, which are considered part of the initial trigger. This situation 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. See "Cascading Triggers" for more information about cascading triggers.

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);

Rules for Procedures

The following rules apply to a procedure that is used as a triggered action:

The routine cannot be a cursory procedure (that is, a procedure that returns more than one row) in a place where only one row is expected.
When an EXECUTE PROCEDURE statement is the triggered action, you can specify the INTO clause only for an UPDATE trigger 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)

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

"EXECUTE PROCEDURE"

You cannot use the old or new correlation name inside the routine. If you need to use the corresponding values in the procedure, 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.
You cannot use the following statements inside the routine: ALTER FRAGMENT, ALTER INDEX, ALTER OPTICAL, ALTER TABLE, BEGIN WORK, COMMIT WORK, CREATE TRIGGER, DELETE, DROP INDEX, DROP OPTICAL, DROP SYNONYM, DROP TABLE, DROP TRIGGER, DROP VIEW, INSERT, RENAME COLUMN, RENAME TABLE, ROLLBACK WORK, SET CONSTRAINTS, and UPDATE.

When you use a procedure as a triggered action, the objects that it references are not checked until the procedure is executed.

Privileges to Execute Triggered Actions

If you are not the trigger owner, but the trigger owner's privileges include the WITH GRANT OPTION privilege, you inherit the owner's privileges 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 an SPL or external routine, you must have the Execute privilege on the routine or the owner of the trigger must have the Execute privilege and the WITH GRANT OPTION privilege.

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

1. Privileges for a DBA routine

CREATE DBA

2. Privileges for a routine without DBA restrictions

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

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

For more information on privileges on routines, see Chapter 14 in the Informix Guide to SQL: Tutorial.

Creating a Triggered Action That Anyone Can Use

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 procedure and grant you the Execute privilege with the WITH GRANT OPTION right. You then use the DBA-privileged procedure as the triggered action. Anyone can execute the triggered action because the DBA-privileged procedure carries the WITH GRANT OPTION right. When you activate the procedure, the database server applies privilege-checking rules for a DBA. For more information about privileges on stored procedures, see Chapter 14 of the Informix Guide to SQL: Tutorial.

Cascading Triggers

The database server allows triggers to cascade, meaning that the triggered actions of one trigger can activate another trigger. 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 stores7 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 stored procedure 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 would prohibit the triggers in this example from executing. When you use INFORMIX-Universal Server with logging, however, the triggers execute successfully because constraint checking is deferred until all the triggered actions are complete, including the actions of cascading triggers. See "Constraint Checking" for more information about how constraints are handled when triggers execute.

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.

Constraint Checking

When you use logging, INFORMIX-Universal Server defers constraint checking on the triggering statement until after the statements in the triggered-action list execute. Universal Server effectively executes a SET statement (SET CONSTRAINTS ALL DEFERRED) before it executes the triggering statement. After the triggered action is completed, it effectively executes another SET statement (SET CONSTRAINTS constr_name IMMEDIATE) 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 the SET statement on page 1-647.

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, Universal 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.

Universal Server does not allow the SET statement in a triggered action. Universal Server checks this restriction when you activate a trigger because the statement could occur inside a stored procedure.

Preventing Triggers from Overriding Each Other

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.

Client/Server Environment

In an Universal Server database, 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 stores7@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 stores7@dbserver3:items 

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

	AND manu_code = post.mc);



-- Triggering statement from dbserver2:



UPDATE stores7@dbserver1:newtab 

	SET qty = qty * 2 WHERE s_num = 5 

	AND mc = 'ANZ';

Logging and Recovery

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.

In INFORMIX-Universal Server, 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 stored procedure as a triggered action, if you terminate the procedure 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 procedure is executed outside a triggered action, the insert is not rolled back.

The stored procedure 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 stored procedure.

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.

Trigger Object Modes

The Trigger Object Modes option allows you to create a trigger in either the enabled or disabled object mode.

You can create triggers in the following object modes.

Object Mode Effect
disabled
When a trigger is created in disabled mode, the database server does not execute the triggered action when the trigger event (an insert, delete, or update operation) takes place. In effect, the database server ignores the trigger even though its catalog information is maintained.

enabled
When a trigger is created in enabled mode, the database server executes the triggered action when the trigger event (an insert, delete, or update operation) takes place.

Specifying Object Modes for Triggers

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

If you do not specify the disabled or enabled object modes explicitly, the default object mode is enabled.
In contrast to unique indexes and constraints of all types, you cannot set triggers to the filtering object mode because a trigger does not impose any type of data-integrity requirement on the tables in the database.
You can use the SET statement to switch the mode of a disabled trigger to the enabled mode. Once the trigger has been 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 in the first place.
You cannot create a trigger on a violations table or a diagnostics table.

References

See the DROP TRIGGER, CREATE PROCEDURE, and EXECUTE PROCEDURE statements in this manual.

In the Informix Guide to SQL: Tutorial, see Chapter 14 for information about stored procedures.

CREATE VIEW

Use the CREATE VIEW statement to create a new view that is based upon existing tables and views in the database.

Syntax

Element Purpose Restrictions Syntax
row type name
The name of a named row type that you use to specify the type of a typed view
You must have `USAGE` privileges on the named row type or be its owner or the `DBA`. The named row type must exist before you can assign it to a view.
Data Type, p. 1-859

column name
The name of a column in the view being created
See "Naming View Columns".
Identifier, p. 1-966

Usage

You can create typed or untyped views. If you omit the OF TYPE clause, the rows in the view are considered to be untyped and default to an unnamed row type.

Typed views, like typed tables, are based on a named row type. Each column in the view corresponds to a field in the named row type.

You can use a view in any SQL statement where you can use a table, except the following.

ALTER FRAGMENT
DROP TABLE

ALTER INDEX
DROP TRIGGER

ALTER TABLE
LOCK TABLE

CREATE INDEX
RECOVER TABLE

CREATE TABLE
RENAME TABLE

CREATE TRIGGER
UNLOCK TABLE

DROP INDEX

The view behaves like a table that is called view name. It consists of the set of rows and columns that the SELECT statement returns each time the SELECT statement is executed by using the view. The view reflects changes to the underlying tables with one exception. If a SELECT * clause defines the view, the view has only the columns in the underlying tables at the time the view is created. New columns that are subsequently added to the underlying tables with the ALTER TABLE statement do not appear in the view.

The view name must be unique; that is, a view name cannot have the same name as another database object, such as a table, synonym, or temporary table.

The view inherits the data types of the columns from the tables from which they come. Data types of virtual columns are determined from the nature of the expression.

To create a view, you must have the Select privilege on all columns from which the view is derived.

The SELECT statement is stored in the sysviews system catalog table. When you subsequently refer to a view in another statement, the database server performs the defining SELECT statement while it executes the new statement.

You cannot create a view on a temporary table.

DB

If you create a view outside the CREATE SCHEMA statement, you receive warnings if you use the -ansi flag or set DBANSIWARN.

Subset of a SELECT Allowed in CREATE VIEW

The SELECT statement has the form that is described on page 1-596, but in CREATE VIEW, it cannot have an ORDER BY clause, INTO TEMP clause, or UNION operator. Do not use display labels in the select list; display labels are interpreted as column names.

Naming View Columns

The number of columns that you specify in the column name parameter must match the number of columns returned by the SELECT statement that defines the view.

If you do not specify a list of columns, the view inherits the column names of the underlying tables. In the following example, the view herostock has the same column names as the ones in the SELECT statement:

CREATE VIEW herostock AS

	SELECT stock_num, description, unit_price, unit, unit_descr

		FROM stock WHERE manu_code = 'HRO'

If the SELECT statement returns an expression, the corresponding column in the view is called a virtual column. You must provide a name for virtual columns. You must also provide a column name in cases where the selected columns have duplicate column names when the table prefixes are stripped. For example, when both orders.order_num and items.order_num appear in the SELECT statement, you must provide two separate column names to label them in the CREATE VIEW statement, as the following example shows:

CREATE VIEW someorders (custnum,ocustnum,newprice) AS 

	SELECT orders.order_num,items.order_num,

			items.total_price*1.5

		FROM orders, items

		WHERE orders.order_num = items.order_num

		AND items.total_price > 100.00

If you must provide names for some of the columns in a view, then you must provide names for all the columns; that is, the column list must contain an entry for every column that appears in the view.

Using a View in the SELECT Statement

You can define a view in terms of other views, but you must abide by the restrictions on creating views that are listed in Chapter 11 of the Informix Guide to SQL: Tutorial. See that manual for further information.

WITH CHECK OPTION Keywords

The WITH CHECK OPTION keywords instruct the database server to ensure that all modifications that are made through the view to the underlying tables satisfy the definition of the view.

The following example creates a view that is named palo_alto, which uses all the information in the customer table for customers in the city of Palo Alto. The database server checks any modifications made to the customer table through palo_alto because the WITH CHECK OPTION is specified.

CREATE  VIEW  palo_alto  AS

	SELECT  *  FROM  customer

		WHERE  city  =  'Palo  Alto'

		WITH  CHECK OPTION

What do the WITH CHECK OPTION keywords really check and prevent? It is possible to insert into a view a row that does not satisfy the conditions of the view (that is, a row that is not visible through the view). It is also possible to update a row of a view so that it no longer satisfies the conditions of the view. For example, if the view was created without the WITH CHECK OPTION keywords, you could insert a row through the view where the city is Los Altos, or you could update a row through the view by changing the city from Palo Alto to Los Altos.

To prevent such inserts and updates, you can add the WITH CHECK OPTION keywords when you create the view. These keywords ask the database server to test every inserted or updated row to ensure that it meets the conditions that are set by the WHERE clause of the view. The database server rejects the operation with an error if the row does not meet the conditions.

However, even if the view was created with the WITH CHECK OPTION keywords, you can perform inserts and updates through the view to change columns that are not part of the view definition. A column is not part of the view definition if it does not appear in the WHERE clause of the SELECT statement that defines the view.

Updating Through Views

If a view is built on a single table, the view is updatable if the SELECT statement that defined it did not contain any of the following items:

Columns in the select list that are aggregate values
Columns in the select list that use the UNIQUE or DISTINCT keyword
A GROUP BY clause
A derived value for a column, which was created using an arithmetical expression

In an updatable view, you can update the values in the underlying table by inserting values into the view.

Important: You cannot update or insert rows in a remote table through views with check options.

Examples

The following statement creates a view that is based on the person table. When you create a view without an OF TYPE clause, the view is referred to as an untyped view.

CREATE VIEW v1 AS SELECT * 
FROM person

The following statement creates a typed view that is based on the table person. To create a typed view, you must include an OF TYPE clause. When you create a typed view, the named row type that you specify immediately after the OF TYPE keywords must already exist.

CREATE VIEW v2 OF TYPE person_t AS SELECT * 
FROM person

For more information about how to create and use typed views, see Chapter 11 of the Informix Guide to SQL: Tutorial.

References

See the CREATE TABLE, DROP VIEW, GRANT, SELECT, and SET SESSION AUTHORIZATION statements in this manual.

In the Informix Guide to SQL: Tutorial, see the discussions of views and security in Chapter 11. Also, see the discussion of named row types in Chapter 10.

Informix Guide to SQL: SyntaxChapter 1: SQL Statements Home Contents Index Master Index New Book

SQL Statements

CREATE TRIGGER

Usage

Trigger Events with Cursors

Trigger Name Element Purpose Restrictions Syntax owner The user name of the owner of the trigger The specified name must be a valid user name. Identifier, p. 1-966

When an UPDATE Statement Activates Multiple Triggers

Using Keywords

INTO clause of an EXECUTE PROCEDURE statement

GROUP BY clause of a SELECT statement

SET clause of an UPDATE statement

Using Correlation Names in Triggered Actions

When to Use Correlation Names

Rules for Procedures

Client/Server Environment

Logging and Recovery

References

Usage

Informix Guide to SQL: Syntax
Chapter 1: SQL Statements

Home Contents Index Master Index New Book

Trigger Name

Element Purpose Restrictions Syntax
owner
The user name of the owner of the trigger
The specified name must be a valid user name.
Identifier, p. 1-966