57 - Disparadores (triggers) función raise
Cuando se ejecuta el bloque de un triggers podemos detener la ejecución de los comandos SQL mediante la elevación de una excepción gracias a la función de SQLite llamada: raise.
Veremos con un ejemplo como definir una lógica de negocios en la aplicación para evitar que los datos queden con inconsistencias luego de ejecutar comandos SQL.
Problema resuelto
Administrar los datos de dos tablas llamadas: "libros" y "ventas". Cada vez que se produzca la venta de libros verificar y reducir el campo stock de la tabla "libros".
No permitir la venta de libros que no hay en el stock elevando una excepción mediante la función raise.
Borramos las tablas "libros" y "ventas" si existen:
drop table if exists ventas; drop table if exists libros;
Creamos las dos tablas con las siguientes estructuras:
create table libros( codigo integer primary key, titulo text, autor text, editorial text, precio real, stock integer ); create table ventas( numero integer primary key, codigolibro integer, precio real, cantidad integer );
Insertamos algunas filas de prueba en la tabla "libros":
insert into libros(titulo, autor, editorial, precio, stock)
values('Uno','Richard Bach','Planeta',15,100);
insert into libros(titulo, autor, editorial, precio, stock)
values('Ilusiones','Richard Bach','Planeta',18,50);
insert into libros(titulo, autor, editorial, precio, stock)
values('El aleph','Borges','Emece',25,200);
insert into libros(titulo, autor, editorial, precio, stock)
values('Aprenda PHP','Mario Molina','Emece',45,200);
Si existe procedemos a eliminar el trigger 'disparador_ventas_borrar':
drop trigger if exists disparador_ventas_borrar;
Creamos el disparador 'disparador_ventas_borrar' con la siguiente lógica:
create trigger disparador_ventas_borrar
before insert on ventas
begin
select
case when new.cantidad>libros.stock THEN
raise ( ABORT, 'No hay stock suficiente de ese libro')
end
from libros where new.codigolibro=libros.codigo;
update libros set stock=libros.stock-new.cantidad
where new.codigolibro=libros.codigo;
end;
Este trigger se ejecuta 'before insert', antes que se inserte un registro en la tabla 'on ventas'.
En el bloque del algoritmo mediante un select y mediante la cláusula 'case when' controlamos si la cantidad de libros a vender supera el stock actual:
select
case when new.cantidad>libros.stock THEN
Si sucede que la cantidad de libros a vender supera el stock actual detenemos la ejecución de comandos SQL mediante la llamada a la función raise:
raise ( ABORT, 'No hay stock de ese libro')
En el caso que la cantidad de libros a vender no supera el stock actual de dicho libro se ejecuta un 'update' en la tabla libros, reduciendo el stock actual de dicho libro:
update libros set stock=libros.stock-new.cantidad
where new.codigolibro=libros.codigo;
Efectuamos la venta de un libro:
insert into ventas(codigolibro, precio, cantidad) values(1, 15, 1);
Como podemos ver se disminuye en 1 el stock actual de dicho libro:
El stock actual del libro con código 1 es de 99. Probemos de intentar vender 100 libros:
insert into ventas(codigolibro, precio, cantidad) values(1,15,100);
Mediante la ejecución de la función raise evitamos que se actualice la tabla "libros" y que se produzca el insert en la tabla "ventas".
Si queremos ver todos los triggers, vistas y tablas creados en nuestra base de datos utilizando el programa "SQLite Browser" debemos seleccionar la pestaña "Estructura de la base de datos":
Problema propuesto
Una empresa almacena los datos de sus empleados en una tabla denominada "empleados" y en otra tabla llamada "secciones", el código de la sección y el sueldo máximo de cada una de ellas.
-
Borrar la tablas "empleados" y "secciones" si existen.
-
Crear las tablas con las siguientes estructuras:
create table secciones( codigo integer primary key, nombre text, sueldomaximo real ); create table empleados( documento text primary key, nombre text, domicilio text, codigoseccion integer, sueldo real );
-
Insertamos tres filas en la tabla "secciones":
insert into secciones (nombre, sueldomaximo) values ('Administracion',1500); insert into secciones (nombre, sueldomaximo) values ('Sistemas',2000); insert into secciones (nombre, sueldomaximo) values('Secretaria',1000); -
Cree un disparador que no permita insertar un empleado si se le asigna un sueldo mayor a la sección donde ingresa. Borrar el disparador si ya existía anteriormente.
-
Efectúe un insert en la tabla "empleados". Ingrese un empleado en la sección de 'Sistemas' con un sueldo que no supere 2000.
-
Imprimir la tabla "empleados".
-
Intente nuevamente insertar una fila en la tabla "empleados" ingresando un empleado en la sección de 'Sistemas' con un sueldo que supere los 2000.
-
Compruebe que la inserción no se efectuó.
drop table if exists empleados;
drop table if exists secciones;
create table secciones(
codigo integer primary key,
nombre text,
sueldomaximo real
);
create table empleados(
documento text primary key,
nombre text,
domicilio text,
codigoseccion integer,
sueldo real
);
insert into secciones (nombre, sueldomaximo) values ('Administracion',1500);
insert into secciones (nombre, sueldomaximo) values ('Sistemas',2000);
insert into secciones (nombre, sueldomaximo) values('Secretaria',1000);
drop trigger if exists disparador_sueldo_seccion;
create trigger disparador_sueldo_seccion
before insert on empleados
begin
select
case when new.sueldo>secciones.sueldomaximo THEN
raise ( ABORT, 'El sueldo supera al máxima permitido a dicha sección.')
end
from secciones where new.codigoseccion=secciones.codigo;
end;
insert into empleados(documento,nombre,domicilio,codigoseccion,sueldo)
values('11111111','Ana Acosta','Avellaneda 344',2,2000);
select * from empleados;
insert into empleados(documento,nombre,domicilio,codigoseccion,sueldo)
values('22222222','Bernardo Bustos','Lima 3445',2,7000);
Ejecución de ejercicios online
Puede ejecutar comandos de SQLite directamente en el sitio sin tener que instalar nada en su computadora.