Objetivos:
• Conocer los procedimientos almacenados llamados
Triggers/Disparadores.
• Conocer sus usos mas comunes.
• Identificar diferentes Tipos de Triggers.
• Crear/modificar Trigger de acuerdo a nuestra necesidad.
NOTA: Usamos como ejemplo la Base de Datos:
ORCL, la cual viene por defecto en cualquier versión de
ORACLE.
____________________________________________________________________________________
Los Triggers.
Un Trigger es un tipo especial de procedimiento almacenado que se ejecuta automáticamente cuando se produce un evento en el servidor de Base de Datos. Los Triggers DML se ejecutan cuando un usuario intenta modificar los datos a través de una sentencia DML. Los eventos DML son INSERT, UPDATE o DELETE en una tabla o vista.[1]
Los
Triggers son programas almacenados que se ejecutan o disparan automáticamente cuando se producen algunos eventos. Los
Triggers son escritos para ser ejecutado en respuesta a cualquiera de los siguientes eventos: Una sentencia de manipulación de Bases de Datos (
DML) (
DELETE,
INSERT o
UPDATE)
.[2]
Oracle le permite definir procedimientos llamados Triggers que se ejecutan de manera implícita cuando un DELETE, INSERT o UPDATE se emite en una tabla asociada o, en algunos casos, en una vista, o hasta cuando se producen acciones en el sistema de Base de Datos. Estos procedimientos pueden ser escritos en PL/SQL o Java y se almacenan en la Base de Datos.
Los
Triggers son similares a los procedimientos almacenados. Un
Trigger almacenado en la Base de Datos puede incluir
SQL y
PL/SQL o
Java para funcionar como una unidad y puede invocar procedimientos almacenados. Sin embargo, los procedimientos y
Triggers difieren en la forma en que se invocan. Un procedimiento se ejecuta de forma explícita por un usuario, aplicación o por un Trigger. Los
Triggers se ejecutan de forma implícita por parte de
Oracle cuando se produce un evento de activación, sin importar qué usuario está conectado o la aplicación que se está utilizando
.[3]
Nota: El nombre mas común es Triggers pero también son conocidos como Desencadenadores o Disparadores.
Sintaxis:
CREATE [ OR REPLACE ] TRIGGER [ schema. ]trigger
{ BEFORE | AFTER | INSTEAD OF }
{ dml_event_clause
| { ddl_event [ OR ddl_event ]...
| database_event [ OR database_event ]...
}
ON { [ schema. ]SCHEMA
| DATABASE
}
}
[ WHEN (condition) ]
{ pl/sql_block | call_procedure_statement } ;
____________________________________________________________________________________
Sus Usos:
Los Triggers complementan las capacidades básicas de Oracle para proporcionar un sistema de gestión de Base de Datos altamente personalizado. Por ejemplo, un Trigger puede restringir las operaciones DML en una tabla para que solo se ejecuten en horario de oficina. También pueden ser usados para:
• Generar automáticamente valores derivados en columnas.
• Evitar transacciones inválidas.
• Imponer complejas autorizaciones de seguridad.
• Exigir integridad referencial entre los nodos en una Base de Datos distribuida.
• Hacer cumplir reglas de negocio complejas.
• Proporcionar un registro de eventos transparente.
• Proporcionar auditoría.
• Mantener réplicas síncronas de tablas.
• Recopilar estadísticas de acceso en tablas.
• Modificar datos de una tabla cuando se emiten sentencias DML en vistas.
• Publicar información sobre eventos de Base de Datos, eventos de usuario, y sentencias SQL a aplicaciones suscritas.
Evento Disparador:
Un evento disparador es una sentencia SQL, evento de Base de Datos, o evento de usuario que aprieta el gatillo o provoca la activación del Trigger. Los cuales pueden ser:
• Un INSERT, UPDATE o DELETE en una tabla específica (o una vista, en algunos casos).
• Un CREATE, ALTER o DROP en cualquier objeto de esquema.
• Un arranque(startup) de Base de Datos o un cierre(shutdown) de instancia.
• Un mensaje de error en particular o cualquier mensaje de error.
• Un inicio o cierre de sesión de un usuario de Base de Datos.
____________________________________________________________________________________
Tipos de Triggers:
• Triggers de fila y Triggers de sentencia (Row Triggers and Statement Triggers).
• Antes y después (BEFORE and AFTER Triggers).
• Triggers EN LUGAR DE (INSTEAD OF Triggers).
• Triggers de eventos del sistema y eventos de usuario (Triggers on System Events and User Events).
Triggers de fila y Triggers de sentencia (Row Triggers and Statement Triggers).
Al definir un Trigger, puede especificar el número de veces que la acción de disparo se va a ejecutar:
• Triggers de fila/Row Triggers: Una vez por cada fila afectada por la sentencia de activación, ejemplo: se dispara por una sentencia UPDATE que actualiza varias filas. Si una sentencia de activación no afecta alguna fila, el Trigger de fila no se ejecuta.
• Triggers de sentencia/Statement Triggers: Una vez por la sentencia de activación sin importar el número de filas que afecta.
Antes y después (BEFORE and AFTER Triggers).
También puede especificar el tiempo(cuando) de disparo; esto es, si la acción se va a ejecutar antes o después de la sentencia de activación. Los Triggers Antes y Después pueden aplicarse/combinarse con los Triggers de fila y Triggers de sentencia.
Los Triggers Antes y Después son activados/lanzados por sentencias DML y puede definirse sólo en tablas, no en vistas. Sin embargo, los Triggers en las tablas base de una vista se disparan si un INSERT, UPDATE o DELETE se emite en la vista. Los Triggers Antes y Después activados/lanzados por sentencias DDL sólo pueden ser definidos en la Base de Datos o un esquema, no en tablas particulares.
BEFORE Triggers:
Los BEFORE Triggers ejecutan la acción antes que la sentencia de activación se haya ejecutado. Este tipo de Triggers se utiliza comúnmente en las siguientes situaciones:
• Cuando la acción del Trigger determina si la sentencia de activación se debe permitir que complete.
• Para derivar valores específicos de columnas antes de completar una sentencia INSERT o UPDATE.
AFTER Triggers:
Los Triggers AFTER ejecutan la acción después de ejecutar la sentencia de activación. Comúnmente usados para hacer copias en tablas que sirven de espejo.
Acceso a los valores de columna en Triggers de fila:
Dentro del cuerpo de un Triggers de fila, las sentencias de SQL y PL/SQL tienen acceso a los nuevos y antiguos valores de columna en la actual fila procesada. Existen dos nombres que hacen referencia a cada columna de la tabla que se está modificado: una para el valor antiguo, y otra para el nuevo. Dependiendo del tipo de sentencia de activación, ciertos nombres podrían no tener significado.
• :OLD = Hace referencia al valor actual, el cual será modificado o eliminado.
• :NEW = Hace referencia al nuevo valor a ser establecido.
Un Trigger disparado por una sentencia INSERT solamente tiene acceso a los valores de columna nuevos (:NEW). Debido a que la fila está siendo creada por el INSERT, los viejos valores son nulos.
Un Trigger disparado por una sentencia UPDATE tiene acceso tanto a los valores antiguos(:OLD) como nuevos (:NEW) de columna, esto aplica para los Triggers de fila BEFORE y AFTER.
Un Trigger disparado por una sentencia DELETE tiene un acceso solamente a los valores de columna :OLD. Debido a que una vez suprimida, la fila ya no existe, los valores :NEW son NULL. Nota: no es posible modificar los nuevos valores, de tratar se hacerlo una EXCEPCIÓN se levantaría.
Los antiguos (:OLD) y nuevos (:NEW) valores están disponibles tanto antes (BEFORE) como después (AFTER) en los Triggers de fila. Un valor :NEW puede ser modificado en un Trigger de fila BEFORE, pero no en un Trigger de fila AFTER (esto porque la sentencia de activación es disparada antes que el Trigger de fila AFTER sea lanzado). Si un Trigger de fila BEFORE cambia el valor de :new.column, entonces, un Trigger de fila AFTER disparado por la misma instrucción ve el último valor asignado en el Trigger de fila BEFORE.
Los identificadores NEW y OLD pueden ser usados en expresiones booleanas de una cláusula WHEN. Notar que dos puntos (:) deben preceder a ambos identificadores cuando se utilizan dentro del cuerpo del Trigger, pero esto no aplica cuando los mismo son usados la cláusula WHEN o en la opción REFERENCING.
INSTEAD OF Triggers:
Los INSTEAD OF Triggers proporcionan una manera transparente de modificar vistas que no deben ser modificadas directamente a través de sentencias DML (INSERT, UPDATE y DELETE). Estos Triggers son llamados INSTEAD OF Triggers, ya que, a diferencia de otros tipos, Oracle dispara el Trigger en lugar de ejecutar la sentencia de activación.
Puede escribir sentencias INSERT, UPDATE y DELETE en vistas y el INSTEAD OF Trigger se dispara para actualizar las tablas subyacentes de manera apropiada. Los INSTEAD OF Triggers se activan por cada fila de la vista modificada.
Triggers de eventos del sistema y eventos de usuario (Triggers on System Events and User Events).
Puede utilizar Triggers para publicar información sobre eventos de Base de Datos a los suscriptores. Las aplicaciones pueden suscribirse a eventos de Base de Datos tal y como se suscriben a mensajes de otras aplicaciones. Estos eventos de Base de Datos pueden incluir:
• Eventos del sistema.
--Puesta en marcha(startup) y cierre(shutdown) de la Base de Datos.
--Eventos de error del servidor.
• Eventos de usuario.
--Cuando el usuario inicia o cierra sesión.
--Sentencias DDL (CREATE, ALTER y DROP).
--Sentencias DML (INSERT, DELETE y UPDATE).
Los Triggers de eventos del sistema se pueden definir a nivel de Base de Datos o a nivel de esquema. Por ejemplo, un Trigger de cierre de Base de Batos se define a nivel de Base de Batos:
CREATE OR REPLACE TRIGGER trg_inicio_seccion
ON DATABASE STARTUP
BEGIN
...
DBMS_AQ.ENQUEUE(...);
...
END;
/**/
---
Los Triggers de sentencias DDL o eventos de inicio/cierre de sesión también se puede definir a nivel de Base de Datos o a nivel de esquema. Los Triggers en sentencias DML se pueden definir en una tabla o vista. Un Trigger definido a nivel de Base de Datos es disparado para todos los usuarios, mientras que un Trigger definido a nivel de esquema o tabla, ejecuta sólo cuando el evento de activación implica el esquema o tabla en cuestión.
____________________________________________________________________________________
Eliminar o Deshabilitar un Trigger.
Puede eliminar un Trigger con la sentencia DROP TRIGGER seguido del nombre del Trigger. Tome en cuenta en el Trigger debe estar en su propio esquema, de no estarlo, debe tener el privilegio de sistema DROP ANY TRIGGER. Del mismo modo si desea eliminar un Trigger de Base de Datos debe tener el privilegio ADMINISTER DATABASE TRIGGER.
Sintaxis:
DROP TRIGGER trigger_name;
Adicionalmente puede deshabilitar, habilitar o compilar un Trigger de Base de Datos con la sentencia ALTER TRIGGER. De igual manera si el Trigger pertenece a otro esquema debe tener el privilegio ALTER ANY TRIGGER, y si el Trigger es de Base de Datos debe tener el privilegio ADMINISTER DATABASE TRIGGER.
Sintaxis:
ALTER TRIGGER [ schema. ] trigger
{ { ENABLE | DISABLE }
| RENAME TO new_name
| COMPILE [ DEBUG ] [ compiler_parameters_clause ... ] [ REUSE SETTINGS ]
};
____________________________________________________________________________________
Ejemplos:
CREATE OR REPLACE TRIGGER trg_secure_history
BEFORE INSERT OR UPDATE OR DELETE ON job_history
BEGIN
IF USER NOT IN ('SYSTEM','SYS') THEN
RAISE_APPLICATION_ERROR(-20001,'Esta tabla no debe ser modificada bajo ninguna circunstancia');
END IF;
END trg_secure_history;
/
SHOW ERROR
/*Creamos el Trigger: trg_secure_history, el cual restringe a todos usuarios (excepto system y sys) el privilegio de modificar los datos de la tabla job_history. Este Trigger cabe en la clasificación de Trigger de sentencia y BEFORE Trigger.*/
---
INSERT INTO job_history
(
employee_id,
start_date,
end_date,
job_id,
department_id
)
VALUES
(
101,
ADD_MONTHS(SYSDATE,-18), --resta 18 meses a fecha actual.
SYSDATE,
'IT_PROG',
100
);
/*Conectados con hr, probamos el Trigger: trg_secure_history tratando de insertar un nuevo registro a la tabla: job_history.*/
---OUTPUT:
ALTER TRIGGER update_job_history
DISABLE;
/*El ejemplo deshabilita el Trigger: update_job_history el cual viene por defecto en la tabla employees del esquema hr.*/
---
DROP TRIGGER trg_secure_history;
/*El ejemplo elimina el Trigger: trg_secure_history creado anteriormente.*/
---
CREATE OR REPLACE TRIGGER update_history
AFTER UPDATE OF job_id, department_id OR DELETE ON employees
FOR EACH ROW
BEGIN
INSERT INTO hr.job_history
(
employee_id,
start_date,
end_date,
job_id,
department_id
)
VALUES
(
:OLD.employee_id,
:OLD.hire_date,
SYSDATE,
:OLD.job_id,
:OLD.department_id
);
END actualiza_history;
/
SHOW ERROR
/*Creamos el trigger: update_history, el cual se activa cada vez que se modifica el campo job_id o department_id de la tabla employees, así como también si se elimina un registro de la misma. El Trigger inserta en la tabla job_history un registro con los datos previos a la modificación. Este es un Trigger de fila AFTER.*/
---
UPDATE employees
SET job_id = 'PU_CLERK'
WHERE employee_id = 206;
/*Para probar el Trigger, cambiamos el tipo de empleo al empleado 206.*/
---
SELECT *
FROM job_history
WHERE employee_id = 206;
/*Consultamos la tabla job_history.*/
---OUTPUT:
CREATE OR REPLACE VIEW dept_vw
(
cod_dept,
nomb_dept,
en_uso
)
AS
SELECT
department_id,
department_name,
CASE
WHEN 0 < (
SELECT NVL(COUNT(*), 0)
FROM employees e
WHERE e.department_id = d.department_id
) THEN 'S'
ELSE 'N'
END
FROM departments d;
/*Creamos la vista: dept_vw que nos muestra el nombre y código de cada departamento de la empresa, dicha vista posee el campo virtual: en_uso que nos dice si dicho departamento tiene empleados trabajando en él. Nota: Esta vista permite la modificación de los datos de la tabla base (departments) a través de ella.*/
---
SELECT *
FROM dept_vw;
/*Consultamos nuestra vista.*/
---OUTPUT:
CREATE OR REPLACE TRIGGER trg_dept_secure
INSTEAD OF DELETE ON dept_vw
BEGIN
RAISE_APPLICATION_ERROR(-20023, 'Este operación no es permitida, contacte el Administrador!.');
END;
/
SHOW ERROR
/*Creamos un Trigger que impida eliminar datos de la tabla departments a través de la vista: dept_vw.*/
---
DELETE FROM dept_vw
WHERE cod_dept = 240;
/*Tratamos de eliminar el departamento 240 a través de la vista: dept_vw.*/
---OUTPUT:
_____________________________________________________________________________________
Fuentes: Oracle Database: PL/SQL Fundamentals,
https://docs.oracle.com/cd/B10500_01/server.920/a96524/c18trigs.htm,
https://docs.oracle.com/cd/B19306_01/server.102/b14200/statements_7004.htm
Objetivos:
