Programa con Paula: TRIGGER

Puedes acceder a la teoría necesaria para hacer los ejercicios, a los ejercicios y las bases de datos necesarias aquí, aquí y aquí.

Pregunta 1

/*mi versión*/
delimiter $$
drop procedure if exists pregunta1 $$
create procedure pregunta1(e_cod_articulo char(4), e_cantidad int, out s_error_num int, out s_error_text varchar(200))
begin
/* declaracion de variables*/
declare v_articulo int;
declare v_existencias int;
/*bloqueación de la fila implicada*/
declare articulos_cursor cursor for
select existencias
from articulos
where cod_articulo=e_cod_articulo
for update;
/* control de errores 1 */
declare exit handler for sqlexception
begin
set s_error_num=0;
set s_error_text='ocurrio un error';
rollback;
end;
/* valor de las variables */
select count(*)
into v_articulo
from articulos
where cod_articulo=e_cod_articulo;
/*control de errores 2*/
if v_articulo <> 1 then
set s_error_num=-1;
set s_error_text='El articulo no existe';
else
/*transacción*/
start transaction;
open articulos_cursor;
fetch articulos_cursor into v_existencias;
if (v_existencias-e_cantidad)<0 then
set s_error_num=-2;
set s_error_text='La cantidad solicitada es mayor que las existencias';
else
set s_error_num=1;
update articulos set existencias=v_existencias-e_cantidad where cod_articulo=e_cod_articulo;
if s_error_num=1 then
set s_error_text='Actualización realiza con exito';
commit;
end if;
close articulos_cursor;
end if;
end if;
end $$
delimiter ;

/* respuesta profesor */
delimiter $$
drop procedure if exists pregunta1 $$
create procedure pregunta1(p_arti varchar(4), p_cantidad int, out resultado int)
begin
declare v_existencias int;
declare v_cod_art varchar(4);
declare exit handler for sqlexception
begin
rollback;
set resultado=0;
end;
select cod_articulo, existencias
into v_cod_art, v_existencias
from articulos
where cod_articulo=p_arti
for update;
if v_cod_art is null then
set resultado=-1;
else
start transaction;
if p_cantidad > v_existencias then
set resultado=-2;
rollback;
else
update articulos set existencias=v_existencias-p_cantidad
where cod_articulo=p_arti;
commit;
set resultado=1;
end if;
end if;
end $$
delimiter ;

Comprobaciones:
(antes)

---

(después)

Pregunta 2

/*mi versión*/
delimiter $$
drop trigger if exists pregunta2 $$
create trigger pregunta2
before delete on pedidos
for each row
begin
/* variables*/
declare v_num_pedido int;
declare v_enviado char;
declare v_precio_unidad decimal(8,2);
declare v_unidades int;
declare v_cod_articulo char(4);
declare v_ultima_fila int default 0;
/* cursor */
declare detalles_cursor cursor for
select unidades, cod_articulo
from detalle_pedidos
where cod_pedido=v_num_pedido
for update;
/* control de cursor */
declare continue handler for not found
set v_ultima_fila=1;
/* abre cursor */
select num_pedido, enviado
into v_num_pedido, v_enviado
from pedidos
where num_pedido=old.num_pedido;
open detalles_cursor;
detalles_c : loop
set v_ultima_fila=0;
fetch detalles_cursor into v_unidades, v_cod_articulo;
if v_ultima_fila=1 or v_enviado='S' then
leave detalles_c;
end if;
update articulos set existencias=existencias+v_unidades where cod_articulo=v_cod_articulo;
end loop detalles_c;
close detalles_cursor;
delete from detalle_pedidos where cod_pedido=old.num_pedido;
end $$

delimiter ;

/* respuesta profesor */
delimiter $$
drop trigger if exists pregunta2 $$
create trigger pregunta2
before delete on pedidos
for each row
begin
declare v_ultima_fila integer default 0;
declare v_cod_pedido integer (3);
declare v_cod_articulo char(4);
declare v_unidades integer(3);
declare pedidos_cursor cursor for
select cod_pedido, cod_articulo, unidades
from detalle_pedidos
where cod_pedido=old.num_pedido
for update;
declare continue handler for not found
set v_ultima_fila=1;
if old.enviado='N' then
open pedidos_cursor;
pedidos_bucle : loop
fetch pedidos_cursor into v_cod_pedido, v_cod_articulo, v_unidades;
if v_ultima_fila=1 then
leave pedidos_bucle;
end if;
update articulos set existencias=existencias+v_unidades
where cod_articulo=v_cod_articulo;
end loop pedidos_bucle;
delete from detalle_pedidos where cod_pedido=old.num_pedido;
close pedidos_cursor;
end if;
end $$

delimiter ;

Comprobaciones:
(antes)

---

(después)

Pregunta 3

/*mi versión con ayuda de compañeros*/
delimiter $$
drop function if exists pregunta3 $$
create function pregunta3(e_num_pedido int)
returns decimal(8,2)
begin
/*variables*/
declare v_pedido int;
declare v_cod_articulo char(4);
declare v_precio_unidad decimal(8,2);
declare v_unidades int;
declare v_descuento int;
declare v_ultima_fila int default 0;
declare total decimal(8,2);
declare totalfinal decimal(8,2) default 0;

/*cursor*/
declare articulo_cursor cursor for
select cod_articulo
from detalle_pedidos
where cod_pedido = e_num_pedido;

/*manejador errores*/
declare continue handler for not found
set v_ultima_fila = 1;

/*pedido*/
select count(*)
into v_pedido
from pedidos
where num_pedido = e_num_pedido;

if v_pedido != 1 then
set totalfinal = -1;

else

open articulo_cursor;

articulo_c : loop
fetch articulo_cursor into v_cod_articulo;
if v_ultima_fila = 1 then
leave articulo_c;
end if;

select precio_unidad
into v_precio_unidad
from articulos
where cod_articulo = v_cod_articulo;

select unidades
into v_unidades
from detalle_pedidos
where cod_articulo = v_cod_articulo
and cod_pedido = e_num_pedido;

set total = v_precio_unidad*v_unidades;
set totalfinal=totalfinal+total;
end loop articulo_c;
close articulo_cursor;

select descuento
into v_descuento
from pedidos
where num_pedido = e_num_pedido;

set totalfinal = totalfinal - ((totalfinal*v_descuento)/100);

end if;

return totalfinal;

end $$

delimiter ;

/* respuesta profesor */
delimiter $$
drop function if exists pregunta3$$
create function pregunta3 (v_ped int)
returns double
begin
declare existe int;
declare total_sin double;
declare v_descuento int;
select count(*)
into existe
from pedidos
where num_pedido=v_ped;
if existe =0 then
returns(-1);
else
set total_sin=(select sum(d.unidades*a.precio
from articulos a, detalle_pedidos declare
where a.cod_articulo=d.cod_articulo
and d.cod_pedido=v_ped);
set v_descuento=(select descuento
from pedidos
where cod_pedido=v_ped);
returns(total_sin*(1-(v_descuento/100)));
end if;
end $$

delimiter ;

Comprobaciones: