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

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 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.

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

“INSTEAD OF trigger” es un tipo especial de disparador. Se utiliza únicamente en disparadores 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, 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 vista compleja a partir de dos tablas base.

• Table_1 es la tabla emp y

• Table_2 es ​​la tabla de departamentos.

Luego, veremos cómo se utiliza el disparador INSTEAD OF para emitir la instrucción UPDATE de detalles de ubicación en esta vista compleja. 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

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.

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.

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	EE. UU.
YYY	OFERTAS	UK
XXX	FINANCIERA	JAPÓN

Paso 4) Actualización de la vista antes en lugar 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 la ubicación de “XXX” a “FRANCIA”. Planteó la excepción porque el Declaraciones DML No están permitidos en la vista compleja.

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".

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 de. Ahora el error no aparecerá ya que el disparador en lugar de se encargará de 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á de “Japón” a “Francia”.

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	EE. UU.
YYY	OFERTAS	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.

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.

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.

Resum

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.