Oracle Tutorial de activación de PL/SQL: en lugar de compuesto [Ejemplo]

¿Qué es el disparador en PL/SQL?

ACTIVADORES Son programas almacenados que son disparados por Oracle motor automáticamente cuando se ejecutan declaraciones DML como insertar, actualizar, eliminar en la tabla o cuando ocurren algunos eventos. El código que se ejecutará en caso de un desencadenante se puede definir según el requisito. Puede elegir el evento en el que se debe activar 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.

Beneficios de los desencadenantes

Following son 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 la siguientewing parámetros.

  • Clasificación basada en la sincronización
  • ANTES del disparador: se dispara antes de que ocurra el evento especificado.
  • DESPUÉS del disparador: se dispara después de que ha ocurrido el evento especificado.
  • EN LUGAR DE Trigger: Un tipo especial. Aprenderá más sobre otros temas. (sólo para DML)
  • Clasificación basada en la nivel
  • Activador de nivel de DECLARACIÓN: Se activa una vez para la declaración de evento especificada.
  • Activador de nivel de FILA: Se activa para cada registro que se vio afectado en el evento especificado. (sólo 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 (CREAR/ALTERAR)
  • Activador de BASE DE DATOS: Se activa 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 activador.

crear activador

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 horarios del evento.
  • INSERTAR/ACTUALIZAR/INICIAR SESIÓN/CREAR/etc. especificará el evento para el cual 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 la FILA.
  • La cláusula WHEN especificará la condición adicional en la que se debe activar 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 las otras bloques PL/SQL. La parte de declaración y la parte de manejo de excepciones 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 ha proporcionado dos cláusulas en el disparador de nivel RECORD para contener estos valores. Podemos usar estas cláusulas para referirnos a los valores antiguos y nuevos dentro del cuerpo del disparador.

  • :NUEVO: contiene un nuevo valor para las columnas de la tabla/vista base durante la ejecución del activador.
  • :OLD: contiene el valor antiguo de las columnas de la tabla/vista base durante la ejecución del activador.

Esta cláusula debe usarse según el evento DML. La siguiente tabla especificará qué cláusula es válida para qué declaración DML (INSERT/UPDATE/DELETE).

INSERT ACTUALIZAR BORRAR
: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 LUGAR DE disparador” es el tipo especial de disparador. Se utiliza únicamente en activadores DML. Se utiliza cuando va a ocurrir algún evento DML en la comunicación.plex ver.

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, dejará de ser válido porque los datos se toman de 3 tablas diferentes. Entonces, en este caso se usa el disparador EN LUGAR DE. El disparador INSTEAD OF se utiliza para modificar las tablas base directamente en lugar de modificar la vista para el evento determinado.

Ejemplo : En este ejemplo, vamos a crear una complex Vista desde la mesa de dos bases.

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

Luego veremos cómo se usa el disparador EN LUGAR DE para emitir ACTUALIZAR la declaración de detalles de ubicación en esta comunicación.plex vista. También veremos cómo :NEW y :OLD son útiles en los disparadores.

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

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

EN LUGAR DE Gatillo

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 tabla 'departamento'.

Salida

Tabla creada

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

EN LUGAR DE Gatillo

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

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

Salida

Procedimiento PL/SQL terminado

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

EN LUGAR DE Gatillo

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: Creación de la vista 'guru99_emp_view'.
  • Línea de código 33: Consultando guru99_emp_view.

Salida

Ver creado

NOMBRE DE EMPLEADO DEPTO_NOMBRE
ZZZ HR Estados Unidos de America
YYY Ventas UK
XXX FINANCIERA JAPÓN

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

EN LUGAR DE Gatillo

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 la ubicación de “XXX” a “FRANCIA”. Planteó la excepción porque el Declaraciones DML no están permitidos en la complex ver.

Salida

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

ORA-06512: en la línea 2

Paso 5)Para evitar el error durante la actualización de la vista en el paso anterior, en este paso usaremos "en lugar de activador".

EN LUGAR DE Gatillo

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: Creación del activador EN LUGAR DE para el evento 'ACTUALIZAR' en la vista 'guru99_emp_view' en el nivel FILA. Contiene la declaración de actualización para actualizar la ubicación en la tabla base "departamento".
  • Línea de código 44: La declaración de actualización utiliza ':NEW' y ':OLD' para encontrar el valor de las columnas antes y después de la actualización.

Salida

Activador creado

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

EN LUGAR DE Gatillo

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: Actualización de la ubicación de “XXX” a “FRANCIA”. Tiene éxito porque el activador 'EN LUGAR DE' detuvo la declaración de actualización real a la vista y realizó la actualización de la tabla base.
  • Línea de código 55: Verificando el registro actualizado.

Salida:

Procedimiento PL / SQL completado con éxito

NOMBRE DE EMPLEADO DEPTO_NOMBRE
ZZZ HR Estados Unidos de America
YYY Ventas UK
XXX FINANCIERA FRANCIA

Gatillo compuesto

El disparador compuesto es un disparador que le permite especificar acciones para cada uno de los cuatro puntos de sincronización en el cuerpo del disparador único. Los cuatro puntos de sincronización diferentes que admite se muestran a continuación.

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

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

Gatillo compuesto

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

  • La sintaxis anterior muestra la creación del disparador 'COMPUESTO'.
  • 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 sincronización. Podemos crear un disparador COMPUESTO solo para los tiempos que sean necesarios.

Ejemplo : En este ejemplo, crearemos un activador para completar automáticamente la columna de salario con el valor predeterminado 5000.

Gatillo compuesto

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:

  • Línea de código 2-10: Creación de disparador compuesto. Se crea para cronometrar ANTES del nivel 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: Verificando el registro insertado.

Salida:

Disparador creado

Procedimiento PL / SQL completado con éxito.

EMP_NOMBRE EMP_NO SALARIO GENERAL DEPTO_NO
CCC 1004 5000 AAA 30

Habilitar y deshabilitar desencadenadores

Los desencadenantes 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 activadores.

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 el disparador único.
  • La segunda declaración muestra cómo habilitar/deshabilitar todos los activadores en una tabla en particular.

Resumen

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