Uso de Triggers en MySQL

En el día de hoy voy a comentar el uso de Triggers en MySQL.
En sistemas gestores de base de datos, como PostgreSQL u Oracle Database, las restricciones de tipo “check” se evalúan correctamente, cosa que no ocurre en MySQL, complicando algo la creación de tablas con sus correspondientes restricciones.

Ésta es la tabla con la que vamos a trabajar en los ejemplos:

create table Pagos
(
    Codigo                   varchar(8),
    Concepto                 varchar(30),
    Importe                  numeric(7,2),
    Fecha                    date,
    CodigoTipodePago         varchar(4),
    CodigoProyecto           varchar(4),
    NIFColaborador           varchar(9),
    constraint pk_Pago primary key(Numero),
    constraint fk_ProyectoTipoPago foreign key(CodigoTipodePago) references TiposDePago(Codigo),
    constraint fk_ProyectoPago foreign key(CodigoProyecto) references Colaboraciones(CodigoProyecto),
    constraint fk_ColaboradorPago foreign key(NIFColaborador) references Colaboraciones(NIFColaborador)
);
Antes de nada, introduciré los delimitadores en MySQL, es simplemente el conjunto de caracteres que define cuando hemos finalizado de introducir lo que deseamos. En la mayoría de los SGBD está definido por defecto el “;”, pero, como veremos más adelante, no podemos dejar dicho delimitador, ya que será un componente necesario en la elaboración de nuestros Triggers, para ello estableceremos el delimitador con $$ con el siguiente comando:
delimiter $$
A continuación, veremos algunos ejemplos, comparando cómo debemos usar Oracle Database o PostgreSQL con MySQL. Un caso sería, simplemente añadiendo la siguiente línea en la creación de la tabla (o posteriormente con un alter table add constraint), con lo que provocamos que sólo admitamos filas cuyo valor de importe sea menor a 25000:
constraint precioMenorA25000 check(Importe < 25000)

Válido tanto en PostgreSQL como en Oracle Database, para poder definirlo en MySQL debemos realizarlo con la la insercción de Triggers, éste en concreto sería tal que así:

create trigger costePagoOk
before insert on Pagos
for each row begin
   if new.Importe > 25000 then
      signal sqlstate '45000' set message_text = 'El importe debe ser menor a 25000';
   end if;
end;
Pero si lo analizamos un poco, vemos que ésto sólo serviría para las nuevas inserciones en la tabla Pagos, ya que el Trigger se ejecuta antes de insertar datos en la tabla (before insert on Pagos) para cada fila asociada (for each row), con lo que habría que crear otro trigger pero cambiando dicha línea:
create trigger costePagoOk
before update on Pagos
for each row begin
   if new.Importe > 25000 then 
      signal sqlstate '45000' set message_text = 'El importe debe ser menor a 25000';
   end if;
end;
También comentar, que hay que tener cuidado si lo comparamos con las restricciones de tipo “check”, ya que en ellas vamos a dictar lo que es válido, mientras que en el Trigger haremos que salte cuando la condición no se cumple y que, en principio, la señal que mandaremos será la “45000”, que indica que es una excepción no controlada definida por nosotros.
Por último, vamos a ver que se complica algo más, cuando en una misma tabla existen más de una restricción, en Oracle Database y PostgreSQL simplemente añadiríamos otra restricción “check” a continuación, del estilo:
constraint formatoCodigo check(Codigo like 'P%')

En la que sólo se admitirán filas cuyo código tenga un formato de “P” y lo que sea (hasta 8 valores en este caso). pues bien, MySQL no admite que existan dos Triggers que se ejecuten cuando ocurre la misma acción, es decir, no puede haber dos Triggers que se ejecuten cuando se realiza una inserción, por lo que habría que modificar el Trigger y quedaría de la siguiente forma:

create trigger costePagoOk
before insert on Pagos
for each row begin
   if new.Importe > 25000 then
      signal sqlstate '45000' set message_text = 'El importe debe ser menor a 25000';
   end if;
   if new.Codigo not like "P%" then
      signal sqlstate '45000' set message_text = 'El código debe tener un formato P%';
   end if;
 end;

Espero os resulte de ayuda.

David Tinoco Castillo.

Anuncios

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión /  Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión /  Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión /  Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión /  Cambiar )

Conectando a %s