MYSQL PROCEDURAL

Crear Procedimientos Almacenados en MySQL

junio 12, 2014 James Revelo

Un procedimiento es un conjunto de instrucciones que se guardan en el servidor para un

posterior uso, ya que se ejecutarn frecuentemente. En MySQL se nombran con la
clausula PROCEDURE.
A diferencia de las funciones, los procedimientos son rutinas que no retornan en ningn tipo
de valor. Simplemente se llaman desde el cliente con un comando y las instrucciones dentro
del procedimiento se ejecutarn.

Ventajas de usar Procedimientos en MySQL

Seguridad: Los procedimientos ocultan el nombre de las tablas a usuarios que no tengan
los privilegios para manipular datos. Simplemente llaman los procedimientos sin conocer la
estructura de la base de datos.

Estndares de cdigo: En un equipo de desarrollo usar el mismo procedimiento permite

crear sinergia en las fases de construccin. Si cada programador crea su propio
procedimiento para realizar la misma tarea, entonces podran existir problemas de
integridad y perdida de tiempo

Velocidad: Es mucho mas fcil ejecutar un programa ya definido mediante ciertos

parmetros, que reescribir de nuevo las instrucciones.

Crear un Procedimiento en MySQL

La creacin de un procedimiento se inicia con las clausulas CREATE PROCEDURE. Luego
definimos un nombre y los parmetros que necesita para funcionar adecuadamente. Veamos
su sintaxis:
CREATE PROCEDURE nombre ([parmetro1,parmetro2,...])
[Atributos de la rutina]
BEGIN instrucciones
END

Un procedimiento puede tener uno o mas parmetros o tambin no tener ninguno. Puede
carecer de atributos o puede poseer varios. Y como ves, el cuerpo del procedimiento es
un bloque de instrucciones definido.

Parmetros de Entrada y Salida en un Procedimiento

Un parmetro es un dato necesario para el funcionamiento del procedimiento, ya que
contribuyen al correcto desarrollo de las instrucciones del bloque de instrucciones.
Los parmetros pueden ser de entrada (IN), salida (OUT) o entrada/salida (INOUT) y deben
tener definido un tipo. Un parmetro de entrada en un dato que debe ser introducido en la
llamada del procedimiento para definir alguna accin del bloque de instrucciones.
Un parmetro de salida es un espacio de memoria en el cual el procedimiento devolver
almacenado su resultado. Y un parmetro de entrada/salida contribuye tanto como a ingresar
informacin til como para almacenar los resultados del procedimiento. Por defecto, si no
indicas el tipo de parmetro MySQL asigna IN.
Para especificar el tipo de parmetro seguimos la siguiente sintaxis:

[{IN|OUT|INOUT} ] nombre TipoDeDato

Atributos de un Procedimiento en MySQL

Son caractersticas adicionales para establecer la naturaleza del procedimiento. Veamos la
utilidad de algunas:
LANGUAGE SQL: Indica que el procedimiento se escribir en lenguaje estndar SQL/PSM.

Pero su utilidad se basa en la suposicin de que en el futuro los procedimientos podran ser
escritos en otros lenguajes como Php, Java, etc. Ya que aun los escribimos en SQL entonces
no es necesario ponerlo.
SQL SECURITY {DEFINER|INVOKER}: Establece el nivel de seguridad de invocacin de un
procedimiento. Si usas DEFINER el procedimiento sera ejecutado con los permisos del usuario
que lo cre, y si usas INVOKER ser ejecutado con los permisos del usuario que lo esta
invocando.
[NOT] DETERMINISTIC: Especifica si el procedimiento devolver siempre el mismo resultado
al ingresar los mismo parmetros. O si devolver distintos resultados al ingresar los mismo
resultados. Un ejemplo sera ingresar la suma 1+2, se sabe que siempre el resultado ser 3,
as que usamos DETERMINISTIC. Pero si el parmetro es un valor de un retiro de cuenta
bancaria, el resultado del saldo que queda ser diferente sin importar la cantidad retirada.
NO SQL|CONTAINS SQL|READS SQL DATA|MODIFIES SQL DATA: Estas caractersticas
determinan la estructura del procedimiento. NO SQL indica que el procedimiento no contiene
sentencias del lenguaje SQL. READS SQL DATA especifica que el procedimiento lee

