Tutorial de Activación de Oracle PL/SQL: En lugar de, Compound [Ejemplo]

En este tutorial, aprenderá-

• Beneficios de los Disparadores
• Tipos de Disparadores en Oracle
• Cómo crear Disparadores
• :Cláusula NUEVA y :ANTIGUA
• EN LUGAR DE Disparador
• Disparador compuesto

Beneficios de los disparadores

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

• Generar automáticamente algunos valores de columna derivados
• Reforzar la integridad referencial
• Registro de eventos y almacenamiento de información sobre el acceso a la tabla
• Auditar
• Replicación sincrónica de tablas
• Imponer autorizaciones de seguridad
• Evitar transacciones no válidas

Los tipos de desencadenadores en Oracle

Los desencadenadores se pueden clasificar en función de los siguientes parámetros.

• Clasificación basada en el tiempo
  • ANTES del desencadenador: Se activa antes de que se produzca el evento especificado.
  • DESPUÉS del desencadenador: Se activa después de que se haya producido el evento especificado.
  • EN LUGAR DE Trigger: Un tipo especial. Aprenderá más sobre los temas adicionales. (solo para DML )
• Clasificación basada en el desencadenador de nivel de INSTRUCCIÓN level
  • : Se activa una vez para la instrucción de evento especificada.
  • Desencadenador de nivel de fila: Se activa para cada registro que se vio afectado en el evento especificado. (solo para DML)
• Clasificación basada en el activador DML de evento
  • : Se activa cuando se especifica el evento DML (INSERTAR/ACTUALIZAR/ELIMINAR)
  • Activador DDL: Se activa cuando se especifica el evento DDL (CREATE/ALTER)
  • Desencadenador DE BASE DE DATOS: Se activa cuando se especifica el evento de base de datos (INICIO DE SESIÓN/CIERRE DE SESIÓN/INICIO/APAGADO)

De modo que cada desencadenador 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 TRIGGER <trigger_name> ON<name of underlying object> DECLARE<Declaration part>BEGIN<Execution part> EXCEPTION<Exception handling part> END;

Explicación de sintaxis:

• La sintaxis anterior muestra las diferentes instrucciones opcionales que están presentes en la creación del disparador.
• ANTES/ DESPUÉS especificará los tiempos de los eventos.
• INSERTAR / ACTUALIZAR/INICIAR SESIÓN / CREAR / etc. especificará el evento para el que se debe disparar 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 del desencadenador DML.
• El comando «PARA CADA FILA» especificará el desencadenador de nivel de FILA.
• La cláusula WHEN especificará la condición adicional en la que se debe disparar el disparador.
• La parte de declaración, la parte de ejecución, la 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: ANTIGUA

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

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

• :NEW-Contiene un nuevo valor para las columnas de la tabla/vista base durante la ejecución del disparador
• : OLD – Contiene un valor antiguo de las columnas de la tabla/vista base durante la ejecución del disparador

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

	INSERTAR	ACTUALIZACIÓN	ELIMINAR
:NUEVO	VÁLIDO	VÁLIDO	INVÁLIDA. No hay un nuevo valor en el caso eliminar.
:ANTIGUO	NO VÁLIDO. No hay ningún valor antiguo en insertar caso	VÁLIDO	VÁLIDO

en LUGAR DE Desencadenar

«en LUGAR DE «trigger» es el tipo especial de gatillo. Se usa solo en desencadenantes de LMD. Se utiliza cuando se va a producir cualquier 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, no será válido porque los datos se toman de 3 tablas diferentes. Así que en este EN LUGAR DE gatillo se utiliza. El desencadenador EN LUGAR DE se usa 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 la tabla emp y

• Table_2 es el departamento de la tabla.

Luego veremos cómo se usa el desencadenador EN LUGAR DE para emitir la instrucció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 columnas adecuadas
• Paso 2: Rellenar la tabla con valores de muestra
• Paso 3: Crear una vista para la tabla creada anteriormente
• Paso 4: Actualizar la vista antes del desencadenador en lugar de
• Paso 5: Creación del desencadenador en lugar de
• Paso 6: Actualizar la vista después del desencadenador en lugar de

