58 - Disparadores (triggers) delete

Hemos visto en conceptos anteriores la creación de disparadores cuando se ejecuta el comando insert en una tabla. Ahora veremos con un ejemplo como asociar un trigger a una tabla para cuando se ejecuta el comando delete.

Problema resuelto

Una librería almacena los datos de sus libros en una tabla denominada "libros" y en otra tabla llamada "ventas", las ventas de los mismos.
Eliminamos las tablas 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 algunos registros 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);

Del concepto anterior creamos nuevamente el disparador cuando se produce una venta para disminuir el stock de libros:

 drop trigger if exists disparador_ventas_borrar;
 
 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;

Creamos un nuevo disparador para actualizar el campo "stock" de la tabla "libros" cuando se elimina un registro de la tabla "ventas" (por ejemplo, si el comprador devuelve los libros comprados):

 drop trigger if exists disparador_devolucion_libro; 
 
 create trigger disparador_devolucion_libro
   before delete on ventas
 begin
   update libros set stock=libros.stock+old.cantidad
     where old.codigolibro=libros.codigo; 
 end;

Podemos comprobar que este disparados se ejecuta para el comando "delete" de la tabla "ventas":

   before delete on ventas

Efectuemos primero una venta:

 insert into ventas(codigolibro, precio, cantidad) values(1, 15, 1);

Podemos comprobar que disminuye el stock en 1:

SQLite Browser triggers

Ahora eliminaremos la venta mediante la ejecución del comando 'delete' en la tabla "ventas":

delete from ventas where numero=1;

Si ahora vemos el contenido de la tabla "libros" podremos comprobar que se ha incrementado en 1 el stock:

SQLite Browser triggers

Problema propuesto

Un comercio que vende artículos de informática almacena los datos de sus artículos en una tabla denominada "articulos".

  1. Borrar la tabla "articulos" si existe.

  2. Crear la tabla "articulos" con la siguiente estructura:

     create table articulos(
  codigo integer primary key,
  tipo text,
  descripcion text,
  precio real,
  stock integer
 );

  3. Insertar algunas filas en la tabla:

     insert into articulos(tipo,descripcion,precio,stock)
  values ('impresora','Epson Stylus C45',400,100);
 insert into articulos(tipo,descripcion,precio,stock)
  values ('impresora','Epson Stylus C85',500,200);
 insert into articulos(tipo,descripcion,precio,stock)
  values ('impresora','Epson Stylus Color 600',400,0);
 insert into articulos(tipo,descripcion,precio,stock)
  values ('monitor','Samsung 23',900,0);

  4. Cree un disparador para controlar que no se elimine un artículo si hay stock. El disparador se activará cada vez que se ejecuta un "delete" sobre "articulos", controlando el stock, si se está eliminando un artículo cuyo stock sea mayor a 0, el disparador debe retornar un mensaje de error y evitar que se borre.

  5. Solicite la eliminación de un articulo que no tenga stock. Se activa el disparador y permite la transacción.

  6. Intente eliminar un artículo para el cual haya stock. El trigger se dispara y debe evitar el borrado. Puede verificar que el artículo no fue eliminado consultando la tabla "articulos".

Solución 
drop table if exists articulos;

 create table articulos(
  codigo integer primary key,
  tipo text,
  descripcion text,
  precio real,
  stock integer
 );
 
 insert into articulos(tipo,descripcion,precio,stock)
  values ('impresora','Epson Stylus C45',400,100);
 insert into articulos(tipo,descripcion,precio,stock)
  values ('impresora','Epson Stylus C85',500,200);
 insert into articulos(tipo,descripcion,precio,stock)
  values ('impresora','Epson Stylus Color 600',400,0);
 insert into articulos(tipo,descripcion,precio,stock)
  values ('monitor','Samsung 23',900,0);

 drop trigger if exists disparador_articulo_borrar;
 
 create trigger disparador_articulo_borrar
   before delete on articulos
 begin
   select
     case when old.stock>0 THEN
       raise ( ABORT, 'No se puede borrar un articulo que hay stock')
     end
     from articulos where old.codigo=articulos.codigo; 
 end;    
 
 delete from articulos where codigo=4;
 
 select * from articulos;
 
 delete from articulos where codigo=1;

Ejecución de ejercicios online

Puede ejecutar comandos de SQLite directamente en el sitio sin tener que instalar nada en su computadora.

Resultado.....
Retornar