INSTEAD OF Triggers on Views (IDS)

Use INSTEAD OF triggers to perform a specified trigger action on a view, rather than execute the triggering INSERT, DELETE, or UPDATE event.

>>-CREATE TRIGGER--trigger--INSTEAD OF--| Trigger on a View |--->
 
   .-ENABLED--.
>--+-DISABLED-+------------------------------------------------><
 
Trigger on a View:
 
|--+-INSERT ON--view--+------------------------------+-----------------------+-->
   |                  '-REFERENCING NEW--+----+--new-'                       |
   |                                     '-AS-'                              |
   +-DELETE ON--view--+-------------------------------+----------------------+
   |                  '-REFERENCING--OLD--+----+--old-'                      |
   |                                      '-AS-'                             |
   '-UPDATE ON--view--+----------------------------------------------------+-'
                      +-REFERENCING--OLD--+----+--old----------------------+
                      |                   '-AS-'                           |
                      '-REFERENCING NEW--+----+--new--+------------------+-'
                                         '-AS-'       '-OLD--+----+--old-'
                                                             '-AS-'
 
                                                 (1)
>--FOR EACH ROW--| INSTEAD OF Triggered Action |----------------|
 

1. See page The Action Clause of INSTEAD OF Triggers (IDS)

You can use the trigger action to update the tables underlying the view, in some cases updating an otherwise "non-updatable" view. You can also use INSTEAD OF triggers to substitute other actions when INSERT, DELETE, or UPDATE statements reference specific columns within the database.

In the optional REFERENCING clause of an INSTEAD OF UPDATE trigger, the new correlation name can appear before or after the old correlation name.

The specified view is sometimes called the triggering view. The left-hand portion of this diagram (including the view specification) defines the trigger event. The rest of the diagram defines correlation names and the trigger action.

The Action Clause of INSTEAD OF Triggers (IDS)

When the trigger event for the specified view is encountered, the SQL statements of the trigger action are executed, instead of the triggering statement. Triggers defined on a view support the following syntax in the action clause.

INSTEAD OF Triggered Action:
 
      .-,----------------------------------------.
      V                        (1)               |
|--(----+-| INSERT Statement |-----------------+-+--)-----------|
        |                      (2)             |
        +-| DELETE Statement |-----------------+
        |                      (3)             |
        +-| UPDATE Statement |-----------------+
        |                                 (4)  |
        +-| EXECUTE PROCEDURE Statement |------+
        |                                (5)   |
        '-| EXECUTE FUNCTION Statement |-------'
 

1. See page INSERT
2. See page DELETE
3. See page UPDATE
4. See page EXECUTE PROCEDURE
5. See page EXECUTE FUNCTION

This is not identical to the syntax of the trigger action for a trigger on a table, as described in the section Triggered-Action List. Because no WHEN (condition) is supported, the same trigger action is executed whenever the INSTEAD OF trigger event is encountered, and only one action list can be specified, rather than a separate list for each condition.

Restrictions on INSTEAD OF Triggers on Views (IDS)

You must be either the owner of the view or have the DBA status to create an INSTEAD OF trigger on a view. The owner of a simple view (based on only one table) has Insert, Update, and Delete privileges. For information about the relationship between the privileges of the trigger owner and the privileges of other users, see Privileges to Execute Trigger Actions.

If multiple tables underlie a view, only the owner of the view can create a trigger, but that owner can grant DML privileges on the view to other users.

An INSTEAD OF trigger defined on a view cannot violate the Restrictions on Triggers and must observe the following additional rules:

• You can define an INSTEAD OF trigger only on a view, not on a table.
• The view must be local to the current database.
• The view cannot be an updatable view WITH CHECK OPTION.
• No SELECT event or WHEN clause is valid in an INSTEAD OF trigger.
• No BEFORE nor AFTER action is valid in an INSTEAD OF trigger.
• No OF column clause is valid in an INSTEAD OF UPDATE trigger.
• Every INSTEAD OF trigger must specify FOR EACH ROW.
• The triggered action cannot include EXECUTE PROCEDURE INTO.

A view can have no more than one INSTEAD OF trigger defined for each type of event (INSERT, DELETE, or UPDATE). It is possible, however, to define a trigger on one or more other views, each with its own INSTEAD OF trigger.

Updating Views

INSERT, DELETE, or UPDATE statements can directly modify a view only if all of the following are true of the SELECT statement that defines the view:

• All of the columns in the view are from a single table.
• No columns in the projection list are aggregate values.
• No UNIQUE or DISTINCT keyword is in the SELECT projection list.
• No GROUP BY clause nor UNION operator is in the view definition.
• The query selects no calculated values and no literal values.

By using INSTEAD OF triggers, however, you can circumvent these restrictions on the view, if the trigger action modifies the base table.

Example of an INSTEAD OF Trigger on a View

Suppose that dept and emp are tables that list departments and employees:

CREATE TABLE dept (
   deptno INTEGER PRIMARY KEY,
   deptname CHAR(20),
   manager_num INT
);
CREATE TABLE emp (
   empno INTEGER PRIMARY KEY,
   empname CHAR(20),
   deptno INTEGER REFERENCES dept(deptno),
   startdate DATE
);
ALTER TABLE dept ADD CONSTRAINT(FOREIGN KEY (manager_num)
      REFERENCES emp(empno));

The next statement defines manager_info, a view of columns in the dept and emp tables that includes all the managers of each department:

CREATE VIEW manager_info AS
   SELECT d.deptno, d.deptname, e.empno, e.empname
      FROM emp e, dept d WHERE e.empno = d.manager_num;

The following CREATE TRIGGER statement creates manager_info_insert, an INSTEAD OF trigger that is designed to insert rows into the dept and emp tables through the manager_info view:

CREATE TRIGGER manager_info_insert
   INSTEAD OF INSERT ON manager_info    --defines trigger event
      REFERENCING NEW AS n              --new manager data
   FOR EACH ROW                         --defines trigger action
      (EXECUTE PROCEDURE instab(n.deptno, n.empno));

CREATE PROCEDURE instab (dno INT, eno INT)
   INSERT INTO dept(deptno, manager_num) VALUES(dno, eno);
   INSERT INTO emp (empno, deptno) VALUES (eno, dno);
END PROCEDURE;

After the tables, view, trigger, and SPL routine have been created, the database server treats the following INSERT statement as a triggering event:

INSERT INTO manager_info(deptno, empno) VALUES (08, 4232);

This triggering INSERT statement is not executed, but this event causes the trigger action to be executed instead, invoking the instab( ) SPL routine. The INSERT statements in the SPL routine insert new values into both the emp and dept base tables of the manager_info view.

Related Information

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

For a task-oriented discussion of triggers, and for examples of INSTEAD OF DELETE (and UPDATE) triggers on views, see the IBM Informix Guide to SQL: Tutorial. For performance implications of triggers, see your IBM Informix Performance Guide.