Paso 1) Crear la tabla ‘emp’ y ‘dept’ con columnas adecuadas

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 la tabla ‘emp’.
• Línea de código 8-12: Creación de la tabla «dept».

Salida

Tabla Creada

Paso 2) Ahora que hemos creado la tabla, rellenaremos 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’.

Salida

Procedimiento PL / SQL completado

Paso 3) Creación de 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: la Creación de ‘guru99_emp_view’ punto de vista.

• Línea de código 33: Consultar guru99_emp_view.

Output

View created

EMPLOYEE_NAME	DEPT_NAME	LOCATION
ZZZ	HR	USA
YYY	SALES	UK
XXX	FINANCIAL	JAPAN

Step 4) Update of view before instead-of trigger.

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

Code Explanation

• Code line 34-38: Update the location of «XXX» to ‘FRANCE’. Planteó la excepción porque las instrucciones DML no están permitidas en la vista compleja.

Salida

ORA-01779: no se puede modificar una columna que se asigna a una tabla que no conserva la clave

ORA-06512: en la línea 2

Paso 5)Para evitar el error durante la vista de actualización en el paso anterior, en este paso vamos a usar «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 evento EN LUGAR de trigger para ‘ACTUALIZAR’ en la vista ‘guru99_emp_view’ a nivel de FILA. Contiene la instrucción update para actualizar la ubicación en la tabla base ‘dept’.
• La línea de código 44: La instrucción Update utiliza ‘: NEW ‘y’: OLD ‘ para encontrar el valor de las columnas antes y después de la actualización.

Salida

Desencadenador Creado

Paso 6) Actualización de la vista después en lugar del desencadenador. Ahora el error no se producirá ya que el» disparador en lugar de » 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 «de » 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 «EN LUGAR DE» ha detenido la instrucción de actualización real en la vista y ha realizado la actualización de la tabla base.
• Línea de código 55: Verificar el registro actualizado.Salida

:

PL/SQL procedimiento completado con éxito

EMPLOYEE_NAME	NOMBRE_DPTO	UBICACIÓN
ZZZ	RRHH	estados UNIDOS
YYY	VENTAS	reino unido
XXX	FINANCIERA	FRANCIA

Compuesto Gatillo

El Compuesto trigger es un disparador que permite especificar acciones para cada uno de los cuatro puntos de sincronización en el accionamiento único cuerpo. Los cuatro puntos de sincronización diferentes que soporta son los siguientes.

• ANTES del nivel de INSTRUCCIÓN
• ANTES DEL nivel de fila
• DESPUÉS del nivel de fila
• DESPUÉS del nivel de INSTRUCCIÓN

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

CREATE TRIGGER <trigger_name> FORON <name of underlying object><Declarative part>‭ ‬BEFORE STATEMENT ISBEGIN<Execution part>;END BEFORE STATEMENT;BEFORE EACH ROW ISBEGIN<Execution part>;END EACH ROW;AFTER EACH ROW ISBEGIN<Execution part>;END AFTER EACH ROW;AFTER STATEMENT ISBEGIN<Execution part>;END AFTER STATEMENT;END;

Explicación de sintaxis:

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

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

CREATE TRIGGER emp_trig FOR INSERT ON empCOMPOUND TRIGGER BEFORE EACH ROW IS BEGIN: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 compuesto gatillo. Se crea para el tiempo ANTES del nivel de FILA para llenar 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.

Salida:

Se creó el desencadenador

El procedimiento PL / SQL se completó con éxito.

EMP_NAME	EMP_NO	SUELDO	MANAGER	DEPT_NO
CCC	1004	5000	AAA	30

Habilitar y Deshabilitar los Desencadenadores

Activa puede ser habilitado o deshabilitado. Para habilitar o deshabilitar el desencadenador, se debe proporcionar una instrucción ALTER (DDL) para el desencadenador que lo deshabilita o habilita.

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

ALTER TRIGGER <trigger_name> ;ALTER TABLE <table_name> ALL TRIGGERS;

Explicación de sintaxis:

• La primera sintaxis muestra cómo activar/desactivar el desencadenador único.
• La segunda instrucción muestra cómo habilitar / deshabilitar todos los disparadores de 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 discutido EN LUGAR DE disparador y disparador COMPUESTO.