Trigger gérant plusieurs lignes (insertion ou modification) à la fois [Résolu]

[alassanediakite]

Salut
J'ai prix une petite appli de gestion de factures.
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.
L'idée est 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(vente ou achat)
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.
Cela est il faisable avec un trigger de PL/pgsql?

[punkoff]

Bonjour,

Je mettrai plutôt une contrainte de type CHECK :
http://www.postgresql.org/docs/9.1/s...nstraints.html

Cette contrainte peut appeler une fonction pl/sql qui elle vérifira ceci.

(ceci étant dit un trigger before insert / update pourra aussi le faire)

[alassanediakite]

salut
J'ai déjà fait avec CHECK...

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
CREATE OR REPLACE FUNCTION fncheckreglement(idf integer, mt integer)
  RETURNS BOOLEAN AS
$BODY$
DECLARE
  totalfacture integer;
  totalreglement integer;
BEGIN
SELECT INTO totalfacture sum(lignefacture.quantite*lignefacture.prixunitaire) FROM lignefacture WHERE idfacture=idf;
SELECT INTO totalreglement coalesce(sum(montantreglement),0)+mt FROM reglement WHERE idfacture=idf;
IF totalreglement>totalfacture THEN
  RETURN false;
END IF;
RETURN true;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Mais là je cherche à comprendre le mécanisme de trigger de PostrgreSQL. En faite c'est plutôt une recherche personnelle sur le SGBD.
Merci d'avance

[punkoff]

Bein avec un trigger, c'est presque pareil sauf qu'il faudra en faire un pour l'insert un autre pour l'update.

Et ils devront être before insert / update.

Il faudra par contre gérer manuellement l'exception en appelant un raise exception.

Un exemple à adapter :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
 
CREATE OR REPLACE FUNCTION "_INSERT_SOC_EXCLUSION"()
  RETURNS TRIGGER AS
$BODY$declare cnt integer;
begin
	SELECT count(*) INTO cnt FROM t_societe_soc WHERE cli_id = new.cli_id;
	IF cnt > 0 then
		raise exception unique_violation USING MESSAGE = 'Duplicate CLI_ID in T_PERSONNE_PER table: ' || new.cli_id;
	end IF;
	RETURN NULL;
end;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;

Le binding du trigger sur la table en question :

Code :

1
2
3
4
5
6
 
CREATE TRIGGER "_INSERT"
  BEFORE INSERT
  ON t_societe_soc
  FOR EACH ROW
  EXECUTE PROCEDURE "_INSERT_SOC_EXCLUSION"();

[alassanediakite]

Salut
La solution donnée n'est pas adaptable car elle ne prend pas en compte les données en insertion ou en update. De plus avec BEFORE les données OLD et NEW sont vides. Par ailleurs les tables de PostegreSQL n'acceptent pas INSTEAD OF. J'avais poser ma question sur un autre forum sans succès. Je pense que la solution avec les triggers n'est pas encore possible.

[punkoff]

Bonjour,

Envoyé par alassanediakite

Salut
La solution donnée n'est pas adaptable car elle ne prend pas en compte les données en insertion ou en update.

C'est pour ca que je spécifiais qu'il fallait 2 trigger, 1 en insert et 1 en update.

De plus avec BEFORE les données OLD et NEW sont vides.

Non pas vraiment, les valeurs new / old sont bien allimentées.

Par ailleurs les tables de PostegreSQL n'acceptent pas INSTEAD OF. J'avais poser ma question sur un autre forum sans succès. Je pense que la solution avec les triggers n'est pas encore possible.

Effectivement, mais ici ce n'est pas instead of qui est utilisé.


Avez-vous au moins testé ?

[alassanediakite]

J'ai vraiment essayé. Un lien en mp.

[punkoff]

Oui étrange pourtant Gleu est une personne qui s'y connais plus que moi ...

Ceci étant dit en 9.1 :

Code :

1
2
3
4
5
6
7
8
 
CREATE TABLE facture (id integer PRIMARY KEY, type_fac varchar(10));
CREATE TABLE lig_facture(id integer REFERENCES facture(id), id_produit integer, quantite integer, val_unit integer, PRIMARY KEY (id, id_produit));
CREATE TABLE reglement (id_facture integer REFERENCES facture(id), id_reglement integer, dt_reglement date, mont_reglement integer, PRIMARY KEY(id_facture, id_reglement));
 
 
INSERT INTO facture VALUES (1, 'vente');
INSERT INTO lig_facture VALUES (1, 1, 4, 30), (1, 2, 2, 45);

Maintenant la fonction trigger + binding :

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
 
CREATE OR REPLACE FUNCTION _INSERT_REGLEMENT_EXCLUSION()
  RETURNS TRIGGER AS
$BODY$
declare 
	montant integer := 0;
	montant_init integer := 0;
begin
	SELECT COALESCE(sum(mont_reglement), 0) INTO montant FROM reglement a WHERE a.id_facture = new.id_facture;
	SELECT sum(val_unit*quantite) INTO montant_init FROM lig_facture WHERE id = new.id_facture;
	IF montant + new.mont_reglement > montant_init then
		raise exception unique_violation USING MESSAGE = 'Montant trop grand ';
	end IF;
	RETURN NULL;
end;$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
 
 
CREATE TRIGGER "_INSERT_TRIG"
  BEFORE INSERT
  ON reglement
  FOR EACH ROW
  EXECUTE PROCEDURE _insert_reglement_exclusion();

Le test avec une valeur trop grande :

Code :

1
2
3
4
5
6
7
8
9
10
 
INSERT INTO reglement VALUES (1, 2, now(), 50000000);
 
ERREUR:  Montant trop grand 
 
 
********** Erreur **********
 
ERREUR: Montant trop grand 
État SQL :23505

Le test avec un montant ok :

Code :

1
2
3
4
 
INSERT INTO reglement VALUES (1, 3, now(), 50);
 
La requête a été exécutée avec succés : 0 ligne modifiée. La requête a été exécutée en 31 ms.

edit : ah je viens de voir que l'insert ne c'est pas fait ... je cherche !

[punkoff]

Ok, dans le trigger il faut mettre return new et non return null.

Sinon en rajoutant, dans le trigger ce genre de code :

Code :

1
2
 
raise notice USING message = new.montant;

Vous verrez que les la valeur new / old sont bien alimentées.

[alassanediakite]

Merci de cette réponse. Je vais voir tout ça.

[alassanediakite]

Salut et très grand merci pour la solution.
Sur insert:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Function: _insert_reglement_exclusion()
 
-- DROP FUNCTION _insert_reglement_exclusion();
 
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;

sur update:

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
 
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;

Les deux marchent très bien, même en cas d'insertion ou modification multiples le tout est annuler d'un coup.