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.




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.




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




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.




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.




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.




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




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.




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

descencadenantes.pdf

Documents