informacin de la base de datos mas no escribe datos. MODIFIES SQL DATA indica que el
procedimiento escribe datos en la base de datos. CONTAINS SQL es el tipo por defecto de un
procedimiento e indica que el procedimiento contiene sentencias SQL
COMMENT cadena: Con este atributo podemos aadir una descripcin al procedimiento con
respecto a las instrucciones que ejecuta. Por ejemplo,Este procedimiento da de baja a todos
los clientes que hace 3 meses no compran a la compaa.

Ejemplo de un Procedimiento con un parmetro IN

En el siguiente ejemplo desarrollemos un procedimiento para el siguiente requerimiento:
Imprima los nmeros del 1 hasta n, donde n esta dado por el usuario.
Usaremos un procedimiento para capturar el numero n del usuario. Incorporaremos una
variable contadora que comience en 1 y un WHILE para el incremento e impresin. Veamos:
DELIMITER //

CREATE PROCEDURE numeros_1_hasta_n (IN n INT)

BEGIN
DECLARE contador INT DEFAULT 1;
WHILE contador<=n DO
SELECT contador;
SET contador = contador + 1 ;
END WHILE;
END//

DELIMITER ;

La sentencia DELIMITER cambia el carcter de terminacin ';' por cualquier otro carcter,
en este caso elegimos '//'. Se hace con el fin de que MySQL no termine el procedimiento
al encontrar el primer punto y coma. Al final restablecemos el valor original del caracter de
escape.

Como ejecuto un procedimiento ya almacenado?

Usaremos el comando CALL enseguida del nombre del procedimiento y si tiene parmetros,
entonces se ingresan sus parmetros. Ahora veamos como llamar al anterior procedimiento:
CALL numeros_1_hasta_n(5)

Veamos el resultado:

Modificar un Procedimientos en MySQL

Para modificar un procedimiento en MySQL usaremos la sentencia ALTER PROCEDURE. Esta
modificacin permite cambiar el nivel de seguridad y la descripcin del procedimiento.
ALTER PROCEDURE nombre
[SQL SECURITY {DEFINER|INVOKER}]
[COMMENT descripcin]

Ejemplo: Cambiar la descripcin de un Procedimiento

A continuacin mostraremos un procedimiento que inserta un cliente en la base de datos.
DELIMITER //
CREATE PROCEDURE insertar(id_cliente INT, nombre_cliente VARCHAR(100), apellido_cliente
VARCHAR(100))
COMMENT 'Procedimiento que inserta un cliente a la base de datos'
BEGIN
IF NOT EXISTS ( SELECT C.ID
FROM CLIENTE AS C
WHERE C.ID = id_cliente) THEN
INSERT INTO CLIENTE(ID, NOMBRE, APELLIDO)
VALUES ( id_cliente,nombre_cliente,apellido_cliente);
ELSE
SELECT 'Este cliente ya existe en la base de datos!';
END IF;

END//

DELIMITER ;

Ahora le cambiaremos la descripcin mediante ALTER PROCEDURE:

ALTER PROCEDURE insertar_cliente
COMMENT 'Insertar Cliente'

Borrar un Procedimiento en MySQL

La sentencia DROP permite borrar un procedimiento de MySQL. Obviamente el
procedimiento debe estar almacenado con anterioridad para poder llevar a cabo la operacin.
Dado el caso de que no se encuentre creado, podemos usar el operador EXISTS junto al
condicional IF para comprobar su existencia.
DROP PROCEDURE [IF EXISTS] nombre_procedimiento

Ejemplo: Eliminar un Procedimiento antes de crearlo

La eliminacin de un procedimiento se lleva a cabo con DROP PROCEDURE. Es comn usa esta
sentencia antes de crear un procedimiento.
Como ejemplo crearemos un procedimiento llamado mostrar_clientes, el cual
simplemente consulta todas las columnas de una tabla llamada CLIENTE:
DROP PROCEDURE IF EXISTS mostrar_clientes;
CREATE PROCEDURE mostrar_clientes()
SELECT * FROM CLIENTE;

