Using an INSTEAD OF Trigger to Update on a View

After you create one or more tables (like those named dept and emp in the following example), and then created a view (like the one named manager_info) from dept and emp, you can use an INSTEAD OF trigger to update that view.

The following CREATE TRIGGER statement creates manager_info_update, an INSTEAD OF trigger that is designed to update rows within the dept and emp tables through the manager_info view.

CREATE TRIGGER manager_info_update
   INSTEAD OF UPDATE ON manager_info 
      REFERENCING NEW AS n 
   FOR EACH ROW 
      (EXECUTE PROCEDURE updtab (n.empno, n.empname, n.deptno,));

CREATE PROCEDURE updtab (eno INT, ename CHAR(20), dno INT,)
   DEFINE deptcode INT;
   UPDATE dept SET manager_num = eno where deptno = dno;
   SELECT deptno INTO deptcode FROM emp WHERE empno = eno; 
   IF dno !=deptcode THEN
      UPDATE emp SET deptno = dno WHERE empno = eno;
   END IF;
   END PROCEDURE;

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

UPDATE manager_info
   SET empno = 3666, empname = "Steve"
   WHERE deptno = 01;

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