trigger controlant plusieurs lignes à la fois

alassanediakite · 02/01/2012 19:12:00

Salut
J'ai trois tables factures, lignesfacture et reglementsfatcture. Une facture peut avoir plusieurs règlements, ce qui me pousse à vérifier par trigger que le montant total des règlements d'une facture ne dépasse pas le total réel de la facture.
J'ai tenter de faire ça...

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

Mais j'ai le message:"NEW n'est pas une relation..."
Je croyais que OLD et NEW se comportaient comme INSERTED et DELETED de SQLserver mais la doc me dit que OLD et NEW ne sont que des RECORD.
Je sais que je peux passer par le CHECK, mais là j'apprends les triggers avec postgresql. Alors je voudrais savoir s'il y avait une possibilité avec les triggers?
Merci d'avance.

gleu · 02/01/2012 19:30:15

Vous n'avez pas besoin du INNER JOIN. Ceci devrait fonctionner :

select COALESCE(sum(lignefacture.quantite*lignefacture.prixunitaire),0)- COALESCE(sum(reglement.montant),0)- sum(NEW.montant) 
from lignefacture
left join reglement on lignefacture.idfacture=reglement.idfacture

Cela étant dit, je ne comprends pas pourquoi vous soustrayez le montant du règlement.

alassanediakite · 02/01/2012 19:48:18

Merci de la reponse.
Pour votre question:(total de ligne facture) >= (total déjà payé)+(payement en cours)
Je vais votre solution.

alassanediakite · 02/01/2012 20:08:35

Salut
Le code m'envoie "L'enregistrement NEW n'est pas encore affectée...". Je pense que cela est due au fait que j'ai utilisé BEFOR. Mais j'ai aussi le message "Les tables ne peuvent pas avoir de triggers INSTEAD OF." quand j'essaye INSTEAD OF.
Alors où est le problème avec NEW?
Merci d'avance.

Dernière modification par alassanediakite (02/01/2012 20:19:21)

gleu · 02/01/2012 20:56:29

Le code m'envoie "L'enregistrement NEW n'est pas encore affectée...". Je pense que cela est due au fait que j'ai utilisé BEFORE

Oui, c'est bien ça.

Mais j'ai aussi le message "Les tables ne peuvent pas avoir de triggers INSTEAD OF." quand j'essaye INSTEAD OF.

INSTEAD OF n'est disponible que pour les vues.

Alors où est le problème avec NEW?

Si vous utilisez un trigger BEFORE, il vous faut utiliser OLD et non pas NEW.

alassanediakite · 02/01/2012 21:54:10

Avec BEFOR et OLD j'ai le même message "L'enregistrement OLD n'est pas encore affectée...".
Par ailleur je suis confronté au fait que les vues de pgsql sont en lecture seule et cela m'a amener vers les SYSTEMES DE REGLES de pgsql que je découvre pour la premiere fois comme notion en bases de données. Encore du chemin à faire...
Merci d'avance

gleu · 02/01/2012 22:22:40

Avec BEFOR et OLD j'ai le même message "L'enregistrement OLD n'est pas encore affectée...".

En effet, je me suis trompé. Il faut utiliser un trigger AFTER. Les triggers BEFORE sont déclenchés avant la requête, ni OLD ni NEW ne sont renseignés.

je suis confronté au fait que les vues de pgsql sont en lecture seule et cela m'a amener vers les SYSTEMES DE REGLES de pgsql

Vous pouvez aussi utiliser les triggers INSTEAD OF sur les vues à partir de la version 9.1.

alassanediakite · 08/01/2012 01:34:56

salut
Je retourne vers vous avec maintes chemins essayés. Le dernier est INSTEAD OF avec une vue...

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;

... je ne sais pas comment capter le

RETURN NEW

.
Je reformule la question alors.
J'ai quatre tables

--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
)

