¿Cómo implementar y usar INSTEAD OF Triggers en Oracle?

En este tutorial, te enseñaremos cómo implementar y usar los desencadenadores INSTEAD OF en Oracle. Los desencadenadores INSTEAD OF son herramientas poderosas que te permiten insertar, actualizar o eliminar datos en tablas a través de vistas no actualizables. Esto es especialmente útil cuando tienes una vista que no se puede modificar directamente mediante declaraciones DML y necesitas realizar cambios en las tablas subyacentes. Aprenderás cómo crear un desencadenador INSTEAD OF, cómo funciona y cómo usarlo para insertar datos en tablas mediante una vista no actualizable.

¿Qué es un desencadenador INSTEAD OF en Oracle?

Un desencadenador INSTEAD OF en Oracle es un tipo de desencadenador que te permite actualizar datos en tablas a través de una vista no actualizable. Cuando emites una declaración DML (INSERTAR, ACTUALIZAR o ELIMINAR) en una vista no actualizable, Oracle emite un error. Sin embargo, al utilizar un desencadenador INSTEAD OF, puedes omitir esta restricción y ejecutar otras declaraciones DML en su lugar.

Es importante tener en cuenta que un desencadenador INSTEAD OF se dispara por cada fila de la vista que se modifica. Esto significa que si hay múltiples filas en la vista que se están actualizando, el desencadenador INSTEAD OF se ejecutará para cada una de ellas.

Otra consideración importante es que solo se puede crear un desencadenador INSTEAD OF por vista. No es posible crear un desencadenador INSTEAD OF para una tabla en sí.

A continuación, veremos la sintaxis para crear un desencadenador INSTEAD OF en Oracle:

CREATE [OR REPLACE] TRIGGER nombre_del_desencadenador
INSTEAD OF {INSERTAR | ACTUALIZAR | ELIMINAR}
EN nombre_de_la_vista
PARA CADA FILA
INICIO
  -- Código del desencadenador
EXCEPCIÓN
  -- Manejo de excepciones
FIN;

En la sección del código del desencadenador, puedes incluir las declaraciones DML necesarias para realizar las actualizaciones deseadas en las tablas subyacentes.

Creación de un desencadenador INSTEAD OF

Para ilustrar cómo se crea un desencadenador INSTEAD OF en Oracle, consideremos un ejemplo utilizando las tablas «customers» y «contacts» de una base de datos de muestra.

Primero, crearemos una vista basada en estas tablas:

CREATE VIEW customer_contacts AS
SELECT c.customer_id, c.first_name, c.last_name, ct.phone_number
FROM customers c
JOIN contacts ct ON c.customer_id = ct.customer_id;

Una vez que la vista esté creada, intentaremos insertar un nuevo cliente y contacto en las tablas subyacentes a través de la vista. Sin embargo, Oracle emitirá un error debido a que la vista no es actualizable:

INSERT INTO customer_contacts(customer_id, first_name, last_name, phone_number)
VALUES(10001, 'John', 'Doe', '555-1234');

El error que recibiremos será similar a:

ORA-01779: no se puede modificar una columna que pertenezca a una clave definida por el sistema

Para solucionar este problema y permitir la inserción de datos a través de la vista, crearemos un desencadenador INSTEAD OF en la vista:

CREATE OR REPLACE TRIGGER customer_contacts_trigger
INSTEAD OF INSERT
ON customer_contacts
FOR EACH ROW
BEGIN
  -- Código del desencadenador
END;

Dentro del bloque del desencadenador, puedes incluir el código necesario para insertar los datos en las tablas «customers» y «contacts». Por ejemplo:

CREATE OR REPLACE TRIGGER customer_contacts_trigger
INSTEAD OF INSERT
ON customer_contacts
FOR EACH ROW
BEGIN
  INSERT INTO customers(customer_id, first_name, last_name)
  VALUES(:NEW.customer_id, :NEW.first_name, :NEW.last_name);

  INSERT INTO contacts(customer_id, phone_number)
  VALUES(:NEW.customer_id, :NEW.phone_number);

Una vez que el desencadenador esté creado, puedes volver a ejecutar la declaración de inserción y verificar que los datos se hayan insertado correctamente en la tabla «customers».

