Cargando



Procedimientos almacenados y Triggers en MySQL

Los procedimientos almacenados y los disparadores o triggers son pequeños programas desarrollados en código SQL. En este tutorial añadiremos funciones y trigger o disparadores con ejemplos prácticos.


dic 30 2015 22:05
Avanzado
ene 08 2016 14:08

Unas herramientas que brinda el motor de base de datos MySQL son los procedimientos almacenados, funciones y trigger, que se utilizan para realizar transacciones u operaciones como insertar o modificar registros.

 

Los procedimientos almacenados son pequeños programas desarrollados en código SQL. Un procedimiento almacenado es un conjunto de comandos SQL que se almacenan junto con la base de datos.

 

mysql-logo.jpg

 

La ventaja de un procedimiento almacenado es que podemos crearlo en cualquier editor de texto e incluso en el servidor, es ejecutado por el motor de bases de datos y no es accesible a los usuarios sino solo al administrador.

 

Un procedimiento almacenado envía sus resultados a una aplicación para que esta los muestre en pantalla evitando sobrecargar el servidor, en el tutorial:

 

Había explicado como crearlos, aquí añadiremos funciones y trigger o disparadores. Veremos un ejemplo sobre una base de datos de una inmobiliaria que denominaremos alquilolugar y luego crearemos las tablas.

-- Estructura de tabla para la tabla `inmuebles`

CREATE TABLE IF NOT EXISTS `inmuebles` (
  `id` int(11) NOT NULL,
  `idusuario` int(11) DEFAULT NULL,
  `idtipoimueble` int(6) DEFAULT '0',
  `precio` decimal(10,2) DEFAULT '0.00',
  `comision` decimal(10,0) NOT NULL,
  `descripcion` text,
  `fechaAlta` date DEFAULT '0000-00-00',
  `idprovincia` int(10) DEFAULT NULL,
  `idlocalidad` int(10) DEFAULT NULL,
  `direccion` varchar(150) DEFAULT NULL,
  `pisoydepto` varchar(100) DEFAULT NULL,
  `entre_calles` text,
  `idoperacion` int(100) DEFAULT NULL,
  `destacado` char(3) DEFAULT 'no',
  `imagen1` varchar(255) DEFAULT NULL,
  `imagen2` varchar(255) DEFAULT NULL,
  `imagen3` varchar(255) DEFAULT NULL,
  `imagen4` varchar(255) DEFAULT NULL,
  `antiguedad` varchar(100) DEFAULT NULL,
  `mt2cubiertos` int(11) DEFAULT NULL,
  `superficie_lote` int(11) DEFAULT NULL,
  `activado` enum('si','no') NOT NULL DEFAULT 'si'
) ENGINE=MyISAM AUTO_INCREMENT=196 DEFAULT CHARSET=latin1;
-- Indices de la tabla `inmuebles`
ALTER TABLE `inmuebles`
  ADD PRIMARY KEY (`id`);

panta01.jpg

 

Vamos a desarrollar a continuación un procedimiento almacenado para cada transacción consultar, insertar, modificar y eliminar un registro.

 

Podemos utilizar Phpmyadmin o un gestor como Heidisql que es gratuito y funciona en Windows o Linux con Wine.

 

Creamos un procedimiento almacenado para consultar la tabla inmuebles:

DELIMITER //
CREATE PROCEDURE pa_listainmuebles()
BEGIN

SELECT * FROM inmuebles;

END//

DELIMITER ;
MYSQL entiende que una sentencia finaliza con un punto y coma. La sentencia DELIMITER cambia el carácter de finalización por cualquier otro carácter, por convención se utiliza // para indicar el fin del procedimiento almacenado para que MySQL no finalice el procedimiento almacenado al encontrar el primer punto y coma.

 

 

panta02.jpg

 

Podemos ir a la pestaña Rutinas para ver cada transacción que hayamos creado y desde allí podremos modificar, ejecutar, exportar o eliminar el código.

 

panta03.jpg

 

Para ejecutar un procedimiento almacenando usamos el comando CALL desde la pestaña SQL o también desde un lenguaje de programación como .NET o Java. A continuación invocamos el procedimiento almacenado creado con el comando.

CALL pa_listainmuebles();

panta04.jpg

 

A continuación crearemos un procedimiento almacenado para insertar un inmueble, para esto necesitaremos parámetros tipo IN es decir que al procedimiento almacenado le asignaremos datos y variables de entrada para hacer alguna transacción, en este caso guardarlos en la base de datos.

DELIMITER //

CREATE PROCEDURE pa_nuevoinmueble (
    IN id INT,
    IN idusuario INT,
    IN precio DECIMAL,
    IN comision DECIMAL
)
BEGIN

