Tutorial de desencadenadores de Oracle PL / SQL: en lugar de, compuesto (ejemplo)

¿Qué es Trigger en PL / SQL?

Los DISPARADORES son programas almacenados que son activados por el motor de Oracle automáticamente cuando las sentencias DML como insertar, actualizar, eliminar se ejecutan en la tabla o ocurren algunos eventos. El código que se ejecutará en caso de un disparador se puede definir según el requisito. Puede elegir el evento en el que se debe disparar el disparador y el momento de la ejecución. El propósito del disparador es mantener la integridad de la información en la base de datos.

En este tutorial, aprenderá:

  • Beneficios de los disparadores
  • Tipos de disparadores en Oracle
  • Cómo crear un disparador
  • : Cláusula NUEVA y: VIEJA
  • EN LUGAR DE Gatillo
  • Gatillo compuesto

Beneficios de los disparadores

Los siguientes son los beneficios de los desencadenantes.

  • Generando 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 mesa
  • Revisión de cuentas
  • Replicación síncrona de tablas
  • Imponer autorizaciones de seguridad
  • Previniendo transacciones inválidas

Tipos de disparadores en Oracle

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

  • Clasificación basada en el tiempo
    • ANTES del disparador: se dispara antes de que se produzca el evento especificado.
    • DESPUÉS de disparador: se activa después de que se ha producido el evento especificado.
    • EN LUGAR DE Trigger: Un tipo especial. Aprenderá más sobre los otros temas. (solo para DML)
  • Clasificación basada en el nivel
    • Disparador de nivel STATEMENT: Se dispara una vez para la declaración de evento especificada.
    • Desencadenador de nivel FILA: Se dispara para cada registro que se vio afectado en el evento especificado. (solo para DML)
  • Clasificación basada en el evento
    • Activador DML: se activa cuando se especifica el evento DML (INSERT / UPDATE / DELETE)
    • Disparador DDL: se activa cuando se especifica el evento DDL (CREATE / ALTER)
    • Activador de BASE DE DATOS: Se dispara cuando se especifica el evento de la base de datos (INICIO DE SESIÓN / CIERRE DE SESIÓN / INICIO / APAGADO)

Entonces, cada disparador es la combinación de los parámetros anteriores.

Cómo crear un disparador

A continuación se muestra la sintaxis para crear un disparador.

CREATE [ OR REPLACE ] TRIGGER 
[BEFORE | AFTER | INSTEAD OF ][INSERT | UPDATE | DELETE… ]ON[FOR EACH ROW][WHEN ]DECLAREBEGINEXCEPTIONEND;

Explicación de la sintaxis:

  • La sintaxis anterior muestra las diferentes declaraciones opcionales que están presentes en la creación de disparadores.
  • ANTES / DESPUÉS especificará los tiempos del evento.
  • INSERTAR / ACTUALIZAR / INICIAR SESIÓN / CREAR / etc. especificará el evento para el que se debe activar el disparador.
  • La cláusula ON especificará en qué objeto es válido el evento mencionado anteriormente. Por ejemplo, este será el nombre de la tabla en la que puede ocurrir el evento DML en el caso de DML Trigger.
  • El comando "PARA CADA FILA" especificará el nivel de activación de FILA.
  • La cláusula WHEN especificará la condición adicional en la que el disparador debe dispararse.
  • La parte de declaración, parte de ejecución, parte de manejo de excepciones es la misma que la de los otros bloques PL / SQL. La parte de declaración y la parte de manejo de excepciones son opcionales.

: Cláusula NUEVA y: VIEJA

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

Oracle ha proporcionado dos cláusulas en el activador de nivel RECORD para mantener estos valores. Podemos usar estas cláusulas para referirnos a los valores nuevos y antiguos dentro del cuerpo del disparador.

  • : NUEVO: contiene un nuevo valor para las columnas de la tabla / vista base durante la ejecución del disparador
  • : VIEJO - Mantiene el valor antiguo 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 especificará qué cláusula es válida para qué declaración DML (INSERT / UPDATE / DELETE).

INSERTAR ACTUALIZAR ELIMINAR
:NUEVO VÁLIDO VÁLIDO INVÁLIDO. No hay ningún valor nuevo en el caso de eliminación.
:VIEJO INVÁLIDO. No hay ningún valor antiguo en el caso de inserción VÁLIDO VÁLIDO