Recuerda que este es solo un ejemplo básico de cómo se crea un desencadenador INSTEAD OF en Oracle. Dependiendo de tus necesidades, es posible que debas llevar a cabo más acciones en el desencadenador para manejar actualizaciones más complejas en las tablas subyacentes.

Ejemplo de uso en Oracle

Ahora que entendemos cómo se crea un desencadenador INSTEAD OF en Oracle, veamos un ejemplo de su uso en un escenario real.

Supongamos que tenemos las siguientes tablas en nuestra base de datos:

CREATE TABLE employees (
  employee_id   NUMBER,
  first_name    VARCHAR2(50),
  last_name     VARCHAR2(50)
);

CREATE TABLE salaries (
  employee_id   NUMBER,
  salary        NUMBER
);

Y creamos una vista llamada «employee_info» que combina información de ambas tablas:

CREATE VIEW employee_info AS
SELECT e.employee_id, e.first_name, e.last_name, s.salary
FROM employees e
JOIN salaries s ON e.employee_id = s.employee_id;

Si intentamos insertar datos en la vista usando una declaración INSERT, Oracle generará un error debido a que la vista no es actualizable directamente:

INSERT INTO employee_info (employee_id, first_name, last_name, salary)
VALUES (1001, 'John', 'Doe', 5000);

Para permitir la inserción de datos en las tablas subyacentes a través de la vista, crearemos un desencadenador INSTEAD OF en la vista:

CREATE OR REPLACE TRIGGER employee_info_trigger
INSTEAD OF INSERT
ON employee_info
FOR EACH ROW
BEGIN
  -- Insertar datos en la tabla employees
  INSERT INTO employees (employee_id, first_name, last_name)
  VALUES (:NEW.employee_id, :NEW.first_name, :NEW.last_name);

  -- Insertar datos en la tabla salaries
  INSERT INTO salaries (employee_id, salary)
  VALUES (:NEW.employee_id, :NEW.salary);
END;

Una vez que el desencadenador INSTEAD OF esté creado, podemos volver a ejecutar la declaración INSERT en la vista y se insertarán los datos en las tablas «employees» y «salaries».

Puedes verificar la inserción de los datos ejecutando consultas en las tablas:

SELECT * FROM employees;
SELECT * FROM salaries;

Al usar un desencadenador INSTEAD OF en una vista no actualizable, podemos realizar inserciones, actualizaciones o eliminaciones en las tablas subyacentes a través de la vista. Esto ofrece una mayor flexibilidad y control en la manipulación de datos en Oracle.

Verificación de la inserción de datos

Una vez que hayas ejecutado la declaración de inserción a través de la vista utilizando el desencadenador INSTEAD OF, es importante verificar si los datos se insertaron correctamente en las tablas subyacentes. Para hacer esto, puedes ejecutar consultas en las tablas correspondientes y confirmar que los registros se hayan agregado correctamente.

Por ejemplo, si hemos utilizado la declaración INSERT en el ejemplo anterior para insertar un nuevo empleado en la vista «employee_info», podemos verificar la inserción ejecutando las siguientes consultas:

SELECT * FROM employees;
SELECT * FROM salaries;

La primera consulta mostrará todos los empleados en la tabla «employees», y la segunda consulta mostrará todos los salarios en la tabla «salaries». Si el desencadenador INSTEAD OF se ejecutó correctamente, deberías ver el nuevo empleado y el salario correspondiente en las tablas.

Por ejemplo:

+-------------+------------+-----------+
| employee_id | first_name | last_name |
+-------------+------------+-----------+
|    1001     |    John    |    Doe    |
+-------------+------------+-----------+

+-------------+--------+
| employee_id | salary |
+-------------+--------+
|    1001     |  5000  |
+-------------+--------+

Si los datos no se insertaron correctamente, verifica el código del desencadenador INSTEAD OF para asegurarte de que esté correctamente escrito y que las tablas y columnas especificadas sean correctas.

Recuerda que el desencadenador INSTEAD OF se ejecuta por cada fila que se modifique en la vista, por lo que si estás insertando múltiples registros a través de la vista, asegúrate de verificar todas las filas correspondientes en las tablas subyacentes.

Para verificar la inserción de datos después de usar un desencadenador INSTEAD OF en Oracle, debes ejecutar consultas en las tablas subyacentes para confirmar que los registros se hayan insertado correctamente.