Si el procedimiento que deseamos crear ya existe, entonces lo borraremos para darle paso a
la nueva definicin.

Mostrar un Procedimiento en MySQL

Para mostrar las caractersticas de un procedimiento en MySQL usaremos la
sentencia SHOW CREATE PROCEDURE.
SHOW CREATE PROCEDURE nombre_procedimiento

Podras mostrar un ejemplo?

Por
supuesto!,
a
continuacin
mostraremos
las
caractersticas
de
un
procedimiento
llamadoHELLOWORLD
que
se
encuentra
gestionado
por phpMyAdmin de XAMPP.

Creacin de Cursores en MySQL

junio 17, 2014 James Revelo

Un cursor es un objeto que apunta a las filas retornadas de una consulta. Esta caracterstica
permite manipular los datos de cada fila de forma individual. MySQL usa la palabra
reservada CURSOR para declarar estos espacios de lectura.

Qu son los Cursores en MySQL?

Recuerda que cuando consultbamos tablas con SELECT, MySQL arrojaba rpidamente
los registros en pantalla de un solo tiro y nosotros eramos felices. Pero hay momentos donde
necesitarmos
acceder
a
cada
registro
de
forma
individual.
Lo que quiere decir que un cursor permite acceder en tiempo real a los datos de cada fila de
una consulta. Este mecanismo es de gran utilidad cuando vayamos a comunicar MySQL con
aplicativos o realizar consultas complejas.

Como usar Cursores en MySQL?

Para implementar un cursor debemos tener en cuenta 4 fases de su funcionamiento:

Declaracin

Apertura

Lectura

Cierre

1. DECLARACION
Al igual que una una variable, los cursores se declaran con la sentencia DECLARE.
Debemos declararlos despus de nuestras variables corrientes, de lo contrario MySQL,
generar un error. Veamos la sintaxis:

DECLARE nombre_cursor CURSOR FOR ;

Este sera un ejemplo:

DECLARE cursor_edad CURSOR FOR

SELECT EDAD FROM CLIENTE

WHERE NOMBRE LIKE 'a%';

No significa que el objeto cursor_edad vaya a guardar los datos de la consulta a la cual esta
referenciando. Lo que hace es apuntar a la direccin de memoria del primer resultado de
dicha consulta. Si tienes conocimientos en C++ se te har mas fcil comprender esta
interpretacin.

2. APERTURA
En la fase de declaracin la consulta a la que hace referencia el cursor, aun no se ha ejecutado.
Para ejecutarla usaremos el comando OPEN. Sin esta apertura los resultados del cursor no
pueden ser ledos por MySQL, por lo tanto se producir un error.
Debes tener en cuenta que al abrir el cursor este sita un puntero a la primera fila arrojada
por la consulta.
OPEN nombre_cursor;

3. LECTURA
La lectura de los resultados de un cursor se hace con el comando FETCH. Este nos permite
acceder a la primer fila generada por la consulta. Si se vuelve a usar el cursor pasa a apuntar
a la segunda fila, luego a la tercer y as sucesivamente hasta que el cursor no tenga resultados
que referenciar.

FETCH nombre_cursor INTO variable1,variable2,...

Es importante tener variables declaradas para almacenar temporalmente los datos de las
columnas de cada fila, generadas por la consulta. Estas variables lgicamente deben tener el
mismo tipo de dato que el valor de la columna a almacenar, y luego relacionarlas con la
sentencia
INTO.

Por ejemplo, si quisiramos almacenar el id, nombre y apellido del primer empleado de la
tabla EMPLEADO, hacemos lo siguiente:
-- Declaracin de variables para el cursor
DECLARE ID INT;
DECLARE NOMBRE VARCHAR(100);
DECLARE APELLIDO VARCHAR(100);

DECLARE cursor_cliente CURSOR

FOR SELECT ID, NOMBRE, APELLIDO FROM CLIENTE;
OPEN cursor_cliente;
FETCH cursor_cliente INTO ID,NOMBRE,APELLIDO;
CLOSE cursor_cliente;

