Triggers Facturas

--1

CREATE OR REPLACE FUNCTION stock() RETURNS TRIGGER AS'


BEGIN

UPDATE articulos set stock=stock-new.cant where codart=new.codart;



RETURN NEW;
END;
'LANGUAGE 'plpgsql';

DROP TRIGGER tgrstock ON lineas_fac;
CREATE TRIGGER tgrstock
    AFTER INSERT ON lineas_fac
    FOR EACH ROW
    EXECUTE PROCEDURE stock();



--2

CREATE OR REPLACE FUNCTION stock_min() RETURNS TRIGGER AS'
DECLARE
    ext INTEGER;
    st_min INTEGER;
BEGIN

SELECT stock,stock_min into ext,st_min from articulos where codart=new.codart;

IF(ext<=st_min) THEN
    RAISE NOTICE ''El producto % esta al limite o por debajo del stock permitido (%) '',new.codart,ext;
END IF;




RETURN NEW;
END;
'LANGUAGE 'plpgsql';

DROP TRIGGER tgrstock_min ON articulos;
CREATE TRIGGER tgrstock_min
    AFTER UPDATE ON articulos
    FOR EACH ROW
    EXECUTE PROCEDURE stock_min();

--3

CREATE OR REPLACE FUNCTION venta() RETURNS TRIGGER AS'
DECLARE
    ext INTEGER;

BEGIN

SELECT stock into ext from articulos where codart=new.codart;

IF(new.cant>ext) THEN
    RAISE EXCEPTION ''El pedido supera el Stock disponible para el producto % venta rechazada'',new.codart;
END IF;




RETURN NEW;
END;
'LANGUAGE 'plpgsql';

DROP TRIGGER tgrventa ON lineas_fac;
CREATE TRIGGER tgrventa
    BEFORE INSERT ON lineas_fac
    FOR EACH ROW
    EXECUTE PROCEDURE venta();


--4

CREATE OR REPLACE FUNCTION dto() RETURNS TRIGGER AS'
DECLARE


BEGIN


IF(new.dto>=0 and new.dto<=50) THEN
    RETURN NEW;

ELSE
    RAISE EXCEPTION ''El DTO que se quiere aplicar a la venta no es valido para el sistema (%)'',new.dto;

END IF;






END;
'LANGUAGE 'plpgsql';

DROP TRIGGER tgrdto ON lineas_fac;
CREATE TRIGGER tgrdto
    BEFORE INSERT ON lineas_fac
    FOR EACH ROW
    EXECUTE PROCEDURE dto();


--5
CREATE OR REPLACE FUNCTION iva() RETURNS TRIGGER AS'
DECLARE


BEGIN


IF(new.iva>=0) THEN
    RETURN NEW;

ELSE
    RAISE EXCEPTION ''El IVA de una factura no puede ser negativo (%)'',new.codfac;

END IF;






END;
'LANGUAGE 'plpgsql';

DROP TRIGGER tgriva ON facturas;
CREATE TRIGGER tgriva
    BEFORE INSERT ON facturas
    FOR EACH ROW
    EXECUTE PROCEDURE iva();

--6


CREATE OR REPLACE FUNCTION fecha() RETURNS TRIGGER AS'


BEGIN


IF(new.fecha>current_date) THEN
    RAISE EXCEPTION''La fecha introducida % es superior a la actual'',new.fecha;
END IF;

RETURN NEW;










END;
'LANGUAGE 'plpgsql';

DROP TRIGGER tgrfecha ON facturas;
CREATE TRIGGER tgrfecha
    BEFORE INSERT ON facturas
    FOR EACH ROW
    EXECUTE PROCEDURE fecha();


--7. La quantitat d'articles demanats en una factura no pot ser 0 ni inferior

CREATE OR REPLACE FUNCTION arti() RETURNS TRIGGER AS'


BEGIN


IF(new.cant<=0) THEN
    RAISE EXCEPTION''El pedido de la linea % de la factura % tiene una cantidad de 0 o inferior, venta rechazada'',new.linea,new.codfac;
END IF;

RETURN NEW;










END;
'LANGUAGE 'plpgsql';

DROP TRIGGER tgrarti ON lineas_fac;
CREATE TRIGGER tgrarti
    BEFORE INSERT ON lineas_fac
    FOR EACH ROW
    EXECUTE PROCEDURE arti();






