Inserción, actualización y supresión de datos en vistas utilizando desencadenantes INSTEAD OF

Los desencadenantes INSTEAD OF son desencadenantes que se ejecutan en lugar de la sentencia INSERT, UPDATE o DELETE que activa el desencadenante. Solamente se pueden definir estos desencadenantes para vistas. Utilice los desencadenantes INSTEAD OF para insertar, actualizar y suprimir datos en vistas complejas.

Acerca de esta tarea

Las vistas complejas son aquellas vistas que se definen en expresiones o en varias tablas. En algunos casos, esas opiniones son de solo lectura. En estos casos, los activadores INSTEAD OF hacen posibles las operaciones de inserción, actualización y eliminación. Si la vista compleja no es de solo lectura, puede solicitar una operación de inserción, actualización o eliminación. Sin embargo, Db2 decide automáticamente cómo realizar esa operación en las tablas base a las que se hace referencia en la vista. Con los desencadenadores INSTEAD OF, puede definir exactamente cómo debe ejecutarse un Db2 a una operación de inserción, actualización o eliminación en la vista. Ya no dejas la decisión a Db2.

Procedimiento

Para insertar, actualizar o eliminar datos en una vista utilizando activadores INSTEAD OF:

  1. Defina uno o más activadores INSTEAD OF en la vista mediante una instrucción CREATE TRIGGER.

    Puede crear un desencadenante para cada una de las siguientes operaciones: INSERT, UPDATE y DELETE. Estos desencadenantes definen la acción que debe realizar Db2 para cada una de estas operaciones.

  2. Enviar una declaración de INSERTAR, ACTUALIZAR o ELIMINAR en la vista.

    Db2 ejecuta el desencadenante apropiado INSTEAD OF.

Ejemplo

Supongamos que crea la siguiente vista en las tablas de muestra DSN8C10.EMP y DSN8C10.DEPT :

CREATE VIEW EMPV (EMPNO, FIRSTNME, MIDINIT, LASTNAME, PHONENO, HIREDATE,DEPTNAME)
  AS SELECT EMPNO, FIRSTNME, MIDINIT, LASTNAME, PHONENO, HIREDATE, DEPTNAME
     FROM DSN8C10.EMP, DSN8C10.DEPT WHERE DSN8C10.EMP.WORKDEPT 
          = DSN8C10.DEPT.DEPTNO

Supongamos que también define los siguientes tres desencadenantes INSTEAD OF:

CREATE TRIGGER EMPV_INSERT INSTEAD OF INSERT ON EMPV
REFERENCING NEW AS NEWEMP
FOR EACH ROW MODE DB2SQL
  INSERT INTO DSN8C10.EMP (EMPNO, FIRSTNME, MIDINIT, LASTNAME, WORKDEPT, 
                           PHONENO, HIREDATE)
    VALUES(NEWEMP.EMPNO, NEWEMP.FIRSTNME, NEWEMP.MIDINIT, NEWEMP.LASTNAME,
       COALESCE((SELECT D.DEPTNO FROM DSN8C10.DEPT AS D
                   WHERE D.DEPTNAME = NEWEMP.DEPTNAME),
                RAISE_ERROR('70001', 'Unknown department name')),
       NEWEMP.PHONENO, NEWEMP.HIREDATE)

CREATE TRIGGER EMPV_UPDATE INSTEAD OF UPDATE ON EMPV
REFERENCING NEW AS NEWEMP OLD AS OLDEMP
FOR EACH ROW MODE DB2SQL
BEGIN ATOMIC
  UPDATE DSN8C10.EMP AS E
   SET (E.FIRSTNME, E.MIDINIT, E.LASTNAME, E.WORKDEPT, E.PHONENO, 
        E.HIREDATE)
       = (NEWEMP.FIRSTNME, NEWEMP.MIDINIT, NEWEMP.LASTNAME,
       COALESCE((SELECT D.DEPTNO FROM DSN8C10.DEPT AS D
                  WHERE D.DEPTNAME = OLDEMP.DEPTNAME),
                RAISE_ERROR ('70001', 'Unknown department name'))
       NEWEMP.PHONENO, NEWEMP.HIREDATE)
   WHERE NEWEMP.EMPNO = E.EMPNO;
  UPDATE DSN8C10.DEPT D SET D.DEPTNAME=NEWEMP.DEPTNAME
   WHERE D.DEPTNAME=OLDEMP.DEPTNAME;
END

CREATE TRIGGER EMPV_DELETE INSTEAD OF DELETE ON EMPV
REFERENCING OLD AS OLDEMP
FOR EACH ROW MODE DB2SQL
  DELETE FROM DSN8C10.EMP AS E WHERE E.EMPNO = OLDEMP.EMPNO

Debido a que la vista está en una consulta con una combinación interna, la vista es de solo lectura. Sin embargo, los activadores INSTEAD OF hacen posibles las operaciones de inserción, actualización y eliminación.

La siguiente tabla describe lo que ocurre con las distintas operaciones de inserción, actualización y eliminación en la vista EMPV.

Tabla 1. Resultados de los desencadenantes INSTEAD OF
Sentencia SQL Result 
INSERT INTO EMPV VALUES (...)
 El activador EMPV_INSERT está activado. Este activador inserta la fila en la tabla base DSN8C10.EMP si el nombre del departamento coincide con un valor de la columna WORKDEPT de la tabla DSN8C10.DEPT. De lo contrario, se emite un error. Si se hubiera utilizado una consulta en lugar de una cláusula VALUES en la instrucción INSERT, el cuerpo del desencadenador se procesaría para cada fila de la consulta. 
UPDATE EMPV 
   SET DEPTNAME='PLANNING & STRATEGY'
   WHERE DEPTNAME='PLANNING'
 Se activa el desencadenante EMPV_UPDATE. Este desencadenante actualiza la columna NOMDEP en el DSN8C10.DEPT para las filas que cumplan los requisitos. 
DELETE FROM EMPV
  WHERE HIREDATE<'1910-01-01'
 Se activa el desencadenante EMPV_DELETE. Este desencadenante elimina las filas que cumplen los requisitos de la tabla DSN8C10.EMP.