Me imagino que intuyes que si queremos recorrer todas las filas de la consulta, necesitaremos
de alguna estructura repetitiva, no es cierto?, claro!, incluir el comando FETCH dentro
de un buclepermite leer todos los resultados de un cursor. Cuando el cursor llegue al final
de los resultados de la consulta, entonces el bucle termina. Pero terminar un bucle de este
tipo
necesita
una
condicin
de
parada
especial
en
MySQL.
Existen manejadores de errores en MySQL para esta tarea, aunque por el momento no los
hemos
estudiado
es
necesario
saber
lo
siguiente:
Cuando usamos FETCH en el cursor, pero ya no hay mas filas por retornar, MySQL arroja
un error llamado 02000 NO DATA FECH. As que lo que debemos hacer es crear
un manejador para indicar que cuando suceda ese error, el programa no termine, pero que
si termine el bucle. Veamos:
-- Declaracin de un manejador de error tipo NOT FOUND

DECLARE CONTINUE HANDLER FOR NOT FOUND SET @hecho = TRUE;

Aqu indicamos que si ocurre un error tipoNOT FOUND, entonces asignemos a la variable
@hecho el valor de TRUE. Con esa variable podremos manejar la terminacin de nuestro
bucle mas adelante.

4. CIERRE
Una vez ledo todos los resultados del cursor, procedemos a cerrar y limpiar espacios de
memoria con CLOSE.

CLOSE nombre_cursor;

Ejemplo de un Procedimiento con un Cursor

El siguiente es un ejemplo construido con fines educativos:
Cree un procedimiento en MySQL que imprima el cdigo y el total acumulado de ventas del
vendedor que mas factur. Tenga en cuenta que el procedimiento debe recibir la fecha inicial
y la fecha final, para estimar el lapso de tiempo en el que se calcular el acumulado.
El anterior requerimiento se crea a partir de una minibase de datos para un sistema de
facturacin que tiene el siguiente diagrama entidad-relacin:

Diagrama entidad relacin de un sistema de facturacin.

Diseador de phpMyAdmin

Antes de desarrollar el procedimiento, especificaremos el flujo en las entradas, procesos y

salidas
del
programa:
Entradas

Las variables fecha_inicio y fecha_final

Procesos

Consultar las facturas que liquid el vendedor en las fechas estipuladas como entrada.

Encontrar los detalles de cada factura asociada al vendedor.

Multiplicar el precio por la cantidad de cada detalle y guardarlo en la variable

acumulado_ventas.

Comparar el acumulado_ventas de cada vendedor mediante un bucle, para obtener el mejor

vendedor.

Salida

Mostrar en la pantalla el cdigo y la variable acumulado_ventas del mejor vendedor.

Una vez comprendido estos pasos, solo queda implementar el cdigo. Veamos:

DELIMITER //

CREATE PROCEDURE mejor_vendedor(fecha_inicio DATE, fecha_final DATE)

BEGIN

-- Declaracin de variables
DECLARE ID_VENDEDOR INT;
DECLARE ACUMULADO_VENTAS INT;
DECLARE TEMPV INT DEFAULT 0;
DECLARE TEMPID INT DEFAULT 0;

-- Definicin de la consulta
DECLARE mejor_vendedor_cursor CURSOR FOR
SELECT V.IDVENDEDOR,SUM(DF.UNIDADES*DF.PRECIO)
FROM VENDEDOR AS V INNER JOIN FACTURA AS F
ON V.IDVENDEDOR = F.IDVENDEDOR AND (F.FECHA BETWEEN fecha_inicio AND fecha_final)
INNER JOIN DETALLEFACTURA AS DF
ON F.IDFACTURA = DF.IDFACTURA
GROUP BY V.IDVENDEDOR;

-- Declaracin de un manejador de error tipo NOT FOUND

DECLARE CONTINUE HANDLER FOR NOT FOUND SET @hecho = TRUE;

-- Abrimos el cursor
OPEN mejor_vendedor_cursor;