INSERT INTO inmueble`( `id`, `idusuario`, `precio`, `comision`) VALUES (id,idusuario,precio,comision)

END//

DELIMITER ;

 

Luego podemos ejecutar el procedimiento almacenado invocando y asignado los parámetros.

CALL `pa_nuevoinmueble`('12','15','10.00','0,05')
También podemos ingresar datos ejecutando la rutina desde Phpmyadmin.

 

 

 

A continuación crearemos el procedimiento almacenado para editar un inmueble desde el editor de Phpmyadmin, en este caso solo modificaremos el precio.

 

panta07.jpg

 

Podemos crear roles desde el campo Definidor donde podemos asignar un usuario definido en el servidor Mysql, en este caso el usuario root del host localhost, para que pueda acceder a al procedimiento almacenado.
Si queremos hacerlo desde código SQL, deberemos ejecutar los siguiente comandos:

CREATE DEFINER=`root`@`localhost`
PROCEDURE `pa_editarinmueble`(IN `precionuevo` DECIMAL(10,2), IN `idinmueble` INT(11))
BEGIN
UPDATE inmuebles SET precio=precionuevo WHERE id=idinmueble;
END
Lo ejecutas y listo.

 

Utilización de Trigger o Disparadores en Mysql

Un Trigger o Disparador en MySQL es un conjunto de sentencias SQL dependerán de un procedimiento almacenado y se utilizan para ejecutarse automáticamente cuando ocurra un evento determinado en nuestra base de datos. Estos eventos son disparados por transacciones o sentencias como INSERT, UPDATE y DELETE.

 

Un ejemplo es cuando se guarda un cambio en un registro, automáticamente hacemos un backup o grabamos un archivo de auditoria para saber que dato se cambio, cuando y quien lo cambio. Se pueden utilizar para cualquier manipulación que afecten a los datos, para respaldar o generar nueva información.

 

Crearemos a continuación la tabla de auditoria de inmuebles:

CREATE TABLE `auditoria` (
`usuario` VARCHAR(200) NULL DEFAULT NULL,
`descripcion` TEXT NULL,
`fecha` DATETIME NULL DEFAULT NULL
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB
Crearemos un disparador que guarde en auditoria un mensaje si alguien cambia el precio de un inmueble.
CREATE DEFINER=`root`@`localhost` TRIGGER `inmuebles_after_update` AFTER UPDATE ON `inmuebles` FOR EACH ROW INSERT INTO auditoria
(usuario, descripcion,fecha)
VALUES (user( ),
CONCAT('Se modifico el precio del inmueble  ',NEW.id,' (',
OLD.precio,') por (', NEW.precio,')'),NOW())
Este disparador se ejecuta automáticamente luego de producirse una actualización de precio, podemos incluir mas campos si lo deseamos, con OLD especificamos el campo con el valor antes de la modificación y con NEW especificamos el nuevo valor ingresado, con NOW() especificamos la fecha y hora actual.

 

 

panta08.jpg

 

Creamos un disparador que tendrá como evento After Update on inmuebles, es decir después de que ocurra una actualización en la tabla inmuebles, en ese caso añadiremos el usuario que realizo la modificación, el nuevo precio y el precio anterior.

 

Realizo una actualización en un inmueble:

CALL `pa_editarinmueble`('80000', '170')
Luego vamos a la tabla auditoría y podremos ver el cambio:

 

 

panta09.jpg

 

También podemos ver los resultados en un reporte en vista de impresión desde Phpmyadmin. Podemos ver como se han guardado los datos que identifican al inmueble, el cambio realizado y el usuario que lo realizo, además tenemos la fecha y la hora en que se realizó el cambio.

 

A continuación veremos otra posible aplicación si un inmueble se alquila entonces que cambie automáticamente de estado a no activa o la pondremos como no disponible.

 

Para ello deberemos tener una sencilla tabla donde almacenar que inmueble está alquilado, para un ejemplo práctico no tomaremos mucha rigurosidad en los datos.

CREATE TABLE `alquileres` (
`id` INT(10) NOT NULL,
`idinmueble` INT(10) NOT NULL,
`idinquilino` INT(11) NOT NULL,
PRIMARY KEY (`id`)
)
COLLATE='latin1_swedish_ci'
ENGINE=InnoDB;
A continuación crearemos el procedimiento almacenado para insertar un nuevo registro en la tabla alquileres.
CREATE DEFINER=`root`@`localhost` 
PROCEDURE `pa_nuevoalquiler`(IN `idinmueble` INT, IN `idinquilino` INT)
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''

INSERT INTO `alquileres`(`idinmueble`, `idinquilino`) VALUES (idinmueble,idinquilino)

panta010.jpg

 

Y luego el trigger para modificar inmuebles activado:

CREATE DEFINER=`root`@`localhost`
TRIGGER `alquileres_after_insert` AFTER INSERT ON `alquileres` 
FOR EACH ROW UPDATE inmuebles 
SET activado='no' where id=NEW.idinmueble
Luego invocamos el procedimiento almacenado donde asignamos el id del inmueble y el id del cliente o inquilino que alquilo.
CALL pa_nuevoalquiler(170,11)
A continuación vamos a la tabla de inmuebles y deberemos ver que el campo activado cambio de estado SI esta activo a NO esta activo.

 

 

panta011.jpg

 

Hemos visto las ventaja de utilizar trigger con procedimiento almacenados en MySQL para:

  • Auditar y registrar eventos o actividades de cambio de datos en una tabla.
  • Cambiar el estado de un campo activando o negando permisos, y acciones en una tabla
  • Ademas permite preservar la consistencia de los datos ejecutando acciones según eventos que afecten a una o más tablas.
En un otro tutorial, continuaremos con programación de estructuras condicionales y estructuras repetitivas en procedimiento almacenados.


¿Te ayudó este Tutorial?


4 Comentarios


Alex Pereiro
ene 08 2016 15:00

Estaba buscando algo de la sentencia DELIMITER, gracias Sergio.

Para los de BBDD nos ha venido de perlas.


Andres San Juan
mar 09 2019 00:49

Hola que tal duna observación para la sentencia de proceso para insertar un nuevo inmueble no concuerdan los campos de la tabla con los registrados en el procedimiento al intentar hacer el procedimiento sale un error.

 

 

 

DELIMITER //
 
CREATE PROCEDURE pa_nuevoinmueble(
IN id INT,
IN idusuario INT,
IN precio DECIMAL,
IN comision DECIMAL
)
BEGIN
 
INSERT INTO inmuebles( `id`, `idusuario`, `precio`, `comision`) VALUES 
(id, idusuario ,precio, comision);
 
END//
 
DELIMITER ;
No esperes más y entra en Solvetic
Deja tus comentarios y aprovecha las ventajas de la cuenta de usuario ¡Únete!

X