Un poco Geek

Usando Disparadores en MySQL (básico)

February 2, 2008 7:01 pm soullost Base de Datos

Qué es un dispador? Los disparadores (también conocido como triggers) son conjunto de instrucciones (sql claro) que se ejecutan cuando se produce una de las siguientes acciones: INSERT, DELETE o UPDATE de ahí su utilidad. Para quién quiera saber más mirar:

http://dev.mysql.com/doc/refman/5.0/es/triggers.html
http://www.mysql-hispano.org/page.php?id=36&pag=1

Con ellos podemos ahorrar algunas líneas de programación si estamos usando MySQL y dejar el trabajo al propio manejador de Base de Datos. Veamos un ejemplo rápido y ustedes decidan si son útiles o no.

Para empezar necesitamos usar tablas tipo InnoDB, para saber si tenemos esa posibilidad podemos teclear desde la consola de MySQL:

mysql> SHOW VARIABLES LIKE '%innodb%';
+---------------------------------+---------------------------------+
| Variable_name                   | Value                           |
+---------------------------------+---------------------------------+
| have_innodb                     | YES                             |
| innodb_additional_mem_pool_size | 2097152                         |
| innodb_autoextend_increment     | 8                               |
| innodb_buffer_pool_awe_mem_mb   | 0                               |
| innodb_buffer_pool_size         | 16777216                        |
| innodb_checksums                | ON                              |
| innodb_commit_concurrency       | 0                               |
| innodb_concurrency_tickets      | 500                             |
| innodb_data_file_path           | ibdata1:10M:autoextend:max:128M |
| innodb_data_home_dir            |                                 |
| innodb_doublewrite              | ON                              |
| innodb_fast_shutdown            | 1                               |
| innodb_file_io_threads          | 4                               |
| innodb_file_per_table           | OFF                             |
| innodb_flush_log_at_trx_commit  | 1                               |
| innodb_flush_method             |                                 |
| innodb_force_recovery           | 0                               |
| innodb_lock_wait_timeout        | 50                              |
| innodb_locks_unsafe_for_binlog  | OFF                             |
| innodb_log_arch_dir             |                                 |
| innodb_log_archive              | OFF                             |
| innodb_log_buffer_size          | 8388608                         |
| innodb_log_file_size            | 5242880                         |
| innodb_log_files_in_group       | 2                               |
| innodb_log_group_home_dir       | ./                              |
| innodb_max_dirty_pages_pct      | 90                              |
| innodb_max_purge_lag            | 0                               |
| innodb_mirrored_log_groups      | 1                               |
| innodb_open_files               | 300                             |
| innodb_rollback_on_timeout      | OFF                             |
| innodb_support_xa               | ON                              |
| innodb_sync_spin_loops          | 20                              |
| innodb_table_locks              | ON                              |
| innodb_thread_concurrency       | 8                               |
| innodb_thread_sleep_delay       | 10000                           |
+---------------------------------+---------------------------------+
35 rows in set (0.00 sec)

Por defecto creo desde MySQL 5.0 las tablas se crean de tipo InnoDB.

El escenario es el siguiente: Diseñamos una base de datos para poder controlar la facturación de una tienda, entonces realizamos la base de datos de la siguiente forma:

create database  FACTURA;

USE FACTURA;

Create table VENTA (

	Folio Char(30) NOT NULL UNIQUE,

	Fecha Datetime NOT NULL,

Primary Key  (Folio)

); 

Create table PRODUCTO (

	Codigo Integer NOT NULL UNIQUE,

	Descripccion Char(30) NOT NULL,

	Precio Float NOT NULL,

	Existencia Integer Default 0 NOT NULL,

Primary Key  (Codigo)

);

Create table DETALLE_DE_VENTA (

	Folio Char(30) NOT NULL,

	Codigo Integer NOT NULL,

	Cantidad Integer NOT NULL,

	Precio Float NOT NULL,

Primary Key  (Folio,Codigo)

);

Alter table DETALLE_DE_VENTA add  foreign key(Folio) references VENTA (Folio);

Alter table DETALLE_DE_VENTA add  foreign key(Codigo) references PRODUCTO (Codigo);