-- Comenzamos nuestro bucle de lectura

loop1: LOOP

-- Obtenemos la primera fila en la variables correspondientes

FETCH mejor_vendedor_cursor INTO ID_VENDEDOR, ACUMULADO_VENTAS;

-- Si el cursor se qued sin elementos,

-- entonces nos salimos del bucle
IF @hecho THEN
LEAVE loop1;
END IF;

-- Guardamos el acumulado de ventas y el cdigo

-- si el vendedor actual tiene mejores resultados
IF ACUMULADO_VENTAS>=TEMPV THEN
SET TEMPV = ACUMULADO_VENTAS;
SET TEMPID = ID_VENDEDOR;
END IF;

END LOOP loop1;

-- Cerramos el cursor
CLOSE mejor_vendedor_cursor;

-- Imprimimos el cdigo y total acumulado de ventas del vendedor

SELECT TEMPID AS CODIGO_VENDEDOR, TEMPV AS TOTAL_VENTAS;

END//

DELIMITER ;

MySQL no permite que los cursores lean los resultados de una consulta desde el ultimo
elemento hasta el primero. Usa ORDER BY para organizar tu mismo la informacin.
MySQL no permite que saltemos a una fila en particular para ahorrarnos tiempo, debemos
recorrer obligatoriamente uno a uno los resultados.

Crear Funciones En MySQL

junio 20, 2014 James Revelo

Una funcin en MySQL es

una rutina creada para tomar unos parmetros, procesarlos y retornar en un salida.

Se diferencian de los procedimientos en las siguientes caractersticas:

Solamente pueden tener parmetros de entrada IN y no parmetros de salida OUT o INOUT

Deben retornar en un valor con algn tipo de dato definido

Pueden usarse en el contexto de una sentencia SQL

Solo retornan un valor individual, no un conjunto de registros.

Como creo una funcin?

Debes usar la sentencia CREATE FUNCTION. La sintaxis para crear una funcin es casi idntica
a la de crear un procedimiento, veamos:

CREATE FUNCTION nombre_funcin (parametro1,parametro2,...)

RETURNS tipoDato
[atributos de la rutina]
<bloque de instruccciones>

La nica diferencia entre la creacin de un procedimiento y una funcin es que la sintaxis de una
funcin contiene la palabra reservada RETURNS para indicar que tipo de dato se retornar.

Podemos ver un ejemplo?

Claro! Fjate como obtenemos el factorial de un numero x ingresado como parmetro:

DELIMITER //

CREATE FUNCTION factorial(x INT) RETURNS INT(11)

BEGIN
DECLARE factorial INT;

-- Guardamos el valor de x
SET factorial = x ;

-- Caso en que x sea menor o igual a 0

IF x <= 0 THEN
RETURN 1;
END IF;

-- Iteramos para obtener multiplicaciones

consecutivas

bucle: LOOP

-- Cada iteracion reducimos en 1 a x

SET x = x - 1 ;

-- Condicin de parada del bucle

IF x<1 THEN
LEAVE bucle;
END IF;

-- Factorial parcial
SET factorial = factorial * x ;

END LOOP bucle;

-- Retornamos en el factorial
RETURN factorial;

END//
DELIMITER ;

No confundas RETURNS con RETURN. La primera es para indicar el tipo de dato de retorno
de la funcin y la segunda es para retornar el valor en el cuerpo de la funcin.

Puedes mostrar como usar una funcin en un SELECT?

A continuacin crearemos un funcin que retorne en el nombre completo de la prioridad de un cliente,

introduciendo como parmetro el campo prioridad.

Creacin de la funcin:

DELIMITER //
CREATE FUNCTION EXT_PRIORIDAD (cliente_prioridad VARCHAR(5)) RETURNS VARCHAR(20)
BEGIN
CASE cliente_prioridad

WHEN 'A' THEN

RETURN 'Alto';
WHEN 'M' THEN
RETURN 'Medio';
WHEN 'B' THEN
RETURN 'Bajo';
ELSE
RETURN 'NN';
END CASE;
END//
DELIMITER ;

