Download - descencadenantes.pdf
-
UNIVERSIDAD NACIONAL DE SAN CRISTOBAL DE HUAMANGA FACULTAD DE ING. MINAS, GELOGIA Y CIVIL
ESCUELA DE FORMACIN PROFESIONAL DE INGENIERIA DE SISTEMAS
Lab. Administracin de Base de Datos 1 Prof. Elvira Fernndez (IS-443)
DESENCADENANTES O TRIGGERS
I. OBJETIVOS Comprender el concepto de trigger. Estudiar los componentes del trigger Conocer los tipos de triggers Razonar la forma de utilizacin del trigger
II. MARCO TERICO
DESENCADENADORES Al igual que los procedimientos y funciones, los desencadenadores llamados tambin disparadores o trigger son un conjunto de instrucciones SQL cuya diferencia es que se ejecutan solos y en forma automtica cuando se modifica el contenido de una tabla o vista, es decir cuando utilizamos INSERT, UPDATE O DELETE. LA SINTAXIS
CREATE OR REPLACE TRIGGER Nombre TIEMPO evento1 [ OR evento2 OR evento 3.] -- instruccin ON TABLA
For Each Row DECLARE Definicin de variables BEGIN Instrucciones del desencadenador END;
Donde:
NOMBRE Es el nombre del desencadenador.
TIEMPO Se utiliza para indicar si las instrucciones del desencadenador se deben
ejecutar antes o despus de actualizarse la tabla, es decir, antes o despus que se haya ejecutado la instruccin Insert, Update o Delete.
INSTRUCCIN Es la instruccin Insert, Update y/o Delete que har que se ejecuten las instrucciones del desencadenador. Si desea que las instrucciones del desencadenador se ejecuten en dos o en las tres instrucciones, debe separarlas por el operador or.
-
UNIVERSIDAD NACIONAL DE SAN CRISTOBAL DE HUAMANGA FACULTAD DE ING. MINAS, GELOGIA Y CIVIL
ESCUELA DE FORMACIN PROFESIONAL DE INGENIERIA DE SISTEMAS
Lab. Administracin de Base de Datos 2 Prof. Elvira Fernndez (IS-443)
Por ejemplo, si escribe INSERT OR DELETE, las instrucciones del desencadenador se ejecutaran cuando se agreguen o eliminen registros en la tabla para lo cual se crea el desencadenador.
TABLA Es el nombre de la tabla que debe de estar asociada al desencadenador, es decir, la tabla donde debe ejecutarse Insert, Update y/o Delete para que las instrucciones del desencadenador se ejecuten. Para poder crear los desencadenadores el usuario debe tener los privilegios respectivos como:
Create Any trigger
alter Any trigger
drop Any trigger
TIPO Y TIEMPO DE TRIGGERS En el parmetro tiempo se puede utilizar cualquiera de las siguientes palabras:
BEFORE
Le indica a oracle que las instrucciones del desencadenador se deben ejecutar antes de actualizarse la tabla.
AFTER
Le indica a oracle que las instrucciones del desencadenador se deben ejecutar despus de actualizarse la tabla.
INSTEAD OF
Le indica a oracle que ejecute las instrucciones del desencadenador en lugar de la instruccin que lo invocan o desencadenan.
-
UNIVERSIDAD NACIONAL DE SAN CRISTOBAL DE HUAMANGA FACULTAD DE ING. MINAS, GELOGIA Y CIVIL
ESCUELA DE FORMACIN PROFESIONAL DE INGENIERIA DE SISTEMAS
Lab. Administracin de Base de Datos 3 Prof. Elvira Fernndez (IS-443)
Ejemplo01 : Crear un desencadendor tr_insertar_proveedor, de tal manera que utilizando
un cursor muestre los registros principales de la tabla despues de agregarse (insert) un
nuevo registro en dicha tabla.
Invocando al descencadenantes, para ello insertar un registro en la tabla proveedor.
INSERT INTO PROVEEDOR (IDPROVEEDOR,NOMBRE,
REPRESENTANTE,CIUDAD)
VALUES ( 12, 'PERFUMES', 'MARIA RODRIGUEZ', 'LIMA');
INDICAR EL MODO DE EJECUCION DE LAS INSTRUCCIONES
Al crear un desencadenador usted puede indicar si las instrucciones que contiene se deben
ejecutar por la instruccin que genera el desencadenador: insert, update o delete(una sola
vez) o por cada registro de la tabla que se actualice (N veces).
En forma predeterminada las instrucciones del desencadenador se ejecutan slo una vez
cunando se aplica la instruccin insert, update o delete en la tabla indicada.
Si desea que las instrucciones del desencadenador se ejecuten por cada registro que se
agrega, actualiza o elimina, debe utilizar en la creacin del desencadenador el parametro:
For Each Row (por cada fila o registro) .
-
UNIVERSIDAD NACIONAL DE SAN CRISTOBAL DE HUAMANGA FACULTAD DE ING. MINAS, GELOGIA Y CIVIL
ESCUELA DE FORMACIN PROFESIONAL DE INGENIERIA DE SISTEMAS
Lab. Administracin de Base de Datos 4 Prof. Elvira Fernndez (IS-443)
EJEMPLO02
Las siguientes instrucciones crean un desencadenador llamado Tr_actualizaProveedor que
muestra una sola vez el mensaje: registro actualizado cada vez que se utiliza la instruccin
update en la tabla proveedor sin importar la cantidad de registros que se actualizan.
Ejercicio 02: actualice un campo cualquiera de la tabla proveedor.
Salida
Como la creacin del desencadenador Tr_actualizaProveedor no se ha utilizado el
parmetro For Each Row, las instrucciones del desencadenador se ejecutan una sola vez
aunque se hayan actualizado todoslos registros de la tabla proveedor.
EJEMPLO03: modifiquemos el desencadenador anterior y agreguemsle el
parmetro For Each Row, de tal manera que sus instrucciones se ejecuten por cada
registro que se actualice, es decir, el mensaje se visualizar varias veces.
Ejercicio 03: actualice el campo telefono de la tabla proveedor. Verifique los resultados.
CONDICIONAR LA EJECUCION DE LAS INSTRUCCIONES
En forma predeterminada las instrucciones de un desencadenador se ejecutan cuando se
aplica un insert, update y/o delete en la tabla indicada y sin ninguna otra condicin.
-
UNIVERSIDAD NACIONAL DE SAN CRISTOBAL DE HUAMANGA FACULTAD DE ING. MINAS, GELOGIA Y CIVIL
ESCUELA DE FORMACIN PROFESIONAL DE INGENIERIA DE SISTEMAS
Lab. Administracin de Base de Datos 5 Prof. Elvira Fernndez (IS-443)
En oracle podemos establecer 2 tipos de condiciones para que se ejecuten o no las
instruccciones de un desencadenador.
CUANDO SE ACTUALIZA UN DETERMINADO CAMPO
Utilizando la siguiente sintaxis podemos indicar a oracle que slo se ejecuten las
instrucciones del desencadenador cuando se actualicen determinados campos.
CREATE OR REPLACE TRIGGER Nombre TIEMPO Update of nombre del campo-- instruccin ON TABLA DECLARE Definicin de variables
BEGIN Instrucciones del desencadenador
END;
Si se van a condicionr varios campos, deben ir separados por comas.
CREATE OR REPLACE TRIGGER Nombre TIEMPO Update of campo1, campo2,,campoN -- instruccin ON TABLA
For Each Row DECLARE Definicin de variables BEGIN Instrucciones del desencadenador
END;
Ejercicio 03: modifique el ejmplo Tr_actualizaProveedor , para modificar el email de un
proveedor X , de tal manera que solo se ejecute el desencadenate cuando actualice el
campo email de la tabla proveedor. Verifique los resultados cuando actualice el telefono
del proveedor X.
La segunda forma de condicionar la ejecucin de un descencdenador se explica despues de
conocer sus tablas temporales.
VARIABLES TEMPORALES DE LOS DESENCADENADORES
La verdadera potencia de los desencadenadores en la administracin de la base de datos
oracle se obtiene utilizando sus variables temporales.
Las variables temporales de un desencadendor son dos llamadas en forma predeterminada
old y new las actuales se crean en forma automtica y temporal durante la ejecucion del
desencadenador.
Las variables old y new se crean de tipo registro y con la misma estructura de la tabla
utilizada en el desencadenador y cada una de ellas almacen un registro.
-
UNIVERSIDAD NACIONAL DE SAN CRISTOBAL DE HUAMANGA FACULTAD DE ING. MINAS, GELOGIA Y CIVIL
ESCUELA DE FORMACIN PROFESIONAL DE INGENIERIA DE SISTEMAS
Lab. Administracin de Base de Datos 6 Prof. Elvira Fernndez (IS-443)
Variable old El contenido de esta variable depende de la instruccin que genera o ejecuta el
desencadenador.
Delete si la instruccin que genera el desencadendor es delete, la variable old almacena los datos
del registro eliminado. De esta manera podemos saber y leer los datos del registro
eliminado.
Update si la instruccin que genera el desencadendor es update, la variable old almacena los datos
del registro actualizado, pero, con los datos anteriores, es decir, son los datos antes de
actualizarse. De esta manera podemos saber qu registro se actualiz y qu datos o campos
se actualizaron.
Insert La variable old no almacena ningn registro cuando la instruccin que genera el
desencadenador es insert, es decir , no se utiliza.
Variable New El contenido de esta variable depende de la instruccin que genera o ejecuta el
desencadenador.
Delete La variable new no almacena ningn registro cuando la instruccin que genera el
desencadenador es delete, es decir, no se utiliza.
Update si la instruccin que genera el desencadendor es update, la variable new almacena los datos
del registro actualizado con los nuevos datos. De esta manera podemos saber qu registro
se actualiz y qu datos o campos se actualizaron.
Insert si la instruccin que genera el desencadendor es insert, la variable new almacena el nuevo
registro agregado a la tabla. De esta manera podemos saber y leer los datos del nuevo
registro.
Para referirse a un campo de las variables old o new se utiliza la siguiente sintaxis:
:nombre_de_la variable.nombre_campo
Nota : para poder utilizar las variables old y new dentro de un desencadenador debemos
indicar que las instrucciones se deben ejecutar registro por registro.
EJEMPLO03
Se crea un desencadenador llamado Tr_actualizaEmpleado_AntesYDespues, cuyas
instrucciones se ejecutarn registro por registro y antes de actualizarse algn registro de la
tabla empleados. Este desencadenador muestra los datos de los registros antes y despues de
actualizarse.
-
UNIVERSIDAD NACIONAL DE SAN CRISTOBAL DE HUAMANGA FACULTAD DE ING. MINAS, GELOGIA Y CIVIL
ESCUELA DE FORMACIN PROFESIONAL DE INGENIERIA DE SISTEMAS
Lab. Administracin de Base de Datos 7 Prof. Elvira Fernndez (IS-443)
Ejercicio 03: actualice el campo nombre y haber basico de un empleado X de la tabla
empleados. Mostrar los resultados.
Salida
EJECUTAR LAS INSTRUCCIONES ASIGNANDO UNA CONDICIN GENERAL
Utilizando la siguiente sintaxis podemos indicar a oracle que slo se ejecuten las
instrucciones del desencadenador cuando se cumpla una condicin general establecida al
crearlo.
CREATE OR REPLACE TRIGGER Nombre TIEMPO instruccin ON TABLA
For Each Row
When condicin DECLARE
-
UNIVERSIDAD NACIONAL DE SAN CRISTOBAL DE HUAMANGA FACULTAD DE ING. MINAS, GELOGIA Y CIVIL
ESCUELA DE FORMACIN PROFESIONAL DE INGENIERIA DE SISTEMAS
Lab. Administracin de Base de Datos 8 Prof. Elvira Fernndez (IS-443)
Definicin de variables BEGIN Instrucciones del desencadenador END;
Condicin
Es aquella que se debe cumplir para que se pueda ejecutar las instrucciones del
desencadenador
EJEMPLO03
El siguiente ejemplo utiliza la tabla empleados y crea un desencadenador con una
condicin general.
Las siguientes instrucciones de ejemplo crean al desencadenador cuyas
instrucciones se ejecutan cuando se actualiza un sueldo bsico mayor a 800 soles
Ejercicio 04: modifique el haber basico de un empleado de la tabla empleados.
Mostrar los resultados.
ACTIVAR Y DESACTIVAR UN DESENCADENADOR
Despus de crear un desencadenador queda activado y se ejecuta cada vez que se
cumplan las condiciones para el que fue creado. Si se crea mas de un
desencadenador para las mismas condiciones o instrucciones siempre se ejecutarn
como en los dos casos anteriores que fueron creados para la instruccin update.
Para desactivar un desencadenador, utilice la siguiente sintaxis:
Alter Trigger nombre Disable
Y si desea activarlo, utilice la siguiente sintaxis:
Alter Trigger nombre Enable
Ejemplo:
Alter Trigger Tr_actualizaProveedor Disable
El comando alter table permite desactivar o activar todos los desencadenadores
creados para una tabla.
-
UNIVERSIDAD NACIONAL DE SAN CRISTOBAL DE HUAMANGA FACULTAD DE ING. MINAS, GELOGIA Y CIVIL
ESCUELA DE FORMACIN PROFESIONAL DE INGENIERIA DE SISTEMAS
Lab. Administracin de Base de Datos 9 Prof. Elvira Fernndez (IS-443)
Alter Trigger nombre_tabla Disable all triggers
Y si desea activar todos los desencadenadores de una tabla, utilice la siguiente
sintaxis:
Alter Trigger nombre_tabla Enable all triggers
VISUALIZAR LOS DESENCADENADORES
La informacin de los desencadenadores se almacena en la tabla user_triggers y
podemos leerla con la instruccion describe y select.
La siguiente instruccin muestra la estructura de la tabla user_triggers;
Describe user_triggers
Y la siguiente instruccin muestra toda la informacin de los desencadenadores:
Select * from user triggers
ELIMINAR UN DESENCADENADOR La instruccion Drop Trigger permite eleiminar fisicamente un desencadenador de la
base de datos oracle.
Sisntaxis:
Drop trigger nombre
Ejercicio: 1. Crear un desencadenante llamado tr_actualiza_stock, que permita actualizar el
stock de un producto cada vez que se registra una venta.
2. Crear un desencadenante, que permita actualizar el stock cada vez que se elimina un pedido de una venta realizada.mostrar el stock antes y despues.
3. Crear un desencadenante, que permita actualizar el stock de un producto cada vez que se modifica la cantidad de una venta realizada.
4. Crear 2 desencadenantes que crea conveniente