Ejercicio de disparador de Oracle PL / SQL: Reemplazo, compuesto [Example]

¿Qué es Trigger en PL / SQL?

ACTIVADORES son programas almacenados que queman automáticamente el motor de Oracle cuando las declaraciones DML como insertar, actualizar, eliminar en la tabla o cuando ocurren algunos eventos. El código a violar en caso de activación se puede definir de acuerdo con el requisito. Puede seleccionar el evento en el que se debe disparar el disparador y el momento de ejecución. El propósito del disparador es mantener la integridad de la información en la base de datos.

En este tutorial, aprenderá:

Ventajas de los disparadores

Estos son los beneficios de los desencadenantes.

  • Genere automáticamente algunos valores de columna derivados
  • Hacer cumplir la integridad de la referencia
  • Registre eventos y almacene información sobre el acceso a la mesa
  • Inspección
  • Replicación síncrona de tablas
  • Imposición de autorizaciones de seguridad
  • Evitar transacciones inválidas

Tipos de disparadores en Oracle

El disparador se puede clasificar según los siguientes parámetros.

  • Clasificación basada en la momento
    • ANTES de disparador: se activa antes de que ocurra el evento especificado.
    • DESPUÉS de disparador: se activa después de que se produce el evento especificado.
    • EN LUGAR DE Gatillo: tipo especial. Aprenderá más sobre los temas adicionales. (solo para DML)
  • Clasificación basada en la nivel
    • Motivación en el nivel DECLARACIÓN: se activa una vez para la declaración de evento especificada.
    • Activador de nivel de FILA: dispara todos los registros afectados en el caso especificado. (solo para DML)
  • Clasificación basada en la Evento
    • Activador DML: se activa cuando se especifica el evento DML (INSERT / UPDATE / DELETE)
    • Activador DDL: se activa cuando se especifica el evento DDL (CREATE / ALTER)
    • DETALLES: Se dispara cuando se especifica el evento de la base de datos (LOGON / LOGOFF / STARTUP / SHUTDOWN)

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 <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 la sintaxis:

  • La sintaxis anterior muestra las diversas declaraciones opcionales presentes en la creación de disparadores.
  • ANTES / DESPUÉS especifique el momento del evento.
  • INSERTAR / ACTUALIZAR / INICIAR SESIÓN / CREAR / etc. deberá especificar el evento por el cual se debe quemar el gatillo.
  • Una cláusula ON especificará qué objeto es válido para el evento anterior. Por ejemplo, este es el nombre de la tabla en la que puede ocurrir el evento DML en el caso de un activador DML.
  • El comando «PARA TODOS» especificará el nivel de activación de FILA.
  • CUANDO una cláusula especifica la condición adicional en la que se debe prender fuego al disparador.
  • La parte de confirmación, la parte de ejecución, la parte de manejo de excepciones son las mismas que el resto de los bloques PL / SQL. La parte de declaración y la parte de manejo de excepciones son opcionales.

: Cláusula NUEVA y: VIEJA

En un disparador de nivel consecutivo, el disparador se dispara para cada fila relacionada. Y a veces se afirma 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 hacer referencia a los valores nuevos y antiguos dentro del cuerpo del disparador.

  • : NUEVO: agrega un nuevo valor a las columnas de la columna de la tabla / vista durante la ejecución del desencadenador
  • : OLD: tiene un valor antiguo en las columnas de pie de página / vista durante la ejecución del desencadenador

Esta cláusula debe utilizarse en función del evento DML. La siguiente tabla especificará qué cláusula tiene una declaración DML (INSERT / UPDATE / DELETE) válida.

INSERTAR Actualizar ELIMINAR
: NUEVO VALOR VALOR INVÁLIDO. No hay ningún valor nuevo en caso de eliminación.
: SEAN INVÁLIDO. No hay un valor antiguo en un caso de entrada. VALOR VALOR

EN LUGAR DEL Gatillo

El tipo especial de disparador es «EN LUGAR de disparador». Solo se usa en choques DML. Se utiliza cuando se va a producir un evento DML en la escena compleja.

Considere un ejemplo en el que se crea una vista a partir de 3 tablas base. Cuando se emite cualquier evento DML en esta vista, no será válido porque los datos se toman de 3 tablas diferentes. Entonces, en este EN LUGAR DE, se usa un disparador. El disparador INSTEAD OF se utiliza para modificar directamente las tablas base en lugar de modificar la escena para el evento en particular.

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

  • Table_2 es ​​la tabla de departamentos.

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

  • Paso 1: cree una tabla ‘emp’ y ‘sección’ con las columnas apropiadas
  • Paso 2: ponga valores de muestra en la mesa
  • Paso 3: cree una vista para la tabla creada anteriormente
  • Paso 4: actualice la escena antes de que se reemplace el disparador
  • Paso 5: crea el disparador en lugar de un disparador
  • Paso 6: actualizar la vista después del disparador

