Trigger de gestion de stock

**Rahanin** · 30/04/2010, 17h08

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:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
 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:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
 
 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, jai réussi à concevoir un trigger et fonction associée qui permettent 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.
Voici mon trigger:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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

Mon programme me renvoi cette erreur lorsque j'essaye de rentrer 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".

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.

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.
Dernière information: il n'y a pas d'autres triggers sur la table t_product.

La version de postgreSQL que j'utilise: 8.4.

Voilà, merci de m'avoir lu jusqu'au bout. Pourriez vous m'aider?

Julien

**estofilo** · 30/04/2010, 17h51

Tu sembles soupçonner le trigger, et il y a certes des choses discutables dans le code du trigger, mais un premier test à faire serait de voir si l'erreur se produit quand même avec ce trigger totalement désactivé. En effet d'après le code et le message d'erreur montrés, il semblerait plutôt que l'erreur n'ait pas de rapport avec le trigger.

**Rahanin** · 30/04/2010, 18h19

Merci de cette réponse rapide Estofilo.
J'ai effectivement testé l'ajout d'une observation dans la table t_order_product après avoir désactivé le trigger et effectivement l'erreur est toujours renvoyée.
Je n'ai vraiment plus d'idée d'origine possible de ce problème.

Des idées? Avez vous besoin d'informations supplémentaires?

**estofilo** · 30/04/2010, 21h51

Je suggère de poster un jeu d'essai qui permette de reproduire le problème.

**Rahanin** · 01/05/2010, 13h03

Par jeu d'essai, vous suggérez la mise en ligne de données permettant de remplir la bd ou la structure (a savoir les codes pour les tables order, client et type_client)?

**estofilo** · 01/05/2010, 13h41

Idéalement il faut une suite d'INSERT qui en partant d'une base vide, met suffisamment de données pour reproduire le problème. Une seule ligne dans chaque table peut éventuellement suffire. Il est aussi possible d'élaguer en omettant des colonnes qui ne contribuent pas au problème.

**Rahanin** · 01/05/2010, 15h33

Voici les lignes pour remplir la table t_product:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
 
INSERT INTO t_product VALUES 
(1,'B78','Boite','blabla',4.5,4,4,2),
(2,'Bo987','Livre Tao','blabla2',12.2,17,3,4),
(3,'T034','Oolong','blabla3',9.5,1000,2,1),
(4,'G678','Bonbon','blabla4',5,10,5,3);

et celle pour remplir la table t_order_product

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
 
INSERT INTO t_order_product VALUES (1,3,100,9.5,1);

C'est après cette dernière qu'une erreur est renvoyée.

**estofilo** · 01/05/2010, 15h53

les INSERT ne sont pas utilisables tels quels car les données supposent l'existence d'autres entrées dans les tables t_category et t_order. Il faut tous les INSERT de toutes les tables, de sorte qu'une exécution de ces INSERT se termine avec l'erreur à reproduire.

**Rahanin** · 01/05/2010, 17h46

Voici les autres lignes:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
 
INSERT INTO t_category (id, nom)
VALUES  (1,'plante'), (2,'accessoire'), (3,'gâteau'), (4,'livre'); 
 
INSERT INTO t_picture (id, nom)
VALUES  (1,'picture1'), (2,'picture2'), (3,'picture3'), (4,'picture4'),(5,'picture5'), (6,'picture6'); 
 
INSERT INTO t_user (id, nom)
VALUES  (1,'Jim'), (2,'Jhon'), (3,'Silvie'); 
 
INSERT INTO t_order (id, user_id, date, price)
VALUES  (1,1,'23/12/1999',12.5), (2,2,'03/08/2009',60.6);

Est-ce qu'il vous faut d'autres éléments?

**estofilo** · 01/05/2010, 20h56

Dans ce code:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INSERT INTO t_product VALUES 
(1,'B78','Boite','blabla',4.5,4,4,2),
(2,'Bo987','Livre Tao','blabla2',12.2,17,3,4),

la 2eme ligne ne peut pas être insérée car 'Bo987' fait 5 caractères alors que la colonne est limitée à 4 caractères. Du coup si ce produit n'est pas créé il est normal qu'une commande qui y fait référence ne puisse pas être insérée vu les contraintes d'intégrité.

**Rahanin** · 01/05/2010, 21h35

Autant pour moi, je l'ai changé et l'erreur est toujours la même!

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
INSERT INTO t_product VALUES 
(1,'B78','Boite','blabla',4.5,4,4,2),
(2,'Bo87','Livre Tao','blabla2',12.2,17,3,4),

**estofilo** · 04/05/2010, 13h53

En mettant ensemble ces éléments dans un seul script j'arrive à:

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
 
CREATE TABLE t_category (id int primary key, nom text);
CREATE TABLE t_user (id int primary key, nom text);
CREATE TABLE t_picture (id int primary key, nom text);
 
CREATE TABLE t_product
(
  id_product bigserial NOT NULL,
  code character varying(5),
  "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
);
 
 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
);
 
 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
);
 
 
INSERT INTO t_category (id, nom)
VALUES  (1,'plante'), (2,'accessoire'), (3,'gâteau'), (4,'livre'); 
 
INSERT INTO t_picture (id, nom)
VALUES  (1,'picture1'), (2,'picture2'), (3,'picture3'), (4,'picture4'),(5,'picture5'), (6,'picture6'); 
 
INSERT INTO t_user (id, nom)
VALUES  (1,'Jim'), (2,'Jhon'), (3,'Silvie'); 
 
INSERT INTO t_order (id, user_id, create_date, price_ht)
VALUES  (1,1,'1999-12-23',12.5), (2,2,'2009-08-03',60.6); 
 
INSERT INTO t_product VALUES 
(1,'B78','Boite','blabla',4.5,4,4,2),
(2,'Bo87','Livre Tao','blabla2',12.2,17,3,4),
(3,'T034','Oolong','blabla3',9.5,1000,2,1),
(4,'G678','Bonbon','blabla4',5,10,5,3); 
 
INSERT INTO t_order_product VALUES (1,3,100,9.5,1);

Et le dernier insert ne produit pas d'erreur.

**Rahanin** · 06/05/2010, 15h49

Merci Estofilo. Je regarde ça demain soir et je reviens vers vous. Apparemment, vous n'avez pas changé le code. Le problème viendrai peut-être d'une sauvegarde et restauration ou de la création puis la suppression d'un trigger. Je vais donc recréer les tables depuis le début.

Bon, j'ai recréer mais table mais l'erreur survient toujours. Cependant 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à?

[Merci encore pour toute cette aide Esofilo (si c'est plus simple je peux vous envoyer le fichier entier de création de la base (code ou backup) ainsi que le fichier avec quelques lignes pour remplir la base (juste le nécessaire pour la tester sur le problème précis qui m'occupe.]

Trigger de gestion de stock

PostgreSQL

Discussions similaires

Partager

Partager