INSTEAD OF Triggers on Views

Use INSTEAD OF triggers to define a specified action for the database server to perform when a trigger on a view is activated, rather than execute the triggering INSERT, DELETE, MERGE, or UPDATE statement.

Syntax

^{(explicit id iot002)
iot002} CREATE TRIGGERtrigger INSTEAD OF <Trigger on a View> { ENABLED | [DISABLED] }

Trigger on a View

{ INSERT ONview [ REFERENCING NEW [ AS ] correlation ] | DELETE ONview [ REFERENCINGOLD [ AS ] correlation ] | UPDATE ONview [ { REFERENCINGOLD [ AS ] correlation [ NEW [AS] correlation ] | REFERENCING NEW [ AS ] correlation [ OLD [ AS ] correlation ] } ] }

FOR EACH ROW

<INSTEAD OF Triggered Action>^[]


Element	Description	Restrictions	Syntax
correlation	Name that you declare here to qualify an old or new column value (as correlation.column) in a triggered action	Must be unique in this statement	Identifier
trigger	Name declared here for the trigger	Must be unique among the names of triggers in the database	Identifier
view	Name or synonym of the triggering view. Can include owner. qualifier.	The view or synonym must exist in the current database	Identifier

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.

With Informix®, the same REFERENCING OLD and REFERENCING NEW syntax is supported in the CREATE FUNCTION and CREATE PROCEDURE statements for defining correlation names in trigger routines. A trigger routine can be invoked in the Action clause for INSTEAD OF triggers on the view that is specified in the FOR clause of the CREATE FUNCTION or CREATE PROCEDURE statement that defines the trigger routine.

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.

Example

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.

¹ See The Action Clause of INSTEAD OF Triggers