CREATE DEFINER=`facturas`@`%` FUNCTION `fnc_totalfactura`(
`pFactura` INT
)
RETURNS decimal(12,2)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE mitotal DECIMAL(12,2);
set mitotal := (SELECT sum(lf.cantidad * lf.precio) tt
FROM lin_facturas lf
WHERE lf.fk_factura = pFactura);
if mitotal IS NULL then
set mitotal := 0;
END if;
RETURN mitotal;
END
CREATE DEFINER=`facturas`@`%` PROCEDURE `prc_actualizafactura`(
IN `pFactura` INT
)
LANGUAGE SQL
DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT ''
BEGIN
DECLARE miTotal DECIMAL (12,2);
SET miTotal := fnc_totalfactura(pFactura);
UPDATE facturas f SET f.total=miTotal
WHERE f.id = pFactura;
END
CREATE DEFINER=`facturas`@`%` TRIGGER `facturas_before_delete` BEFORE DELETE ON `facturas` FOR EACH ROW BEGIN
DELETE FROM lin_facturas
WHERE fk_factura = OLD.id;
END
CREATE DEFINER=`facturas`@`%` TRIGGER `facturas_before_insert` BEFORE INSERT ON `facturas` FOR EACH ROW BEGIN
if NEW.total !=0 then
SET NEW.total:=0;
END if;
END
CREATE DEFINER=`facturas`@`%` TRIGGER `facturas_before_update` BEFORE UPDATE ON `facturas` FOR EACH ROW BEGIN
if NEW.total != OLD.total then
set new.total := fnc_totalfactura(NEW.id);
END if;
END
CREATE DEFINER=`facturas`@`%` TRIGGER `lin_facturas_after_delete` AFTER DELETE ON `lin_facturas` FOR EACH ROW BEGIN
UPDATE facturas
SET total := fnc_totalfactura(old.fk_factura)
WHERE id = old.fk_factura;
END
CREATE DEFINER=`facturas`@`%` TRIGGER `lin_facturas_after_insert` AFTER INSERT ON `lin_facturas` FOR EACH ROW BEGIN
UPDATE facturas
SET total := fnc_totalfactura(NEW.fk_factura)
WHERE id = NEW.fk_factura;
END
CREATE DEFINER=`facturas`@`%` TRIGGER `lin_facturas_after_update` AFTER UPDATE ON `lin_facturas` FOR EACH ROW BEGIN
DECLARE i INT;
if NEW.fk_factura = OLD.fk_factura AND
(NEW.precio != OLD.precio OR
NEW.cantidad != OLD.cantidad) then
call prc_actualizafactura(NEW.fk_factura);
END if;
if NEW.fk_factura <> OLD.fk_factura then
call prc_actualizafactura(NEW.fk_factura);
call prc_actualizafactura(OLD.fk_factura);
END if;
END