Con ella podremos consultar de la siguiente forma a los clientes de la base de datos:

SELECT NOMBRE, APELLIDO, EXT_PRIORIDAD(PRIORIDAD)

FROM CLIENTE;

De esta manera hemos usado nuestra funcin en un contexto de consulta. Tambin podemos
usar funciones en las sentencias DELETE y UPDATE, siempre y cuando el valor retornado
sea acorde con las necesidades.

Como actualizar una funcin?

Para actualizar una funcin usamos el comando ALTER FUNCTION. Con esta sentencia podemos
cambiar los atributos de la funcin, pero no podremos cambiar el cuerpo. Veamos la sintaxis:

ALTER FUNCTION nombre_funcion

[SQL SECURITY {DEFINER|INVOKER}]
[COMMENT descripcin ]

Si quisiramos aadir una descripcin a una funcin que calcula el promedio de huspedes diario con
respecto a una fecha llamada promedio_huespedes, hacemos lo siguiente:

ALTER FUNCTION promedio_huespedes

COMMENT 'Calculo del promedio diario de huspedes entre una fecha inicial y una fecha final';

Como borrar una funcin?

Usando el comando DROP FUNCTION. Simplemente especificamos el nombre de la funcin y esta
se borrar de la base de datos. Su sintaxis esta definida de la siguiente forma:

DROP FUNCTION nombre_funcion

Por ejemplo, para borrar una funcin que retorna en el ingreso neto con respecto a todos los tiquetes
areos comprados en una sucursal de una aerolnea, llamada ingreso_neto_sucursal:

DROP FUNCTION ingreso_neto_sucursal;

Crear Triggers en MySQL

junio 28, 2014 James Revelo

Un Trigger en MySQL es un programa almacenado(stored program), creado para

ejecutarse automaticamente cuando ocurra un evento en nuestra base de datos. Dichos
eventos son generados por los comandos INSERT, UPDATE y DELETE, los cuales hacen
parte del DML(Data Modeling Languaje) de SQL.
Esto significa que invocaremos nuestros Triggers para ejecutar un bloque de
instrucciones que proteja, restrinja o preparen la informacin de nuestras tablas, al momento
de manipular nuestra informacin. Para crear triggers en MySQL necesitas los privilegios
SUPER Y TRIGGER.

Crear un Trigger en MySQL

Aprovecha el Viernes negro en Udemy, cursos de programacin a $10

Usaremos una sintaxis similar a la creacin de Procedimientos y Funciones en MySQL.

Observemos:

CREATE [DEFINER={usuario|CURRENT_USER}]
TRIGGER nombre_del_trigger {BEFORE|AFTER} {UPDATE|INSERT|DELETE}
ON nombre_de_la_tabla
FOR EACH ROW
<bloque_de_instrucciones>

Obviamente la sentencia CREATE es conocidisima para crear nuevos objetos en la base de

datos. Eso ya lo tienes claro. Enfoquemos nuestra atencin en las otras partes de la definicin:

DEFINER={usuario|CURRENT_USER}
: Indica al gestor de bases de datos qu usuario tiene privilegios en su cuenta, para la
invocacin de los triggers cuando surjan los eventos DML. Por defecto este caracterstica
tiene el valor CURRENT_USER que hace referencia al usuario actual que esta creando el
Trigger.

nombre_del_trigger:
Indica el nombre de nuestro trigger. Existe una nomenclatura muy prctica para nombrar

un trigger, la cual nos da mejor legibilidad en la administracion de la base de datos. Primero

ponemos el nombre de tabla, luego especificamos con la inicial de la operacin DML y
seguido usamos la inicial del momento de ejecucin(AFTER o BEFORE). Por ejemplo:
-- BEFORE INSERT
clientes_BI_TRIGGER

BEFORE|AFTER: Especifica si el Trigger se ejecuta antes o despus del evento DML.

UPDATE|INSERT|DELETE:
Aqu eliges que sentencia usars para que se ejecute el Trigger.

ON nombre_de_la_tabla:
En esta seccin estableces el nombre de la tabla asociada.

FOR EACH ROW: Establece que el Trigger se ejecute por cada fila en la tabla asociada.

