Procedimientos Almacenados y Disparadores

Unidad 6

Disparadores (Triggers)

CaracterísticasLos disparadores solo ocurren cuando se

presentan determinados eventos

Los tipos de eventos permitidos suelen ser Insertar, Eliminar o Actualizar una tupla en particular

Funcionamiento GeneralSe prueba una condición, si ésta no se

cumple, ninguna otra acción asociada al disparador se produce en respuesta al evento

Si se cumple la condición del disparador, el sistema de administración de la base de datos realiza la acción asociada a él

CaracterísticasLa acción puede ejecutarse antes del evento

disparador, después de él, o en lugar de él

La acción puede referirse a valores anteriores y nuevos que se insertaron, eliminaron o actualizaron en el evento que desencadenó la acción

Los eventos de actualización pueden especificar un atributo particular o un conjunto de atributos

CaracterísticasUna condición puede especificarse con una

cláusula WHEN, y la acción se ejecutará sólo si se dispara la regla y se cumple la condición cuando ocurre el evento que la desencadenó

El programador tiene la opción de de especificar que la acción se realiza:

Una vez en cada tupla modificada Una vez en todas las tuplas que cambian en

una operación de la base de datos

Estructura Básica de un Disparador (Trigger)CREATE TRIGGER nombre_dispmomento_disp evento_disp

ON nombre_tabla FOR EACH ROWsentencia_disp

EstructuraLa primera sentencia, “CREATE TRIGGER

nombre_disp” creará el trigger

momento_disp se refiere a en que momento se debe ejecutar la regla, las opciones disponibles son:

AFTER. La condición se prueba antes del evento disparador

BEFORE. La condición se prueba después de que el evento disparador

Evento DisparadorEvento disparador indica la clase de

sentencia que activa el disparador, puede ser INSERT, UPDATE o DELETE

No puede haber dos disparadores en una misma tabla que correspondan al mismo momento y sentencia

Por ejemplo, no se pueden tener dos disparadores BEFORE UPDATE.

Pero sí es posible tener los disparadores BEFORE UPDATE y BEFORE INSERT o BEFORE UPDATE y AFTER UPDATE

EstructuraLa frase FOR EACH ROW, expresa la

exigencia de que une Trigger se ejecute una vez por cada tupla actualizada

La sentencia disparadora es la que se ejecuta cuando se activa el disparador, las sentencias deben colocarse entre BEGIN ... END

Valores Nuevos o ViejosLas columnas de la tabla asociada con el

disparador pueden referenciarse empleando los alias OLD y NEW

OLD.nombre_col hace referencia a una columna de una fila existente antes de ser actualizada o borrada

NEW.nombre_col hace referencia a una columna en una nueva fila a punto de ser insertada, o en una fila existente luego de que fue actualizada

DelimitadoresCuando se escribe un trigger, se utiliza el

punto y como para finalizar sentencias, esto puede causar ciertos errores, por eso es conveniente crear un delimitador para que no reconozca varios puntos y comas normales como el fin del mismo

DELIMITER delimitador_deseadoDELIMITER $$DELIMITER ; Vuelve a manejar punto y coma

como el delimitador

Manejo de TriggersListar los Triggers

SHOW TRIGGERS;

Listar el código del Procedimiento SHOW CREATE TRIGGER nombre_trigger

Eliminar un Trigger DROP TRIGGER IF EXISTS nombre_trigger

Procedimiento Almacenado (Store Procedure)

DefiniciónUn procedimiento almacenado o Stored

Procedure es un programa que se almacena en una base de datos

Sus usos más comunes se relacionan con la validación de de datos o encapsular un proceso más grande

Su implementación depende del controlador de bases de datos que se está utilizando

VentajasPueden incrementar el desempeño de las

aplicaciones ya que en lugar de enviar múltiples sentencias, solo envía una llamada a un procedimiento que está almacenado

Pueden ser compartidos entre varias aplicaciones

Ofrecen mayor seguridad ya que solamente se tiene acceso a la llamada y no a las sentencias de esta forma se pueden usar sin tener conocimiento de la estructura de las tablas