Comment faire (avec trigger) que l'ensemble des règlements d'une facture ne dépassent pas le montant total de la dite facture? Que l'INSERTION ou la MODIFICATION concerne une ligne ou plusieurs.
Merci d'avance

Dernière modification par alassanediakite (08/01/2012 01:41:16)

gleu · 08/01/2012 10:54:25

Je pense que je ferais simple. J'ajouterais un trigger sur toute opération dans la table lignefacture, qui se déclenche pour chaque ligne. À chaque exécution, le trigger recalcule la valeur du montantreglement et la met à jour. Et j'ajouterai une contrainte ">0" à la colonne montantreglement de la table reglement. Ainsi, qu'on essaie de mettre à jour directement la colonne montantreglement avec une valeur negative ou automatiquement suite à l'exécution du trigger, la colonne est protégée.

Voici un pseudo code rapide (pratiquement sûr qu'il ne soit pas exact) :

alter table reglement add constraint reg_positif check (montantreglement>0);

pour ajouter la contrainte positive sur la colonne montantreglement

CREATE OR REPLACE FUNCTION maj_montantreglement() RETURNS trigger AS
$BODY$
DECLARE
  total integer;
BEGIN
SELECT INTO total sum(lignefacture.quantite*lignefacture.prixunitaire) FROM lignefacture WHERE idfacture=NEW.idfacture;
UPDATE reglement SET montantreglement=total WHERE idfacture=NEW.idfacture;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

pour créer la fonction trigger, et enfin pour ajouter le trigger :

create trigger tr_montantreglement after insert on lignefacture for each row execute procedure maj_montantreglement();

En fait, ce n'est pas du pseudo-code. C'est testé et approuvé. Voici l'erreur que vous aurez si le montant est négatif :

alassanediakite=# insert into lignefacture values (1, 3, 3, -10);
ERROR:  new row for relation "reglement" violates check constraint "reg_positif"
CONTEXT:  SQL statement "UPDATE reglement SET montantreglement=total WHERE idfacture=NEW.idfacture"
PL/pgSQL function "maj_montantreglement" line 8 at SQL statement

Et dans ce cas, la nouvelle ligne n'apparaît pas, et le montant n'est pas mis à jour.

Cela ne fonctionne que pour l'INSERT. Je vous laisse, pour exercice, de modifier le trigger et la fonction trigger pour prendre en compte la modification et la suppression d'une ligne.

alassanediakite · 08/01/2012 14:57:00

Salut gleu
Je pense que les contenus de mes tables ne sont pas bien compris ou j'ai pas compris votre méthode.
En fait, la table lignefacture enregistre les détails de factures (les produits contenu dans la facture) et la table règlement enregistre les différents règlements (payements) de facture que le client effectue. Le montant total de la facture est calculé par SUM(quantite*prixunitaire) par idfacture. Pour chaque règlement on vérifie que le client ne dépasse pas le montant réel de sa facture. Le trigger doit donc vérifier les entrées ou modifications de la table règlement!!!
Merci de l'accompagnement.
PS: comment peut on signaler des erreurs dans la doc française? Par exemple sur la "mise à jour des vues" il y a une phrase non traduite.

Dernière modification par alassanediakite (08/01/2012 15:31:57)

gleu · 08/01/2012 17:47:34

Je pense que les contenus de mes tables ne sont pas bien compris ou j'ai pas compris votre méthode.

Je pense que j'ai bien compris le but des différentes tables. Vous pouvez procéder autrement pour vérifier les modifications de la table règlement. Notamment, vous pouvez poser une contrainte CHECK pour s'assurer que le montant total du règlement ne soit jamais inférieur à 0. C'est ce que fait la contrainte CHECK montantreglement>=0. Si jamais on essaie de mettre à jour cette colonne, de façon manuelle ou automatique, avec une valeur inférieure à 0, PostgreSQL retourne une erreur. Le trigger placé dans mon exemple s'occupe simplement de mettre à jour la colonne montantreglement, sans vérifier si la valeur est valide ou pas, car c'est la contrainte CHECK qui le fera. Si la contrainte CHECK empêche la modification de la colonne montantreglement, le trigger sort en erreur et la ligne insérée dans lignefacture est annulée.

Avez-vous essayé cette méthode ?

PS: comment peut on signaler des erreurs dans la doc française? Par exemple sur la "mise à jour des vues" il y a une phrase non traduite.

Il suffit de m'envoyer un mail (guillaume at lelarge.info). Merci.

alassanediakite · 09/01/2012 16:59:22

Salut
Il ne s’agit pas de contrôler les insertions ou modifications de la table lignefacture !!! Mais plutôt la table règlement.
La facture est créer avec ses détails (lignefacture) et le client est livré (ou nous recevons les produits) et cette facture est ses détails ne sont plus modifiés. A chaque fois que le client se présente pour payer (ou que nous payons chez le fournisseur) une partie de (ou toute) la facture alors la table règlement est mouvementée. Il s’agit alors de faire en sorte que le client ne paye pas plus que ce qu’il nous doit (ou que nous ne payons pas plus que ce qu'on doit au fournisseur). On peut l’assimiler aussi à un prêt bancaire. Il ne faut pas que les remboursements dépassent le capital + l’interêt.
Exemple
Facture
idfacture datefacture typefacture
1 02/01/2012 vente

lignefacture (ou détail facture)
idfacture produit quantite prixunitaire
1 CPUI4 4 30
1 EHP1755 2 45

reglement
idfacture idreglement datereglement montantreglement
1 1 02/01/2012 90
1 2 10/01/2012 70

Nous voyons que la facture N° 1 fait un total de (4*30)+(2*45)=210. Il ne faut pas alors que le total des différents règlements sur cette facture dépasse 210.

Dernière modification par alassanediakite (09/01/2012 17:01:17)

gleu · 09/01/2012 17:22:15

OK, je n'avais pas pigé le schéma. Donc, dans ce cas, la contrainte CHECK est à abandonner et le trigger doit être mis sur la table reglement. Le trigger calcule dans un premier temps le montant total de la facture avec un SUM sur lignefacture, puis il fait la même chose avec les montantreglement de la table reglement et enfin il compare les deux résultats. Si le montant réglé est inférieur ou égale au montant de la facture, il renvoie NEW. S'il est supérieur, il renvoie NULL. Quelque chose de ce genre :

CREATE OR REPLACE FUNCTION maj_montantreglement() 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 sum(montantreglement) FROM reglement WHERE idfacture=NEW.idfacture;
IF totalreglement>totalfacture THEN
  RETURN NULL;
END IF;
RETURN NEW;
END;
$BODY$
LANGUAGE plpgsql;

Je n'ai pas testé mais ça doit ressembler à ça.

alassanediakite · 09/01/2012 17:33:40

salut
Dans ce code ...

SELECT INTO totalreglement sum(montantreglement) FROM reglement WHERE idfacture=NEW.idfacture;

"sum(montantreglement)" contient-il le montant en cours d'insertion?
Et...

RETURN NEW;

comment utiliser le retour? (ou encore comment utiliser la sortie du trigger?)
Merci d'avance
PS: Je vous ai envoyé l'email sur la doc.

Dernière modification par alassanediakite (09/01/2012 17:34:51)

gleu · 09/01/2012 18:21:03

"sum(montantreglement)" contient-il le montant en cours d'insertion?

Oui.

comment utiliser le retour? (ou encore comment utiliser la sortie du trigger?)

Qu'est-ce que vous voulez en faire ? ce n'est pas à l'utilisateur d'utiliser le retour. Sur une fonction trigger qui renvoit NEW sur un trigger AFTER, la ligne est insérée. Alors que sur une fonction trigger qui renvoit NULL sur un trigger AFTER, l'insertion de la ligne est annulée.

PS: Je vous ai envoyé l'email sur la doc.

Je l'ai bien reçu, je vous en remercie. Je m'en occupe ce soir. N'hésitez pas à me faire parvenir toute autre trouvaille de ce genre.

alassanediakite · 10/01/2012 14:00:48

Salut
J'ai créé une table...

create table controle(tr int, tf int)

pour verifier le trigger.
J'ai alors modifier le trigger ...

-- 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;

Le trigger est mis sur INSTEAD OF INSERT OR UPDATE de vregelement qui est une vue sur la table reglement.
Avec...

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

La table reglement reste vide et la table controle contient [0, 9000] (9000 représente en faite le total de la facture 1)
A présent il y des problèmes.

gleu · 10/01/2012 14:50:45

Dans le cas du RETURN NEW, il faut aussi faire un INSERT dans la bonne table (à savoir reglement). Mon exemple de fonction trigger fonctionne bien pour une table mais pas complètement pour une vue.

Cela étant dit, cela n'explique pas pourquoi le montant total des lignes de factures vaut 0. L'exemple que vous montrez n'est pas complet et donc ne permet pas de vous aider plus.

alassanediakite · 11/01/2012 11:26:35

Salut
Je vous demandais...

"sum(montantreglement)" contient-il le montant en cours d'insertion?

vous avez répondu OUI; donc le code...

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

devrait donner [5000, 9000] dans la table controle ce qui n'est pas le cas.
Par ailleurs le code n'est pas adaptable à la table reglement car pgAdmin me dit qu'il ne peut y avoir de trigger INSETEAD OF sur une table.
Je m'excuse de vous faire perdre du temps. Mais on peut juste répondre à la question suivante: Est il possible (par trigger de postgresql) de récupérer l'ensemble de lignes en cours d'insertion ou de modification, vérifier ce ensemble par agrégation, annuler ou valider selon des conditions?
Merci d'avance.

alassanediakite · 23/01/2012 18:54:16

Salut
Pour ceux qui sont intéresses, j'ai trouvé la solution ailleurs.
Pour l'insertion...

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;

Pour la mise à jour...

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;

Merci beaucoup à vous gleu

#1 02/01/2012 19:12:00

trigger controlant plusieurs lignes à la fois

#2 02/01/2012 19:30:15

Re : trigger controlant plusieurs lignes à la fois

#3 02/01/2012 19:48:18

Re : trigger controlant plusieurs lignes à la fois

#4 02/01/2012 20:08:35

Re : trigger controlant plusieurs lignes à la fois

#5 02/01/2012 20:56:29

Re : trigger controlant plusieurs lignes à la fois

#6 02/01/2012 21:54:10

Re : trigger controlant plusieurs lignes à la fois

#7 02/01/2012 22:22:40

Re : trigger controlant plusieurs lignes à la fois

#8 08/01/2012 01:34:56

Re : trigger controlant plusieurs lignes à la fois

#9 08/01/2012 10:54:25

Re : trigger controlant plusieurs lignes à la fois

#10 08/01/2012 14:57:00

Re : trigger controlant plusieurs lignes à la fois

#11 08/01/2012 17:47:34

Re : trigger controlant plusieurs lignes à la fois

#12 09/01/2012 16:59:22

Re : trigger controlant plusieurs lignes à la fois

#13 09/01/2012 17:22:15

Re : trigger controlant plusieurs lignes à la fois

#14 09/01/2012 17:33:40

Re : trigger controlant plusieurs lignes à la fois

#15 09/01/2012 18:21:03

Re : trigger controlant plusieurs lignes à la fois

#16 10/01/2012 14:00:48

Re : trigger controlant plusieurs lignes à la fois

#17 10/01/2012 14:50:45

Re : trigger controlant plusieurs lignes à la fois

#18 11/01/2012 11:26:35

Re : trigger controlant plusieurs lignes à la fois

#19 23/01/2012 18:54:16

Re : trigger controlant plusieurs lignes à la fois

Pied de page des forums

À propos

Nous contacter

Groupes Locaux

Réseaux Sociaux