EN LUGAR DE Gatillo

"EN VEZ DE disparador" es el tipo especial de disparador. Se usa solo en activadores DML. Se utiliza cuando se va a producir un evento DML en la vista compleja.

Considere un ejemplo en el que se crea una vista a partir de 3 tablas base. Cuando se emite cualquier evento DML sobre esta vista, se volverá inválido porque los datos se toman de 3 tablas diferentes. Entonces, en este INSTEAD OF se usa el disparador. El desencadenador INSTEAD OF se utiliza para modificar las tablas base directamente en lugar de modificar la vista para el evento dado.

Ejemplo 1 : En este ejemplo, vamos a crear una vista compleja a partir de dos tablas base.

  • Table_1 es tabla emp y
  • Table_2 es ​​la tabla de departamentos.

Luego, veremos cómo se usa el disparador INSTEAD OF para emitir ACTUALIZAR la declaración de detalles de ubicación en esta vista compleja. También veremos cómo: NEW y: OLD son útiles en los disparadores.

  • Paso 1: creación de la tabla 'emp' y 'dept' con las columnas adecuadas
  • Paso 2: completar la tabla con valores de muestra
  • Paso 3: Crear vista para la tabla creada anteriormente
  • Paso 4: actualización de la vista antes del disparador en lugar de
  • Paso 5: creación del disparador en lugar de
  • Paso 6: Actualización de la vista después del disparador en lugar de

Paso 1) Creando la tabla 'emp' y 'dept' con las columnas apropiadas

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));/

Explicación del código

  • Línea de código 1-7 : Creación de tabla 'emp'.
  • Línea de código 8-12 : Creación de 'departamento' de tabla.

Producción

Tabla creada

Paso 2) Ahora que hemos creado la tabla, completaremos esta tabla con valores de muestra y Creación de vistas para las tablas anteriores. 

BEGININSERT INTO DEPT VALUES(10,‘HR’,‘USA’);INSERT INTO DEPT VALUES(20,'SALES','UK’);INSERT INTO DEPT VALUES(30,‘FINANCIAL',‘JAPAN');COMMIT;END;/BEGININSERT INTO EMP VALUES(1000,'XXX5,15000,'AAA',30);INSERT INTO EMP VALUES(1001,‘YYY5,18000,‘AAA’,20) ;INSERT INTO EMP VALUES(1002,‘ZZZ5,20000,‘AAA',10);COMMIT;END;/

Explicación del código

  • Línea de código 13-19 : Insertar datos en la tabla 'dept'.
  • Línea de código 20-26: Insertar datos en la tabla 'emp'.

Producción

Procedimiento PL / SQL completado

Paso 3) Crear una vista para la tabla creada anteriormente. 

CREATE VIEW guru99_emp_view(Employee_name:dept_name,location) ASSELECT emp.emp_name,dept.dept_name,dept.locationFROM emp,deptWHERE emp.dept_no=dept.dept_no;/
SELECT * FROM guru99_emp_view;

Explicación del código

  • Línea de código 27-32: Creación de la vista 'guru99_emp_view'.
  • Línea de código 33: Consultando guru99_emp_view.

Producción

Ver creado

NOMBRE DE EMPLEADO DEPT_NAME LOCALIZACIÓN
ZZZ HORA EE.UU
YYY VENTAS Reino Unido
XXX FINANCIERO JAPÓN

Paso 4) Actualización de la vista antes en lugar del disparador.

BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name=:'XXX’;COMMIT;END;/

Explicación del código

  • Línea de código 34-38: actualice la ubicación de "XXX" a 'FRANCIA'. Presentó la excepción porque las declaraciones DML no están permitidas en la vista compleja.

Producción

ORA-01779: no se puede modificar una columna que se asigna a una tabla sin clave preservada

ORA-06512: en la línea 2

Paso 5) Para evitar la aparición de errores durante la actualización de la vista en el paso anterior, en este paso vamos a utilizar "en lugar de disparador".

CREATE TRIGGER guru99_view_modify_trgINSTEAD OF UPDATEON guru99_emp_viewFOR EACH ROWBEGINUPDATE deptSET location=:new.locationWHERE dept_name=:old.dept_name;END;/

