Oracle Disparador PL/SQL: en lugar de tipos compuestos

¿Qué es el disparador en PL/SQL?

Los disparadores se almacenan PL / SQL programas que son activados por el Oracle motor automáticamente cuando Declaraciones DML Las operaciones como insertar, actualizar y eliminar se ejecutan en la tabla, o cuando ocurren ciertos eventos. El código que se ejecuta en el caso de un disparador se puede definir según los requisitos. Se puede elegir el evento que activará el disparador y el momento de su ejecución. El propósito de un disparador es mantener la integridad de la información en la base de datos.

Beneficios de los desencadenantes

A continuación se presentan los beneficios de los desencadenantes.

• Generar algunos valores de columna derivados automáticamente
• Hacer cumplir la integridad referencial
• Registro de eventos y almacenamiento de información sobre el acceso a la tabla
• Revisión de cuentas
• Syncreplicación cronosa de tablas
• Imposición de autorizaciones de seguridad
• Prevención de transacciones no válidas

Tipos de desencadenantes en Oracle

Los desencadenantes se pueden clasificar según los siguientes parámetros.

Clasificación basada en el momento

• ANTES del disparador: Se activa antes de que ocurra el evento especificado.
• DESPUÉS del disparador: Se activa después de que haya ocurrido el evento especificado.
• EN LUGAR DE Gatillo: Un tipo especial. Aprenderás más en los siguientes temas. (Solo para DML)

Clasificación basada en el nivel

• Disparador de nivel de DECLARACIÓN: Se activa una sola vez para la instrucción de evento especificada.
• Disparador a nivel de fila: Se activa para cada registro afectado por el evento especificado. (Solo para DML)

Clasificación basada en el evento

• Disparador DML: Se activa cuando se especifica el evento DML (INSERTAR/ACTUALIZAR/ELIMINAR).
• Disparador DDL: Se activa cuando se especifica el evento DDL (CREATE/ALTER).
• Disparador de BASE DE DATOS: Se activa cuando se especifica el evento de la base de datos (LOGON/LOGOFF/STARTUP/SHUTDOWN).

Por lo tanto, cada disparador es una combinación de los parámetros mencionados anteriormente.

Cómo crear un disparador

A continuación se muestra la sintaxis para crear un disparador. La captura de pantalla que aparece a continuación muestra esta sintaxis de creación de disparadores. Oracle.

CREATE [ OR REPLACE ] TRIGGER <trigger_name> 

[BEFORE | AFTER | INSTEAD OF ]

[INSERT | UPDATE | DELETE......]

ON<name of underlying object>

[FOR EACH ROW] 

[WHEN<condition for trigger to get execute> ]

DECLARE
<Declaration part>
BEGIN
<Execution part> 
EXCEPTION
<Exception handling part> 
END;

Explicación de sintaxis:

• La sintaxis anterior muestra las diferentes declaraciones opcionales que están presentes en la creación del disparador.
• ANTES/DESPUÉS especificará los momentos en que se produce el evento.
• INSERTAR/ACTUALIZAR/INICIAR SESIÓN/CREAR/etc. especificará el evento para el cual se debe activar el disparador.
• La cláusula ON especificará el objeto sobre el cual el evento mencionado anteriormente es válido. Por ejemplo, este será el nombre de la tabla sobre la cual puede ocurrir el evento DML en el caso de un disparador DML.
• El comando “FOR EACH ROW” especificará el disparador a nivel de fila.
• La cláusula WHEN especificará la condición adicional en la que debe activarse el disparador.
• La parte de declaración, la parte de ejecución y la parte de manejo de excepciones son las mismas que las de los demás. bloques PL/SQL. La parte de la declaración y la manejo de excepciones Las partes son opcionales.

:NUEVO y :VIEJO Cláusula

En un disparador de nivel de fila, el disparador se activa para cada fila relacionada. Y a veces es necesario conocer el valor antes y después de la declaración DML.

Oracle Se han proporcionado dos cláusulas en el disparador a nivel de fila para almacenar estos valores. Podemos usar estas cláusulas para referirnos a los valores antiguos y nuevos dentro del cuerpo del disparador.

• :NUEVO – Almacena un nuevo valor para las columnas de la tabla/vista base durante la ejecución del disparador.
• :VIEJO – Conserva el valor anterior de las columnas de la tabla/vista base durante la ejecución del disparador.

Esta cláusula debe utilizarse en función del evento DML. La siguiente tabla especifica qué cláusula es válida para cada instrucción DML (INSERT/UPDATE/DELETE).

EN LUGAR DE Gatillo

Un disparador "INSTEAD OF" es un tipo especial de disparador. Se utiliza únicamente en disparadores DML. Se usa cuando se va a producir algún evento DML en una vista compleja.

