Forums PostgreSQL.fr

Rahan

Membre

Trigger de gestion des stock.

Bonjour,

Je suis plus ou moins débutant sur postgres. Je conçois actuellement une base de données pour la gestion de stock.
J'ai donc créer une table produit:

CREATE TABLE t_product
(
  id_product bigserial NOT NULL,
  code character varying(4),
  "name" character varying(50) NOT NULL,
  description character varying(250) NOT NULL,
  price_ht double precision NOT NULL,
  stock_web integer NOT NULL,
  picture_id integer,
  category_id integer NOT NULL,
  CONSTRAINT id_product PRIMARY KEY (id_product),
  CONSTRAINT category_idep FOREIGN KEY (category_id)
      REFERENCES t_category (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT picture_idp FOREIGN KEY (picture_id)
      REFERENCES t_picture (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE t_product OWNER TO postgres;

Une table commande:

 CREATE TABLE t_order
(
  id serial NOT NULL,
  create_date date NOT NULL,
  price_ht double precision NOT NULL,
  user_id integer NOT NULL,
  CONSTRAINT order_id PRIMARY KEY (id),
  CONSTRAINT user_ido FOREIGN KEY (user_id)
      REFERENCES t_user (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE

Et une table permettant le lien entre commande et produit:

 CREATE TABLE t_order_product
(
  id serial NOT NULL,
  product_id integer NOT NULL,
  quantity integer,
  price_ht double precision NOT NULL,
  order_id integer NOT NULL,
  CONSTRAINT order_product_id PRIMARY KEY (id),
  CONSTRAINT order_idop FOREIGN KEY (order_id)
      REFERENCES t_order (id) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION,
  CONSTRAINT product_idop FOREIGN KEY (product_id)
      REFERENCES t_product (id_product) MATCH SIMPLE
      ON UPDATE NO ACTION ON DELETE NO ACTION
)
WITH (
  OIDS=FALSE
);
ALTER TABLE t_order_product OWNER TO postgres;

Aprés plusieurs heures de recherche sur le net et d'essai, je n'ai pas réussi à concevoir un trigger et fonction associé qui permettrait de mettre à jour la champs stock_web de la table t_product à partir de la quantité commandée, renseignée dans le champ quantity de la table t_order_product. Tout ce que j'ai trouvé était à executer sous oracle ou mysql serveur:
http://www.sqlfr.com/codes/TRIGGER-MAJ_48866.aspx

Voilà ou j'en suis actuellement:

 DROP FUNCTION gest_stock()cascade;
CREATE OR REPLACE FUNCTION gest_stock()
  RETURNS opaque AS
$BODY$
	declare stock varchar;
	declare	id_prod integer;
	declare	q integer;
	begin
		select into q new.quantity
		where id_prod = new.product_id;
	update t_product
			set stock_web = stock_web + q 
			where id_product=id_prod;
		q = 0;
		select into q old.quantity 
		where id_prod = product_id;

	update t_product
			set stock_web = stock_web - q 
			where id_product=id_prod;
	return null;	
	end;

$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION gest_stock() OWNER TO postgres;

CREATE TRIGGER gestion_stock
  after INSERT or delete or update
  ON t_order_product
  FOR EACH ROW
  EXECUTE PROCEDURE gest_stock();

Ca ne marche bien évidemment pas.

Merci d'ores et déjà pour toute l'aide et les réponses que vous pourrez m'apporter.

---

Julien
_____________________
« C'est parce qu'on imagine simultanément tous les pas qu'on devra faire qu'on se décourage, alors qu'il s'agit de les aligner un à un. » Marcel Jouhandeau

gleu

Administrateur

Re : Trigger de gestion des stock.

RETURNS opaque

C'est une ancienne déclaration d'un trigger. Vous devriez plutôt utiliser « RETURNS trigger ». Enfin, tout dépend de votre version de PostgreSQL évidemment.

select into q new.quantity where id_prod = new.product_id;

Ça ne veut rien dire. Un SELECT ... WHERE sans FROM, ça ne peut pas fonctionner. Comme je suppose que vous voulez récupérer la nouvelle quantité, il vaudrait mieux faire un « q := new.quantity; » ou un « SELECT INTO q new.quantity ».

Attention au fait que l'alias new n'est disponible que pour les INSERT et les UPDATE.

update t_product set stock_web = stock_web + q where id_product=id_prod;

id_prod n'est pas initialisé. Cette variable vaut donc NULL. Cet UPDATE ne se fera donc pas.

q = 0;

L'affectation utilise l'opérateur := et non pas =

select into q old.quantity where id_prod = product_id;

Attention au fait que l'alias old n'est disponible que pour les DELETE et les UPDATE.

update t_product set stock_web = stock_web - q where id_product=id_prod;

id_prod n'est pas initialisé. Cette variable vaut donc NULL. Cet UPDATE ne se fera donc pas.

D'une façon générale, si c'est votre premier trigger, ne commencez pas en voulant tout gérer d'un coup. Commencez avec un trigger sur une seule opération. Faites trois triggers avec trois procédures stockées. Une fois qu'ils sont tous fonctionnels, vous pourrez les assembler en un seul trigger et une seule fonction. C'est beaucoup plus simple et rapide de faire ainsi.

---

Guillaume.

Marc Cousin

Membre

Re : Trigger de gestion des stock.

Bonjour,
Je pense que votre principal problème vient de la non-compréhension de ce qu'est l'objet NEW : il s'agit de l'enregistrement qui vient d'être modifié par la requête déclenchant le trigger. Vous n'avez donc pas à faire de requête
select into q new.quantity
        where id_prod = new.product_id;
Pour récupérer les informations sur l'enregistrement en cours.

si vous voulez la quantité et l'id du produit, utilisez directement new.quantity et new.product_id dans la suite du code. Ou stockez les valeurs dans des variables si vous voulez, mais par affectation simple.

Au passage, l'opérateur de comparaison est =, celui d'affectation :=, en PL. Le = est toléré en affectation pour le moment, mais utilisez :=
(par exemple : q := 0;  )

---

Marc.

Rahan

Membre

Re : Trigger de gestion des stock.

Merci à vous deux pour vos réponses rapides.

Pour info j'ai bien compris ce qu'est l'objet NEW et je l'avait utilisé comme tel dans un précédent trigger:

 Create or replace function ins_stock () returns opaque as $$
	Begin
		UPDATE t_product
		Set stock_web=stock_web - new_product.quantity
		Where id_product = insert_product_id;
	Return null;
	End;
$$ language plpgsql;

Create trigger ins_stock_O
	after Insert on t_order_product
	for each row execute procedure ins_stock();

Mais comme cela me renvoyait une erreur, j'ai essayé d'adapter du haut de ma petite expérience un code écrit pour mysql serveur ce qui à donné le triste résultat que j'ai publié au dessus.

Aprés avoir pris en compte vos corrections mon programme me renvoi exactement la même erreur lorsque j'essaye de rentrer une nouelle ligne dans ma table t_order_product à savoir:

ERREUR: une instruction insert ou update sur la table "t_order_product" viole la contrainte de clé étrangère "product_idop"
DETAIL: La clé (product_id)= (2) n'est pas présente dans la table "t_product".

Voilà le trigger corrigé:

 CREATE OR REPLACE FUNCTION gest_stock()
  RETURNS trigger AS
$BODY$
	declare stock varchar;
	declare	id_prod integer;
	declare	q integer;
	begin
		select into q new.quantity;
		select into id_prod new.product_id;
	update t_product
			set stock_web = stock_web + q 
			where id_product=id_prod;
		q :=0;
		select into q old.quantity;
		select into id_prod old.product_id;
	update t_product
			set stock_web = stock_web - q 
			where id_product=id_prod;
	return null;	
	end;
$BODY$
  LANGUAGE 'plpgsql' VOLATILE
  COST 100;
ALTER FUNCTION gest_stock() OWNER TO postgres;

Mais j'ai lu ici http://blog.guillaume.lelarge.info/inde … g%C3%A8res (c'est d'ailleurs votre blog Gleu) , si j'ai bien compris, qu'il y avait un problème sous postgres avec des trigger qui désactivé les clés étrangères.

Est-ce que ce que je cherche à faire est impossible?
La version de postgreSQL que j'utilise: 8.4

Julien

---

Julien
_____________________
« C'est parce qu'on imagine simultanément tous les pas qu'on devra faire qu'on se décourage, alors qu'il s'agit de les aligner un à un. » Marcel Jouhandeau

Marc Cousin

Membre

Re : Trigger de gestion des stock.

Êtes vous sûr que le problème vienrt du trigger ?

une instruction insert ou update sur la table "t_order_product". Le code de votre trigger ne touche pas à cette table.

Par ailleurs, pour plus de simplicité, remplacez :
select into q new.quantity;
select into id_prod new.product_id;

Par   
     
q:= new.quantity;
id_prod:= new.product_id;

---

Marc.

gleu

Administrateur

Re : Trigger de gestion des stock.

L'erreur dont vous parlez ne provient pas du trigger. Une contrainte de type clé étrangère empêche l'insertion de la ligne car il n'existe pas de ligne avec un product_id à 2 pour la table t_product. Enlevez le trigger, et vous aurez la même erreur.

Concernant le billet sur mon blog, non, vous n'avez pas compris. J'ai du mal expliquer ça. J'indique dans ce billet que désactiver les triggers a pour conséquence de désactiver aussi les clés étrangères. Conséquence surprenante (et qui m'a surpris) mais logique quand on connait (apprend) l'implémentation des clés étrangères dans PostgreSQL.

---

Guillaume.

Rahan

Membre

Re : Trigger de gestion des stock.

Effectivement, désactiver le trigger n'y fait rien. Cependant il existe bien une ligne pour laquelle l'id_product (qui correspond au champ product_id en tant que clé étrangère dans la table t_order_product) est égal à 2 dans la table t_product. Voilà pourquoi je ne comprends pas l'erreur.
Est-ce que vous auriez une idée? Avez vous besoin d'informations supplémentaires pour pouvoir me répondre?

Merci pour la petite explication du billet. Ce n'est donc pas un bug.

Dernière modification par Rahan (29/04/2010 17:11:29)

---

Julien
_____________________
« C'est parce qu'on imagine simultanément tous les pas qu'on devra faire qu'on se décourage, alors qu'il s'agit de les aligner un à un. » Marcel Jouhandeau

gleu

Administrateur

Re : Trigger de gestion des stock.

Désolé mais il vous dit clairement qu'il n'y a pas de colonne product_id à 2 pour la table t_product.

Qu'avez-vous en faisant un "SELECT count(*) FROM t_product WHERE product_id=2" ?

---

Guillaume.

Rahan

Membre

Re : Trigger de gestion des stock.

Il me renvoie 1 (une entrée).

Et, très interessant si je modifie mon trigger le passant de "after insert or update..." à "before insert or...".
Il ne me renvoi pas d'erreur mais bien évidemment il ne conserve pas la ligne rentrée dans la table t_order_product" puisque la fonction associée au trigger comprend un "return null", si je met "return new", l'erreur est a nouveau renvoyée.

Est-ce que cela permet d'avancer?

Dernière modification par Rahan (29/04/2010 17:37:08)

---

Julien
_____________________
« C'est parce qu'on imagine simultanément tous les pas qu'on devra faire qu'on se décourage, alors qu'il s'agit de les aligner un à un. » Marcel Jouhandeau

gleu

Administrateur

Re : Trigger de gestion des stock.

Il y a d'autres triggers sur la table t_product ?

---

Guillaume.

Rahan

Membre

Re : Trigger de gestion des stock.

Il y en a eu mais il n'y en a plus. Le problème ne vient donc pas de là.
Je ne comprends vraiment pas pourquoi cela ne marche pas.
Toute les idées sont les bienvenues.
Merci d'avance.

Dernière modification par Rahan (01/05/2010 17:09:15)

---

Julien
_____________________
« C'est parce qu'on imagine simultanément tous les pas qu'on devra faire qu'on se décourage, alors qu'il s'agit de les aligner un à un. » Marcel Jouhandeau

Rahan

Membre

Re : Trigger de gestion des stock.

Je reposte à nouveau espérant que quelqu'un puisse m'apporter une réponse car je suis vraiment coincé.
Je me rends compte avoir oublié de mentionné que la table t_product à laquelle fait référence la table t_order_product est alimentée par 4 tables enfants. Est-ce que le problème pourrez venir de là?

Je résume, ma table t_order_product est relié (via la possession d'une clé étrangére) à une table t_order et une table t_product (la même qui est en relation avec 4 tables enfants). Cette dernière table posséde 2 clés étrangères la reliant à une table t_picture, t_catégorie.

La question reste la même: pourquoi j'ai le message d'erreur ci dessous quand j'essaye d'insérer une nouvelle ligne dans ma table t_order_product?

ERREUR: une instruction insert ou update sur la table "t_order_product" viole la contrainte de clé étrangère "product_idop"
    DETAIL: La clé (product_id)= (2) n'est pas présente dans la table "t_product".

D'avance merci pour toutes vos réponses.

---

Julien
_____________________
« C'est parce qu'on imagine simultanément tous les pas qu'on devra faire qu'on se décourage, alors qu'il s'agit de les aligner un à un. » Marcel Jouhandeau

gleu

Administrateur

Re : Trigger de gestion des stock.

Il serait beaucoup plus simple, pour pouvoir vous aider, de fournir un mini dump permettant de reproduire votre situation : ordre de création des différentes tables, clés étrangères, trigger et le strict minimum de données, qui nous permettraient de reproduire.

---

Guillaume.

Rahan

Membre

Re : Trigger de gestion des stock.

Pas de soucis, tout ça est déjà prêt et prévu, mais je préférerai ne pas le rendre public, je peux vous l'envoyer par mail ou autre moyen?
Bien évidemment la solution, si elle existe sera mise sur le forum.

---

Julien
_____________________
« C'est parce qu'on imagine simultanément tous les pas qu'on devra faire qu'on se décourage, alors qu'il s'agit de les aligner un à un. » Marcel Jouhandeau

gleu

Administrateur

Re : Trigger de gestion des stock.

OK pour le mail. guillaume at lelarge point info. Je regarderais ça ce soir par contre.

---

Guillaume.

Rahan

Membre

Re : Trigger de gestion des stock.

Ok, merci beaucoup. Je vous envoi ça ce soir vers 19h.

---

Julien
_____________________
« C'est parce qu'on imagine simultanément tous les pas qu'on devra faire qu'on se décourage, alors qu'il s'agit de les aligner un à un. » Marcel Jouhandeau