--8


CREATE OR REPLACE FUNCTION preu() RETURNS TRIGGER AS'


BEGIN


IF(new.precio<=0) THEN
    RAISE EXCEPTION''El pedido de la linea % de la factura % tiene precio 0 o inferior, venta rechazada'',new.linea,new.codfac;
END IF;

RETURN NEW;










END;
'LANGUAGE 'plpgsql';

DROP TRIGGER tgrpreu ON lineas_fac;
CREATE TRIGGER tgrpreu
    BEFORE INSERT ON lineas_fac
    FOR EACH ROW
    EXECUTE PROCEDURE preu();



--9


CREATE OR REPLACE FUNCTION porcen() RETURNS TRIGGER AS'
BEGIN
IF (new.cant>=10 and new.cant<20) THEN
    UPDATE lineas_fac set dto=5 where new.codfac=codfac and linea=new.linea;
    RETURN NEW;
ELSIF (new.cant>=20 and new.cant<30) THEN
    UPDATE lineas_fac set dto=10 where new.codfac=codfac and linea=new.linea;
    RETURN NEW;
ELSIF (new.cant>=30 and new.cant<40) THEN
    UPDATE lineas_fac set dto=15 where new.codfac=codfac and linea=new.linea;
    RETURN NEW;
ELSIF (new.cant>=40 and new.cant<50) THEN
    UPDATE lineas_fac set dto=20 where new.codfac=codfac and linea=new.linea;
    RETURN NEW;
ELSIF (new.cant>=50 and new.cant<60) THEN
    UPDATE lineas_fac set dto=25 where new.codfac=codfac and linea=new.linea;
    RETURN NEW;
ELSIF (new.cant>=60 and new.cant<70) THEN
    UPDATE lineas_fac set dto=30 where new.codfac=codfac and linea=new.linea;
    RETURN NEW;
ELSIF (new.cant>=70 and new.cant<80) THEN
    UPDATE lineas_fac set dto=35 where new.codfac=codfac and linea=new.linea;
    RETURN NEW;
ELSIF (new.cant>=80 and new.cant<90) THEN
    UPDATE lineas_fac set dto=40 where new.codfac=codfac and linea=new.linea;
    RETURN NEW;
ELSIF (new.cant>=90 and new.cant<100) THEN
    UPDATE lineas_fac set dto=45 where new.codfac=codfac and linea=new.linea;
    RETURN NEW;
ELSIF (new.cant>=100) THEN
    UPDATE lineas_fac set dto=50 where new.codfac=codfac and linea=new.linea;
    RETURN NEW;
END IF;

RETURN NEW;
END;
' LANGUAGE 'plpgsql';

DROP TRIGGER tgrporcen ON lineas_fac;
CREATE TRIGGER tgrporcen
    AFTER INSERT ON lineas_fac
    FOR EACH ROW
    EXECUTE PROCEDURE porcen();











--10


CREATE OR REPLACE FUNCTION media() RETURNS TRIGGER AS'
DECLARE
num_linea INTEGER;
sum_dto INTEGER;
DTO_MEDIA REAL;

BEGIN


select sum(dto),count(linea) INTO sum_dto,num_linea from lineas_fac where codfac=new.codfac;

DTO_MEDIA=sum_dto/num_linea;



UPDATE facturas set dto=DTO_MEDIA where codfac=new.codfac;
RETURN NEW;










END;
'LANGUAGE 'plpgsql';

DROP TRIGGER tgrmedia ON lineas_fac;
CREATE TRIGGER tgrmedia
    AFTER INSERT ON lineas_fac
    FOR EACH ROW
    EXECUTE PROCEDURE media();



--11


CREATE OR REPLACE FUNCTION minsto() RETURNS TRIGGER AS'
BEGIN
IF (new.stock_min<1) THEN
    RAISE EXCEPTION ''El stock minimo no puede ser inferior 1'';
END IF;
RETURN NEW;
END;
' LANGUAGE 'plpgsql';

DROP TRIGGER tgrminsto ON articulos;
CREATE TRIGGER tgrminsto
    BEFORE INSERT ON articulos
    FOR EACH ROW
    EXECUTE PROCEDURE minsto()