Consideremos un ejemplo en el que una vista se crea a partir de tres tablas base. Cuando se ejecuta cualquier operación DML sobre esta vista, esta se invalida porque los datos provienen de tres tablas diferentes. En este caso, se utiliza un disparador INSTEAD OF. Este disparador modifica directamente las tablas base en lugar de la vista para el evento en cuestión.

Ejemplo 1: En este ejemplo, vamos a crear una vista compleja a partir de dos tablas base, donde Tabla_1 es la tabla de empleados y Tabla_2 es ​​la tabla de departamentos.

A continuación, veremos cómo se utiliza el disparador INSTEAD OF para actualizar los detalles de ubicación en esta vista compleja. También veremos la utilidad de :NEW y :OLD en los disparadores. El ejemplo se realiza siguiendo estos pasos:

• Paso 1: Creación de las tablas 'emp' y 'dept' con las columnas correspondientes.
• Paso 2: Rellenar las tablas con valores de muestra.
• Paso 3: Creación de una vista para las tablas creadas anteriormente.
• Paso 4: Actualización de la vista antes del disparador INSTEAD OF
• Paso 5: Creación del disparador INSTEAD OF
• Paso 6: Actualización de la vista después del desencadenador INSTEAD OF

Paso 1) Crear las tablas 'emp' y 'dept' con las columnas apropiadas.

La captura de pantalla a continuación muestra la creación de las tablas base 'emp' y 'dept' en Oracle.

CREATE TABLE emp(
emp_no NUMBER,
emp_name VARCHAR2(50),
salary NUMBER,
manager VARCHAR2(50),
dept_no NUMBER);
/

CREATE TABLE dept(
Dept_no NUMBER,
Dept_name VARCHAR2(50),
LOCATION VARCHAR2(50));
/

Code Explicación

• Code líneas 1-7: Creación de la tabla 'emp'.
• Code líneas 8-12: Creación de la tabla 'depto'.

Salida:

Table Created

Paso 2) Ahora que hemos creado las tablas, las rellenaremos con valores de ejemplo.

La captura de pantalla que aparece a continuación muestra las filas de ejemplo que se insertan en las tablas 'dept' y 'emp'.

BEGIN
INSERT INTO DEPT VALUES(10,'HR','USA');
INSERT INTO DEPT VALUES(20,'SALES','UK');
INSERT INTO DEPT VALUES(30,'FINANCIAL','JAPAN');
COMMIT;
END;
/

BEGIN
INSERT INTO EMP VALUES(1000,'XXX',15000,'AAA',30);
INSERT INTO EMP VALUES(1001,'YYY',18000,'AAA',20) ;
INSERT INTO EMP VALUES(1002,'ZZZ',20000,'AAA',10);
COMMIT;
END;
/

Code Explicación

• Code líneas 13-19: Insertando datos en la tabla 'dept'.
• Code líneas 20-26: Insertando datos en la tabla 'emp'.

Salida:

PL/SQL procedure completed

Paso 3) Creación de una vista para las tablas creadas anteriormente.

La siguiente captura de pantalla muestra cómo se crea la vista compleja y cómo se realiza la consulta.

CREATE VIEW guru99_emp_view(
Employee_name,dept_name,location) AS
SELECT emp.emp_name,dept.dept_name,dept.location
FROM emp,dept
WHERE emp.dept_no=dept.dept_no;
/

SELECT * FROM guru99_emp_view;

Code Explicación

• Code líneas 27-32: Creación de la vista 'guru99_emp_view'.
• Code línea 33: Consultando guru99_emp_view.

Salida:

View created

Paso 4) Actualización de la vista antes del disparador INSTEAD OF.

La captura de pantalla que aparece a continuación muestra el intento de actualización de la vista compleja y el error resultante.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

Code Explicación

• Code líneas 34-38: Actualizar la ubicación de “XXX” a 'FRANCIA'. Se produjo una excepción porque las instrucciones DML no están permitidas directamente en la vista compleja.

Salida:

ORA-01779: cannot modify a column which maps to a non key-preserved table

ORA-06512: at line 2

Paso 5) Para evitar el error que se produjo al actualizar la vista en el paso anterior, en este paso vamos a utilizar un disparador "INSTEAD OF".

La captura de pantalla que aparece a continuación muestra la creación del disparador INSTEAD OF.

CREATE TRIGGER guru99_view_modify_trg
INSTEAD OF UPDATE
ON guru99_emp_view
FOR EACH ROW
BEGIN
UPDATE dept
SET location=:new.location
WHERE dept_name=:old.dept_name;
END;
/

Code Explicación

