Perl=>undef, 

perl=>'C:\Program Files\Perl\bin',

CREATE OR REPLACE FUNCTION _insert_reglement_exclusion()
  RETURNS TRIGGER AS
$BODY$
declare 
	montant integer := 0;
	montant_init integer := 0;
begin
	SELECT COALESCE(sum(montantreglement), 0) INTO montant FROM reglement a WHERE a.idfacture = new.idfacture;
	SELECT sum(prixunitaire*quantite) INTO montant_init FROM lignefacture WHERE idfacture = new.idfacture;
	IF montant + new.montantreglement > montant_init then
		RAISE 'le montant des règlements dépasse le total de la facture!';
	end IF;
	RETURN NEW;
end;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION _insert_reglement_exclusion()
  OWNER TO postgres;

CREATE OR REPLACE FUNCTION _update_reglement_exclusion()
  RETURNS TRIGGER AS
$BODY$
declare 
	montant integer := 0;
	montant_init integer := 0;
begin
	SELECT COALESCE(sum(montantreglement), 0) INTO montant FROM reglement a WHERE a.idfacture = new.idfacture AND a.idfacture = OLD.idfacture;
	SELECT sum(prixunitaire*quantite) INTO montant_init FROM lignefacture WHERE idfacture = new.idfacture AND idfacture = OLD.idfacture;
	IF montant -old.montantreglement+ new.montantreglement > montant_init then
		RAISE 'le montant des règlements dépasse le total de la facture!';
	end IF;
	RETURN NEW;
end;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION _update_reglement_exclusion()
  OWNER TO postgres;

create table controle(tr int, tf int)

-- Function: verifiereginsertion()

-- DROP FUNCTION verifiereginsertion();

CREATE OR REPLACE FUNCTION verifiereginsertion()
  RETURNS trigger AS
$BODY$
DECLARE
  totalfacture integer;
  totalreglement integer;
BEGIN
SELECT INTO totalfacture sum(lignefacture.quantite*lignefacture.prixunitaire) FROM lignefacture WHERE idfacture=NEW.idfacture;
SELECT INTO totalreglement coalesce(sum(montantreglement),0) FROM reglement WHERE idfacture=NEW.idfacture;
insert into controle(tr,tf) values(totalreglement, totalfacture);
IF totalreglement>totalfacture THEN
RAISE 'le montant des règlements depasse le total de la facture!';
  RETURN NULL;
END IF;
RETURN NEW;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION verifiereginsertion()
  OWNER TO postgres;

insert into vreglement(idfacture, datereglement, montantreglement) values(1,'12/03/2011',5000)

CREATE OR REPLACE FUNCTION verifiereginsertion()
  RETURNS trigger AS
$BODY$begin
IF ((select COALESCE(sum(lignefacture.quantite*lignefacture.prixunitaire),0)- COALESCE(sum(reglement.montantreglement),0)- sum(NEW.montantreglement) 
from lignefacture
left join reglement on lignefacture.idfacture=reglement.idfacture)<0) then
RAISE 'le montant des règlements depasse le total de la facture!';
else
RETURN NEW;
end if;
end;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

RETURN NEW

--table facture
facture
(
  idfacture serial NOT NULL,
  datefacture date NOT NULL DEFAULT ('now'::text)::date,
  typefacture smallint NOT NULL,
  CONSTRAINT facture_pkey PRIMARY KEY (idfacture ),
  CONSTRAINT facture_typefacture_check CHECK (typefacture = ANY (ARRAY[1, 2]))
)

--table produit
produit
(
  idproduit serial NOT NULL,
  refproduit character varying(10) NOT NULL,
  libelleproduit character varying(75) NOT NULL,
  CONSTRAINT produit_pkey PRIMARY KEY (idproduit )
)

--table lignefacture
lignefacture
(
  idfacture integer NOT NULL,
  produit integer NOT NULL,
  quantite smallint NOT NULL,
  prixunitaire integer NOT NULL,
  CONSTRAINT pklignefacture PRIMARY KEY (idfacture , produit ),
  CONSTRAINT lignefacture_idfacture_fkey FOREIGN KEY (idfacture)
      REFERENCES facture (idfacture) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE,
  CONSTRAINT lignefacture_produit_fkey FOREIGN KEY (produit)
      REFERENCES produit (idproduit) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)

--table reglement
reglement
(
  idfacture integer,
  idreglement serial NOT NULL,
  datereglement date NOT NULL,
  montantreglement integer NOT NULL,
  CONSTRAINT reglement_pkey PRIMARY KEY (idreglement ),
  CONSTRAINT reglement_idfacture_fkey FOREIGN KEY (idfacture)
      REFERENCES facture (idfacture) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE CASCADE
)