Paso 1) Crear una tabla de ‘emp’ y ‘división’ con 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: Creando una tabla ‘emp’.
  • Línea de código 8-12: Creación de una tabla de ‘sección’.

Producción

Tabla creada

Paso 2) Ahora que hemos creado la tabla, la llenaremos con valores de muestra y creación de escenas para las tablas anteriores.

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,'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

  • Código en línea 13-19: Ingrese datos en la tabla de ‘sección’.
  • Línea de código 20-26: Ingrese datos en la tabla ‘emp’.

Producción

Procedimiento PL / SQL completado

Paso 3) Creando una escena para la mesa creada arriba.

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;

Explicación del Código

  • Línea de código 27-32: Creando una vista ‘guru99_emp_view’.
  • Línea de código 33: Cuestionando a guru99_emp_view.

Producción

Mirada probada

NOMBRE DE EMPLEADO DEPT_NAME LOCALIZACIÓN
ZZZ ANUNCIO SAM
AAAA VENTAS DECIR
XXX FINANZAS JAPÓN

Paso 4) Actualiza la escena antes del disparador.

BEGIN
UPDATE 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 el sitio «XXX» a «FRANCIA». Presentó la excepción porque las declaraciones DML no están permitidas en la vista compleja.

Producción

ORA-01779: una columna que se modifica a una tabla no conservada no se puede modificar

ORA-06512: en la línea 2

Paso 5)Para evitar el contacto de error mientras se actualiza la vista en el paso anterior, en este paso vamos a «usar el disparador en su lugar».

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

Explicación del Código

  • Línea de código 39: EN LUGAR de la creación de un disparador para un evento de ‘Actualización’ en la vista ‘guru99_emp_view’ en el nivel ROW. La declaración de actualización está ahí para actualizar el sitio en la «sección» de la tabla inferior.
  • Línea de código 44: La declaración de actualización usa ‘: NUEVO’ y ‘: VIEJO’ para obtener el valor de las columnas antes y después de la actualización.

Producción

Motivación creada

Paso 6) Actualización de escena después del disparador. Ahora el error no vendrá porque la operación de actualización «en lugar de disparador» manejará esta vista compleja. Y una vez que el código esté completo, el sitio para empleados de XXX se actualizará a «Francia» desde «Japón».

BEGIN
UPDATE 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: Actualice el sitio «XXX» a «FRANCIA». Tiene éxito porque el disparador ‘INSTEAD OF’ detuvo la declaración de actualización real que apareció y actualizó la tabla inferior.
  • Línea de código 55: Verifique el registro actualizado.

Producción:

El procedimiento PL / SQL se completó con éxito

NOMBRE DE EMPLEADO DEPT_NAME LOCALIZACIÓN
ZZZ ANUNCIO SAM
AAAA VENTAS DECIR
XXX FINANZAS 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 individual. Los cuatro puntos de cronometraje diferentes que admite se encuentran a continuación.

  • ANTES DE LA DECLARACIÓN – nivel
  • ANTES DE LA FILA – nivel
  • TIENE 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 <trigger_name> 
FOR
[INSERT | UPDATE | DELET.......]
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 la sintaxis:

  • La sintaxis anterior muestra la creación del disparador ‘COMPOUND’.
  • Una sección de confirmación 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 tiempo. Solo podemos crear un disparador COMPUESTO para los tiempos requeridos.

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

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;

Explicación del Código:

  • Código de línea 2-10: Creación de un disparador compuesto. Se crea con tiempo antes del nivel ROW para cambiar el salario a su valor predeterminado 5000. Esto cambiará el salario a un valor predeterminado de ‘5000’ antes de que el registro se coloque en la tabla.
  • Línea de código 11-14: Ingrese el registro en la tabla ‘emp’.
  • Línea de código 16: Verifica el registro enviado.

Producción:

Motivación probada

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

EMP_NAME EMP_NO SALARIO GERENTE DEPT_NO
CCC 1004 5000 AAA 30

Habilitar y deshabilitar disparadores

El disparador se puede habilitar o deshabilitar. Para habilitar o deshabilitar el desencadenador, el desencadenante que lo deshabilita o habilita debe recibir una instrucción ALTER (DDL).

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 la sintaxis:

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

Resumen

En este capítulo, aprendimos sobre los estímulos PL / SQL y sus beneficios. También aprendimos las diferentes clasificaciones y discutimos EN LUGAR de disparador y disparador COMPUESTO.

Deja un comentario

Tu dirección de correo electrónico no será publicada.

Salir de la versión móvil