Explicación del código

  • Línea de código 39: Creación del disparador INSTEAD OF para el evento 'UPDATE' en la vista 'guru99_emp_view' en el nivel ROW. Contiene la declaración de actualización para actualizar la ubicación en el 'departamento' de la tabla base.
  • Línea de código 44: La declaración de actualización usa ': NUEVO' y ': VIEJO' para encontrar el valor de las columnas antes y después de la actualización.

Producción

Activador creado

Paso 6) Actualización de la vista después en lugar de disparador. Ahora el error no vendrá ya que el "en lugar de disparador" manejará la operación de actualización de esta vista compleja. Y cuando el código se haya ejecutado, la ubicación del empleado XXX se actualizará a "Francia" desde "Japón".

BEGINUPDATE guru99_emp_view SET location='FRANCE' WHERE employee_name='XXX';COMMIT;END;/
SELECT * FROM guru99_emp_view;

Explicación del código:

  • Línea de código 49-53: Actualización de la ubicación de "XXX" a 'FRANCIA'. Tiene éxito porque el disparador 'INSTEAD OF' ha detenido la declaración de actualización real en la vista y ha realizado la actualización de la tabla base.
  • Línea de código 55: Verificación del registro actualizado.

Producción:

Procedimiento PL / SQL completado con éxito

NOMBRE DE EMPLEADO DEPT_NAME LOCALIZACIÓN
ZZZ HORA EE.UU
YYY VENTAS Reino Unido
XXX FINANCIERO FRANCIA

Gatillo compuesto

El disparador compuesto es un disparador que le permite especificar acciones para cada uno de los cuatro puntos de tiempo en el cuerpo del disparador único. 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
  • DESPUÉS DE LA DECLARACIÓN - nivel

Proporciona la posibilidad de combinar las acciones para diferentes tiempos en el mismo disparador.

CREATE [ OR REPLACE ] TRIGGER 
FOR[INSERT | UPDATE | DELET… .]ON ‭ ‬BEFORE STATEMENT ISBEGIN;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN;END EACH ROW;AFTER EACH ROW ISBEGIN;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN;END AFTER STATEMENT;END;

Explicación de la sintaxis:

  • La sintaxis anterior muestra la creación del disparador 'COMPOUND'.
  • La sección declarativa es común para todos los bloques de ejecución en el cuerpo del disparador.
  • Estos 4 bloques de tiempo pueden estar en cualquier secuencia. No es obligatorio tener estos 4 bloques de tiempo. Podemos crear un disparador COMPUESTO solo para los tiempos que se requieren.

Ejemplo 1 : En este ejemplo, crearemos un disparador para autocompletar la columna de salario con el valor predeterminado 5000.

CREATE TRIGGER emp_trigFOR INSERTON empCOMPOUND TRIGGERBEFORE EACH ROW ISBEGIN:new.salary:=5000;END BEFORE EACH ROW;END emp_trig;/
BEGININSERT INTO EMP VALUES(1004,‘CCC’,15000,‘AAA’,30);COMMIT;END;/
SELECT * FROM emp WHERE emp_no=1004;

Explicación del código:

  • Línea de código 2-10 : Creación de un disparador compuesto. Se crea para cronometrar ANTES del nivel de FILA para completar el salario con el valor predeterminado 5000. Esto cambiará el salario al valor predeterminado '5000' antes de insertar el registro en la tabla.
  • Línea de código 11-14 : Inserte el registro en la tabla 'emp'.
  • Línea de código 16 : Verificación del registro insertado.

Producción:

Activador creado

Procedimiento PL / SQL completado con éxito.

EMP_NAME EMP_NO SALARIO GERENTE DEPT_NO
CCC 1004 5000 AAA 30

Habilitar y deshabilitar disparadores

Los disparadores se pueden habilitar o deshabilitar. Para habilitar o deshabilitar el disparador, se debe proporcionar una instrucción ALTER (DDL) para el disparador que lo deshabilita o habilita.

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

ALTER TRIGGER 
 [ENABLE|DISABLE];ALTER TABLE 
 [ENABLE|DISABLE] ALL TRIGGERS;

Explicación de la sintaxis:

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

Resumen

En este capítulo, hemos aprendido sobre los disparadores PL / SQL y sus ventajas. También hemos aprendido las diferentes clasificaciones y discutimos EN LUGAR DE disparador y disparador COMPUESTO.

Articulos interesantes...