Creación de la base de datos:
[code]
UnderHouse sql # mysql -p < db.sql
[/code]

Ingresamos algunos datos ficticios para el ejercicio:

insert into PRODUCTO (Codigo, Descripccion,Precio) values (01,'MEMORIA RAM',500);

insert into PRODUCTO (Codigo, Descripccion,Precio) values (02,'MEMORIA USB',500);

insert into PRODUCTO (Codigo, Descripccion,Precio) values (03,'GABINETE',80);

insert into PRODUCTO (Codigo, Descripccion,Precio) values (04,'TARJETA DE VIDEO',1000);

insert into VENTA VALUES('200716100001','2007-10-16');

insert into VENTA VALUES('200716100002','2007-10-16');

insert into VENTA VALUES('200716100003','2007-10-16');

update PRODUCTO set existencia='10' where codigo=01;

update PRODUCTO set existencia='20' where codigo=02;

update PRODUCTO set existencia='5' where codigo=03;

Lógicamente analizando detenidamente la base de datos podemos crear un trigger cuando se presenta la siguiente situación:

Cuando una venta se lleva a cabo es necesario restar la cantidad de producto que se vendió a la existencia que tenemos del mismo, en caso de no haber existencia suficiente del producto, no permitir que el registro de la nueva venta se lleve a cabo.

El trigger correspondiente que soluciona la situación anterior es:

USE FACTURA;

DELIMITER |

create trigger disminuir_existencia BEFORE INSERT ON DETALLE_DE_VENTA 

FOR EACH ROW

BEGIN

	DECLARE Existencia_Producto Integer;

	select PRODUCTO.Existencia into Existencia_Producto from PRODUCTO where PRODUCTO.Codigo = NEW.Codigo;

	IF Existencia_Producto >= NEW.Cantidad THEN

		update PRODUCTO set Existencia=Existencia-NEW.Cantidad where PRODUCTO.Codigo = NEW.Codigo;

	ELSE

		set NEW.Folio = NULL ;

	END IF; 

END|

DELIMITER ;

Pueden copiarlo a un archivo *.sql y pasarlo de la siguiente forma:
[code]
UnderHouse sql # mysql -p < disparador.sql
[/code]

Lo que hace es:

1) Extrae la existencia del producto que se trata de vender y se le asigna a la variable Existencia_Producto.

2) Verifica si la cantidad que se quiere vender es menor o igual a la existencia disponile (IF).

3) Si la venta es posible, se actualiza el valor de la existencia del producto que se quiere vender y el registro se lleva a cabo correctamente.

4) Si la venta no es posible, se crea un error a propósito para impedir que el registro se lleve a cabo correctamente (nota: en sqlserver es posible usar ROLLBACK pero con mysql no encontré otra forma de detener el insert, si alguien lo sabe y desea compartirlo ps con un comentario basta).

Cuestiones a tomar en cuenta para crear un trigger:

También hay limitaciones sobre lo que puede aparecer dentro de la sentencia que el disparador ejecutará al activarse:

*

El disparador no puede referirse a tablas directamente por su nombre, incluyendo la misma tabla a la que está asociado. Sin embargo, se pueden emplear las palabras clave OLD y NEW. OLD se refiere a un registro existente que va a borrarse o que va a actualizarse antes de que esto ocurra. NEW se refiere a un registro nuevo que se insertará o a un registro modificado luego de que ocurre la modificación.
*

El disparador no puede invocar procedimientos almacenados utilizando la sentencia CALL. (Esto significa, por ejemplo, que no se puede utilizar un procedimiento almacenado para eludir la prohibición de referirse a tablas por su nombre).
*

El disparador no puede utilizar sentencias que inicien o finalicen una transacción, tal como START TRANSACTION, COMMIT, o ROLLBACK.

Veamos si funcionan realmente:

/* 

Valores iniciales

*/