DesventajasEntre más grande sea el procedimiento

almacenado más recursos del equipo requiere

Puede ser complicado realizar, analizar o modificar procedimientos complejos y grandes

DelimitadoresEs necesario crear un delimitador para que

no se tome al punto y coma como delimitador en cuanto al inicio y al final del procedimiento

El funcionamiento es similar al de un delimitador en los Disparadores (Triggers)

Creación de un ProcedimientoLa sentencia básica para crear

procedimiento almacenado es:

CREATE PROCEDURE nombre()BEGINEND

Llamada a un ProcedimientoPara llamar a un procedimiento almacenado

se utiliza la sentencia CALL seguido del nombre del procedimiento deseado

CALL nombreProcedimiento();

Variables en un Procedimiento AlmacenadoPara declarar una variable se utiliza la

sentencia:DECLARE nombre TIPO_DATO (TAMAÑO)

DEFAULT valor_defecto;

Para asignar valores a una variable se utiliza la siguiente sentencia: SET

SET nombre_variable = valor

Variables en un Procedimiento AlmacenadoEs posible usar la sentencia SELECT/INTO

para asignar un valor a una variable previamente declarada

SELECT COUNT (*) INTO nombre_variable FROM tabla

Acceso a una VariableSe puede tener acceso a una variable dentro

del ámbito donde fue declarada, en este caso dentro de las sentencias BEGIN y END

Se puede declarar una variable tipo de sesión colocando la @ antes del nombre de la variable

Parámetros en un ProcedimientoEs posible manejar parámetros en un

procedimiento almacenado, se tienen tres tipos de parámetros IN, OUT o INOUT

IN – El modo por defecto, cuando se declara un parámetro como IN la llamada al procedimiento debe pasarle este argumento

El valor del parámetro no se modifica incluso si es alterado dentro del procedimiento almacenado

Parámetros en un ProcedimientoOUT – El valor de un parámetro declarado

con OUT puede ser modificado dentro del procedimiento almacenado y es regresado a la llamada al procedimiento

INOUT – Es la combinación de IN y OUT lo que significa que el programa que hace la llamada envía el argumento, y el procedimiento puede modificarlo

Condicional IFPara colocar sentencias condicionales se

tienen las instrucciones IF, ELSEIF, ELSE siempre se debe comenzar con un IF y cerrar con un END IF, en medio de ellas pueden ir ELSEIF o ELSE, para indicar que comienza un bloque de sentencias se utiliza la instrucción THEN

Condicional CASEAdemás de las sentencias IF, también se

cuenta con el condicional CASE CASE expresion WHEN valor_1 THEN comandos WHEN valor_2 THEN comandos WHEN valor_3 THEN comandos ELSE comandos END CASE;

Ciclo WhileEl ciclo WHILE se utiliza al igual que en otros

lenguajes de programación para repetir un conjunto de instrucciones mientras se cumpla una condición

WHILE expresion DOSentenciasEND WHILE

Ciclo RepeatEl ciclo REPEAT es el equivalente al ciclo

DO/WHILE en otros lenguajes de programación, aquí primero se ejecutan las sentencias y posteriormente se pregunta si la condición se cumple, por lo que la instrucción se ejecutará al menos una vez.

REPEATSentenciasUNTIL ExpresionEND REPEAT

Loop, Leave, IterateLa sentencia LOOP es equivalente a la

expresión GOTO en algunos lenguajes de programación, en donde se puede indicar que se vaya a una parte de código de manera similar a un ciclo.

Los auxiliares LEAVE e ITERATE se pueden colocar dentro de un ciclo LOOP para alterar su funcionamiento

LEAVE es similar al funcionamiento de la sentencia break en otros lenguajes de programación

ITERATE es similar en cuanto a funcionamiento de la sentencia continue.

Manejo de ProcedimientosListar los Procedimientos

SHOW PROCEDURE STATUS WHERE db='nombre_base_datos';

Listar el código del Procedimiento SHOW CREATE PROCEDURE

nombre_procedimiento

Eliminar un Procedimiento DROP PROCEDURE IF EXISTS

nombre_procedimiento