<bloque_de_instrucciones>: Define el bloque de sentencias que el Trigger ejecutar al ser

invocado.

Identificadores NEW y OLD en Triggers

Si queremos relacionar el trigger con columnas especificas de una tabla debemos usar los
identificadores
OLD
y
NEW.
OLD indica el valor antiguo de la columna y NEW el valor nuevo que pudiese tomar. Por
ejemplo:
OLD.idproducto

NEW.idproducto.
Si usamos la sentencia UPDATE podremos referirnos a un valor OLD y NEW, ya que
modificaremos registros existentes por nuevos valores. En cambio si usamos INSERT solo
usaremos NEW, ya que su naturaleza es nicamente de insertar nuevos valores a las
columnas. Y si usamos DELETE usaremos OLD debido a que borraremos valores que
existen con anterioridad.

Triggers BEFORE y AFTER

Estas clausulas indican si el Trigger se ejecuta antes o despus del evento DML. Hay ciertos
eventos
que
no
son
compatibles
con
estas
sentencias.
Por ejemplo, si tuvieras un Trigger AFTER que se ejecuta en una sentencia UPDATE, sera
ilgico editar valores nuevos NEW, sabiendo que el evento ya ocurri. Igual sucedera con
la sentencia INSERT, el Trigger tampoco podra referenciar valores NEW, ya que los valores
que en algn momento fueron NEW, han pasado a ser OLD.

Qu utilidades tienen los Triggers?

Con los Triggers podemos implementar varios casos de uso que mantengan la integridad de
la base de datos, como Validar informacin, Calcular atributos derivados, Seguimientos de
movimientos
en
la
base
de
datos,
etc.
Cuando surja una necesidad en donde veas que necesitas que se ejecute una accin
implcitamente(sin que la ejecutes manualmente) sobre los registros de una tabla, entonces
puedes considerar el uso de un Trigger.

Ejemplo de Trigger BEFORE en la sentencia UPDATE

A continuacin veremos un Trigger que valida la edad de un cliente antes de una
sentencia UPDATE. Si por casualidad el nuevo valor es negativo, entonces asignaremos NULL a
este atributo.
DELIMITER //
CREATE TRIGGER cliente_BU_Trigger
BEFORE UPDATE ON cliente FOR EACH ROW
BEGIN
-- La edad es negativa?
IF NEW.edad

Este Trigger se ejecuta antes de haber insertado el registro, lo que nos da el poder de verificar
primero si el nuevo valor de la edad esta en el rango apropiado, si no es as entonces
asignaremosNULL a ese campo. Grandes los Triggers!

Ejemplo de Trigger AFTER en la sentencia UPDATE

Supongamos que tenemos una Tienda de accesorios para Gamers. Para la actividad de
nuestro negocio hemos creado un sistema de facturacin muy sencillo, que registra las
ventas realizadas dentro de una factura que contiene el detalle de las compras.
Nuestra tienda tiene 4 vendedores de turno, los cuales se encargan de registrar las compras
de los clientes en el horario de funcionamiento.
Implementaremos un Trigger que guarde los cambios realizados
tabla DETALLE_FACTURA de la base de datos realizados por los vendedores.
Veamos la solucin:

DELIMITER //
CREATE TRIGGER detalle_factura_AU_Trigger
AFTER UPDATE ON detalle_factura FOR EACH ROW

sobre

la

BEGIN
INSERT INTO log_updates
(idusuario, descripcion)
VALUES (user( ),
CONCAT('Se modific el registro ','(',
OLD.iddetalle,',', OLD.idfactura,',',OLD.idproducto,',',
OLD.precio,',', OLD.unidades,') por ',
'(', NEW.iddetalle,',', NEW.idfactura,',',NEW.idproducto,',',
NEW.precio,',', NEW.unidades,')'));

END//

DELIMITER ;

Con este registro de logs podremos saber si algn vendedor ocioso esta alterando las
facturas, lo que lgicamente sera atentar contra las finanzas de nuestro negocio. Cada
registro nos informa el usuario que modific la tabla DETALLE_FACTURA y muestra una
descripcin sobre los cambios en cada columna.