mysql> select * from DETALLE_DE_VENTA;
+--------------+--------+----------+--------+
| Folio        | Codigo | Cantidad | Precio |
+--------------+--------+----------+--------+
| 200716100001 |      1 |        5 |    600 |
| 200716100001 |      2 |       10 |    600 |
| 200716100001 |      3 |        1 |    600 |
+--------------+--------+----------+--------+
3 rows in set (0.00 sec)

mysql> select * from PRODUCTO;
+--------+------------------+--------+------------+
| Codigo | Descripccion     | Precio | Existencia |
+--------+------------------+--------+------------+
|      1 | MEMORIA RAM      |    500 |          1 |
|      2 | MEMORIA USB      |    500 |          5 |
|      3 | GABINETE         |     80 |          4 |
|      4 | TARJETA DE VIDEO |   1000 |          0 |
+--------+------------------+--------+------------+

/* 

Damos de alta una nueva venta
Código Producto: 2 (Memoria USB)
Cantidad:1

*/

mysql> insert into DETALLE_DE_VENTA values ('200716100002',2,1,600);
Query OK, 1 row affected (0.00 sec)

/*

Miramos si hay modificaciones en MEMORIA USB
que anteriormente tenía existencia de 5 y si se
ingreso correctamente el registro.

*/

mysql> select * from DETALLE_DE_VENTA;
+--------------+--------+----------+--------+
| Folio        | Codigo | Cantidad | Precio |
+--------------+--------+----------+--------+
| 200716100001 |      1 |        5 |    600 |
| 200716100001 |      2 |       10 |    600 |
| 200716100002 |      2 |        1 |    600 |
| 200716100001 |      3 |        1 |    600 |
+--------------+--------+----------+--------+
4 rows in set (0.00 sec)

mysql> select * from PRODUCTO;
+--------+------------------+--------+------------+
| Codigo | Descripccion     | Precio | Existencia |
+--------+------------------+--------+------------+
|      1 | MEMORIA RAM      |    500 |          1 |
|      2 | MEMORIA USB      |    500 |          4 |
|      3 | GABINETE         |     80 |          4 |
|      4 | TARJETA DE VIDEO |   1000 |          0 |
+--------+------------------+--------+------------+
4 rows in set (0.00 sec)

/* 

Tratamos de ingresar una venta que exceda la existencia del producto

*/

mysql> insert into DETALLE_DE_VENTA values ('200716100002',3,5,600);
ERROR 1048 (23000): Column 'Folio' cannot be null

/* 

Otro más
Código: 3 (GABINETE)
Cantidad: 4

*/

mysql> insert into DETALLE_DE_VENTA values ('200716100002',3,4,600);
Query OK, 1 row affected (0.00 sec)

mysql> select * from DETALLE_DE_VENTA;
+--------------+--------+----------+--------+
| Folio        | Codigo | Cantidad | Precio |
+--------------+--------+----------+--------+
| 200716100001 |      1 |        5 |    600 |
| 200716100001 |      2 |       10 |    600 |
| 200716100002 |      2 |        1 |    600 |
| 200716100001 |      3 |        1 |    600 |
| 200716100002 |      3 |        4 |    600 |
+--------------+--------+----------+--------+
5 rows in set (0.00 sec)

mysql> select * from PRODUCTO;
+--------+------------------+--------+------------+
| Codigo | Descripccion     | Precio | Existencia |
+--------+------------------+--------+------------+
|      1 | MEMORIA RAM      |    500 |          1 |
|      2 | MEMORIA USB      |    500 |          4 |
|      3 | GABINETE         |     80 |          0 |
|      4 | TARJETA DE VIDEO |   1000 |          0 |
+--------+------------------+--------+------------+
4 rows in set (0.00 sec)

Eso es todo , como ejercicio pueden agregar que si la venta no se ingresa correctamente en la tabla DETALLE_DE_VENTA proceda a eliminar el registro de dicha venta en la tabla VENTA.

PD. Sobre tablas relaciones, mirar: http://soullost.org/base-de-datos/base-de-datos-con-mysql/

Califica el tema:
Temas Relacionados:

GUIs para el manejo de MySQL en GNU/Linux

Preparando migración a Wordpress

Base de Datos con Mysql

Usando TOR en GNU/Linux

Y tú, ¿Eres mexicano?