• Code línea 39: Creación del disparador INSTEAD OF para el evento 'UPDATE' en la vista 'guru99_emp_view' a nivel de fila. Contiene la instrucción de actualización para actualizar la ubicación en la tabla base 'dept'.
• Code línea 44: La instrucción de actualización utiliza ':NEW' y ':OLD' para encontrar el valor de las columnas antes y después de la actualización.

Salida:

Trigger Created

Paso 6) Actualización de la vista tras el disparador INSTEAD OF. Ahora no aparecerá el error, ya que el disparador INSTEAD OF gestionará la actualización de esta vista compleja. Al ejecutarse el código, la ubicación del empleado XXX se actualizará de «Japón» a «Francia».

La captura de pantalla que aparece a continuación muestra la actualización exitosa a través del activador INSTEAD OF y la vista actualizada.

BEGIN
UPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';
COMMIT;
END;
/

SELECT * FROM guru99_emp_view;

Code Explicación:

• Code líneas 49-53: Actualización de la ubicación de “XXX” a 'FRANCIA'. La operación se realizó correctamente porque el disparador 'INSTEAD OF' detuvo la instrucción de actualización real en la vista y realizó la actualización de la tabla base.
• Code línea 55: Verificando el registro actualizado.

Salida:

PL/SQL procedure successfully completed

Gatillo compuesto

El disparador compuesto es un disparador que permite especificar acciones para cada uno de los cuatro puntos de temporización en un único cuerpo de disparador. Los cuatro puntos de temporización diferentes que admite son los siguientes.

• ANTES DE LA DECLARACIÓN – nivel
• ANTES DE LA FILA – nivel
• DESPUÉS DE LA FILA – nivel
• DECLARACIÓN POSTERIOR – nivel

Permite combinar acciones con diferentes tiempos de activación en un mismo disparador.

La captura de pantalla que aparece a continuación muestra la sintaxis del disparador compuesto con sus cuatro secciones de temporización.

CREATE [ OR REPLACE ] TRIGGER <trigger_name>
FOR
[INSERT | UPDATE | DELETE.......]
ON <name of underlying object>
<Declarative part>
BEFORE STATEMENT IS
BEGIN
<Execution part>;
END BEFORE STATEMENT;

BEFORE EACH ROW IS
BEGIN
<Execution part>;
END EACH ROW;

AFTER EACH ROW IS
BEGIN
<Execution part>;
END AFTER EACH ROW;

AFTER STATEMENT IS
BEGIN
<Execution part>;
END AFTER STATEMENT;
END;

Explicación de sintaxis:

• La sintaxis anterior muestra la creación de un disparador 'COMPOUND'.
• La sección declarativa es común a todos los bloques de ejecución en el cuerpo del disparador.
• Estos cuatro bloques de temporización pueden estar en cualquier secuencia. No es obligatorio tener los cuatro bloques. Podemos crear un disparador COMPUESTO solo para los bloques de temporización necesarios.

Ejemplo 1: En este ejemplo, vamos a crear un disparador para rellenar automáticamente la columna de salario con el valor predeterminado de 5000.

La captura de pantalla que aparece a continuación muestra un ejemplo del disparador compuesto y su resultado.

CREATE TRIGGER emp_trig
FOR INSERT
ON emp
COMPOUND TRIGGER
BEFORE EACH ROW IS
BEGIN
:new.salary:=5000;
END BEFORE EACH ROW;
END emp_trig;
/

BEGIN
INSERT INTO EMP VALUES(1004,'CCC',15000,'AAA',30);
COMMIT;
END;
/

SELECT * FROM emp WHERE emp_no=1004;

Code Explicación:

• Code líneas 2-10: Creación del disparador compuesto. Se crea para el nivel de tiempo ANTES DE LA FILA para rellenar el salario con el valor predeterminado 5000. Esto cambiará el salario al valor predeterminado '5000' antes de insertar el registro en la tabla.
• Code líneas 11-14: Inserta el registro en la tabla 'emp'.
• Code línea 16: Verificando el registro insertado.

Salida:

Trigger created

PL/SQL procedure successfully completed.

Habilitar y deshabilitar desencadenadores

Los disparadores se pueden habilitar o deshabilitar. Para habilitar o deshabilitar un disparador, se debe especificar una instrucción ALTER (DDL) para dicho disparador.

A continuación se muestra la sintaxis para habilitar/deshabilitar los disparadores.

ALTER TRIGGER <trigger_name> [ENABLE|DISABLE];
ALTER TABLE <table_name> [ENABLE|DISABLE] ALL TRIGGERS;

Explicación de sintaxis:

• La primera sintaxis muestra cómo habilitar/deshabilitar un único disparador.
• La segunda declaración muestra cómo habilitar/deshabilitar todos los activadores en una tabla en particular.

Preguntas Frecuentes