Cargando

Ir a contenido


 


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.


Escrito por el dic 30 2015 21:05 triggers mysql


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.

 

 

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`);

 

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.

 

 

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.

 

 

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();

 

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 idusuario INT,
    IN idtipoinmueble INT,
    IN precio DECIMAL,
    IN comisionDECIMAL,
)
BEGIN

INSERT INTO inmueble`( `idusuario`, `idtipoinmueble`, `precio`, `comision`) VALUES (idusuario,idtipoinmueble,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.

 

 

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.

 

 

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:

 

 

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)

 

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.

 

 

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 ha gustado y ayudado este Tutorial?
Puedes premiar al autor pulsando este botón para darle un punto positivo
  • -
  • 0
10
VOTA
5
100%
4
0%
3
0%
2
0%
1
0%

  Información

  •   Publicado dic 30 2015 21:05
  •   Actualizado ene 08 2016 13:08
  •   Visitas 11K
  •   Nivel
    Avanzado



Tutoriales Relacionados


2 Comentarios


Alex Pereiro
ene 08 2016 14:00

Estaba buscando algo de la sentencia DELIMITER, gracias Sergio.

Para los de BBDD nos ha venido de perlas.

No esperes más y entra en Solvetic
Deja tus comentarios y aprovecha las ventajas de la cuenta de usuario ¡Únete!
Demuestra que eres experto!
  ESCRIBIR TUTORIAL
Suscribirse