Ejemplo de Trigger BEFORE en al sentencia INSERT

El siguiente ejemplo que te voy a mostrar me encanta!, ya que muestra como mantener la
integridad de una base de datos con respecto a una atributo derivado.
Supn que tienes una Tienda de electrodomsticos y que has implementado un sistema de
facturacin. En la base de datos que soporta la informacin de tu negocio, existen varias
tablas, pero nos vamos a centrar en la tabla PEDIDO y la tabla TOTAL_VENTAS.
TOTAL_VENTAS almacena las ventas totales que se le han hecho a cada cliente del negocio. Es
decir, si el cliente Armado Barreras en una ocasin compr 1000 dolares, luego compr
1250 dolares y hace poco ha vuelto a comprar 2000 dolares, entonces el total vendido a este
cliente es de 4250 dolares.
Pero supongamos que eliminamos el ultimo pedido hecho por este cliente, que pasara con
el registro en TOTAL_VENTAS ?,exacto!, quedara desactualizado.
Usaremos tres Triggers para solucionar esta situacin. Para que cada vez que usemos un
comando DML en la tabla PEDIDO, no tengamos que preocuparnos por actualizar
manualmente TOTAL_VENTAS.
Veamos:

-- TRIGGER PARA INSERT

DELIMITER //
CREATE TRIGGER PEDIDO_BI_TRIGGER
BEFORE INSERT ON PEDIDO
FOR EACH ROW
BEGIN
DECLARE cantidad_filas INT;
SELECT COUNT(*)
INTO cantidad_filas
FROM TOTAL_VENTAS
WHERE idcliente=NEW.idcliente;
IF cantidad_filas > 0 THEN
UPDATE TOTAL_VENTAS
SET total=total+NEW.total
WHERE idcliente=NEW.idcliente;
ELSE
INSERT INTO TOTAL_VENTAS
(idcliente,total)
VALUES(NEW.idcliente,NEW.total);
END IF;
END//

-- TRIGGER PARA UPDATE

CREATE TRIGGER PEDIDO_BU_TRIGGER
BEFORE UPDATE ON PEDIDO
FOR EACH ROW
BEGIN
UPDATE TOTAL_VENTAS
SET total=total+(NEW.total-OLD.total)
WHERE idcliente=NEW.idcliente;
END//

-- TRIGGER PARA DELETE

CREATE TRIGGER PEDIDO_BD_TRIGGER

BEFORE DELETE ON PEDIDO
FOR EACH ROW
BEGIN
UPDATE TOTAL_VENTAS
SET total=total-OLD.total
WHERE idcliente=OLD.idcliente;
END//

Con todos ellos mantendremos el total de ventas de cada cliente actualizado dependiendo del
evento realizado sobre un pedido.
Si insertamos un nuevo pedido generado por un cliente existente, entonces vamos
rpidamente a la tabla TOTAL_VENTAS y actualizamos el total comprado por ese cliente con
una sencilla suma acumulativa.
Ahora, si cambiamos el monto de un pedido, entonces vamos a TOTAL_VENTAS para descontar
el monto anterior y adicionar el nuevo monto.
Y si eliminamos un pedido de un cliente simplemente descontamos del total acumulado el
monto que con anterioridad habamos acumulado. Prctico cierto?

Ver la informacin de un Trigger en MySQL

Si!, usa el comando SHOW CREATE TRIGGER y rpidamente estars vindolas
especificaciones de tu Trigger creado. Observa el siguiente ejemplo:

SHOW CREATE TRIGGER futbolista_ai_trigger;

Tambin puedes ver los Triggers que hay en tu base de datos con:

SHOW TRIGGERS;

Eliminar un Trigger en MySQL

DROP, DROP y mas DROP. Como ya sabes usamos este comando para eliminar casi
cualquier cosa en nuestra base de datos:
DROP TRIGGER [IF EXISTS] nombre_trigger

Recuerda que podemos adicionar la condicion IF EXISTS para indica que si el Trigger ya
existe, entonces que lo borre.