Discussion :

[SQLpro]

ha ba, ce serait nouveau....

A +

[fsmrel]

Buenos dias,

Pour changer à nouveau, voyons voir avec PostgreSQL. Pour ne rien rater, j’ai installé la V16.

Mutatis mutandis, Je reprends, ce que j’ai proposé ici
 
(1) Création des tables :

Code SQL :

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 TABLE Produit
(
   RefProduit CHAR(3),
   Designation VARCHAR(50) NOT NULL,
   Prix SMALLINT NOT NULL,
   CONSTRAINT Produit_PK PRIMARY KEY(RefProduit),
   CONSTRAINT Produit_AK UNIQUE(Designation)
);
 
CREATE TABLE Devis
(
   NumDevis char(3) not null,
   DateDevis DATE NOT NULL,
   CONSTRAINT Devis_PK PRIMARY KEY(NumDevis)
);
 
CREATE TABLE LigneDevis
(
   RefProduit CHAR(3),
   NumDevis char(3),
   Quantite SMALLINT NOT NULL,
   CONSTRAINT LigneDevis_PK PRIMARY KEY(RefProduit, NumDevis),
   CONSTRAINT LigneDevis_Produit_FK FOREIGN KEY(RefProduit) REFERENCES Produit(RefProduit) ON DELETE CASCADE,
   CONSTRAINT LigneDevis_Devis_FK FOREIGN KEY(NumDevis) REFERENCES Devis(NumDevis) ON DELETE CASCADE
);

 
(2) Création de quelques produits :
 

Code SQL :

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

1
2
3
4
5
6
7
insert into Produit (RefProduit, Designation, Prix)
  values
    ('p01', 'bonbons', 100)
  , ('p02', 'caramels', 200)
  , ('p03', 'esquimaux', 300)
  , ('p04', 'chocolats', 400)
;

 

Select * from Produit :

Code :

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

1
2
3
4
5
"refproduit"  "designation"  "prix"
 "p01"         "bonbons"      100
 "p02"         "caramels"     200
 "p03"         "esquimaux"    300
 "p04"         "chocolats"    400

 
(3) Création de la vue comportant la jointure des tables Devis et LigneDevis
 

Code SQL :

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

1
2
3
4
5
6
create view DevisVue
  (NumDevis, DateDevis, RefProduit, Quantite)
as 
  select x.NumDevis, x.DateDevis, y.RefProduit, y.Quantite  
  from Devis as x 
     join LigneDevis as y on x.NumDevis = y.NumDevis ;

 
(4) Création de la fonction appelée par le trigger
 

Code SQL :

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
CREATE FUNCTION devis_vue_fonction() RETURNS trigger AS $$
begin
  if
    (select count(NumDevis)
     from devis
     where NumDevis = new.NumDevis) = 0
   then
     insert into Devis
       values (new.NumDevis, new.DateDevis)  ;
  end if ;
 
  insert into LigneDevis
    values (new.NumDevis, new.RefProduit, new.Quantite)
  ;
  return null ;
end ;
$$ LANGUAGE plpgsql ;

 
(5) Création du trigger utilisé pour la vue
 

Code SQL :

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

1
2
3
create trigger devis_vue_trigger instead of insert on DevisVue 
  for each row execute function devis_vue_fonction() 
;

 
(6) Insert dans la vue :

Code SQL :

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

1
2
3
4
5
6
insert into DevisVue (NumDevis, DateDevis, RefProduit, Quantite)
  values
    (1, '2000-01-01', 'p01', 100) 
  , (1, '2000-01-01', 'p02', 150)
  , (2, '2000-01-02', 'p01', 200)
;

 
Au résultat
 

Code SQL :

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

select * from Devis ;

 

 
 

Code SQL :

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

select * from LigneDevis ;

 

 
 

 

[fsmrel]

Bonjour,
 
A propos des triggers SQL Server et PostgreSQL

A l’occasion des inserts dans les vues, j’avoue préférer l’approche SQL Server. En effet – voir le message ici, point (5) – celui-ci me passe une table INSERTED me permettant de faire un traitement ensembliste, ce qui est naturel dans un contexte relationnel. A l’opposé, je constate que PostgreSQL me force à effectuer un traitement ligne par ligne, point (5) :
 

Code :

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

1
2
create trigger devis_vue_trigger instead of insert on DevisVue 
  for each row execute function devis_vue_fonction()

 
Cela me fait penser à la programmation que je pratiquais en 1965 : accès séquentiel, un par un, aux enregistrements dans les fichiers...

Irrésistiblement, me vient en tête la métaphore de la boîte de petits pois :

Pour faire cuire les petits bois, j’ouvre la boîte et la verse directement dans la casserole (approche SQL Server, ensembliste), tandis je peux aussi récupérer chaque petit pois dans la boîte, chacun son tour, et le déposer (délicatement) dans la casserole (approche PostgreSQL, ligne par ligne).

Je ne suis pas fin connaisseur de PostgreSQL, mais je suis surpris par l’approche non ensembliste : en gros, le trigger se contente d’effectuer un « open/close » et balance les lignes une par une à la fonction à laquelle il fait appel.

@Fred,

En plus de l’approche archaïque, je subodore que sur le plan des performances ça doit être moins bon qu’avec SQL Server... Qu’en penses-tu ?

Accessoirement, MySQL (create trigger ... for each row) et PostgreSQL même combat (ligne par ligne).

Au plaisir

[CinePhil]

La doc de MariaDB est muette qur la signification de "FOR EACH ROW".

MySQL en parle un peu plus mais le FOR EACH ROW reste obligatoire :

The statement following FOR EACH ROW defines the trigger body; that is, the statement to execute each time the trigger activates, which occurs once for each row affected by the triggering event.

Celle de PostgreSQL par contre, dit ceci :

Un déclencheur marqué FOR EACH ROW est appelé pour chaque ligne que l'opération modifie. Par exemple, un DELETE affectant dix lignes entraîne dix appels distincts de tout déclencheur ON DELETE sur la relation cible, une fois par ligne supprimée. Au contraire, un déclencheur marqué FOR EACH STATEMENT ne s'exécute qu'une fois pour une opération donnée, quelque soit le nombre de lignes modifiées (en particulier, une opération qui ne modifie aucune ligne résulte toujours en l'exécution des déclencheurs FOR EACH STATEMENT applicables). Notez qu'avec un INSERT utilisant une clause ON CONFLICT DO UPDATE, un trigger au niveau requête sera exécuté pour un INSERT ou un UPDATE.

Les déclencheurs définis en remplacement (INSTEAD OF) doivent obligatoirement être marqués FOR EACH ROW, et ne peuvent être définis que sur des vues. Les déclencheurs BEFORE et AFTER portant sur des vues devront quant à eux être marqués FOR EACH STATEMENT.

Le FOR EACH ROW est donc obligatoire sur les TRIGGER INSTEAD OF mais pas sur un trigger simple.

[fsmrel]

Merci Philippe pour l'information.

Dans mon cas (insert dans une vue), je n'ai donc, hélas ! pas le choix...

[escartefigue]

La doc de MariaDB est muette sur la signification de "FOR EACH ROW".
Le FOR EACH ROW est donc obligatoire sur les TRIGGER INSTEAD OF mais pas sur un trigger simple.

Quoi qu'il en soit, ni MariaDB ni MySQL ne proposent des TRIGGER "instead OF"

[Paprick]

Bonsoir,
La solution PostgreSQL proposée par François me parait sympathique, bien lisible, et donc assez facile à expliquer à mes chers étudiants.
Et pour être complet, il faut aussi empêcher la suppression de la dernière ligne d'un devis... Je suppute un petit trigger pour contrôler ça, non ?

[Paprick]

Bonsoir,
Voici ma proposition PostgreSQL pour régler le problème de la suppression de la dernière ligne d'un devis :

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 FUNCTION Delete_Ligne() RETURNS TRIGGER AS '
BEGIN
    IF (SELECT COUNT(*) FROM LigneDevis WHERE NumDevis=OLD.NumDevis) > 1
    THEN
        RETURN OLD;
    ELSE
        RAISE integrity_constraint_violation;
        RETURN NULL;
    END IF;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER T_Delete_Ligne BEFORE DELETE ON LigneDevis
FOR EACH ROW EXECUTE FUNCTION Delete_Ligne();

Simple, mais a priori opérationnel !
Qu'en pensez-vous

[fsmrel]

Ave Paprick,

Voici ma proposition PostgreSQL pour régler le problème de la suppression de la dernière ligne d'un devis :

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 FUNCTION Delete_Ligne() RETURNS TRIGGER AS '
BEGIN
    IF (SELECT COUNT(*) FROM LigneDevis WHERE NumDevis=OLD.NumDevis) > 1
    THEN
        RETURN OLD;
    ELSE
        RAISE integrity_constraint_violation;
        RETURN NULL;
    END IF;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER T_Delete_Ligne BEFORE DELETE ON LigneDevis
FOR EACH ROW EXECUTE FUNCTION Delete_Ligne();

Simple, mais a priori opérationnel !
Qu'en pensez-vous

Petit problème quand même : on ne peut plus supprimer les devis...

Je suis noyé dans la masse hétérogène des messages de la discussion, peux-tu en ouvrir une nouvelle, dédiée à ce problème...

[fsmrel]

J’ai essayé ce qui suit :

Code SQL :

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

1
2
3
insert into Devis(NumDevis, DateDevis) values (1, '2000-01-01') ;
insert into LigneDevis( RefProduit, NumDevis, Quantite)
values ('p01', 1, 150), ('p02', 1, 200) ;

 
puis
 

Code SQL :

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

1
2
3
4
 
alter table Lignedevis disable trigger T_Delete_Ligne ;
 
delete from devis where numdevis = 1 ;

 
Ça se passe bien, le devis a été supprimé avec ses lignes !
Je n’ai plus qu’à réactive le trigger :
 

Code SQL :

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

 alter table Lignedevis enable trigger T_Delete_Ligne ;

 
That's all folks!

Il est quand même dommage de devoir en passer par un alter table, contrairement à SQL Server...

[Paprick]

Hello !
Toujours aussi vigilant François !
J'ai une autre solution qui pourrait vous plaire : si le devis est supprimé, on accepte la suppression de toutes les lignes.

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
CREATE FUNCTION Delete_Ligne() RETURNS TRIGGER AS '
BEGIN
  IF ((SELECT COUNT(*) FROM LigneDevis WHERE NumDevis=OLD.NumDevis) > 1)
  OR ((SELECT COUNT(*) FROM Devis WHERE NumDevis=OLD.NumDevis) = 0)
  THEN
     RETURN OLD;
  ELSE
     RAISE integrity_constraint_violation;
     RETURN NULL;
   END IF;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER T_Delete_Ligne BEFORE DELETE ON LigneDevis
FOR EACH ROW EXECUTE FUNCTION Delete_Ligne();

Je pense avoir testé tous les cas, et ça a l'air de bien fonctionner ...
Mais est-ce à l'épreuve des balles de François ?

[fsmrel]

Hello !
Toujours aussi vigilant François !
J'ai une autre solution qui pourrait vous plaire : si le devis est supprimé, on accepte la suppression de toutes les lignes.

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
CREATE FUNCTION Delete_Ligne() RETURNS TRIGGER AS '
BEGIN
  IF ((SELECT COUNT(*) FROM LigneDevis WHERE NumDevis=OLD.NumDevis) > 1)
  OR ((SELECT COUNT(*) FROM Devis WHERE NumDevis=OLD.NumDevis) = 0)
  THEN
     RETURN OLD;
  ELSE
     RAISE integrity_constraint_violation;
     RETURN NULL;
   END IF;
END;
' LANGUAGE plpgsql;

CREATE TRIGGER T_Delete_Ligne BEFORE DELETE ON LigneDevis
FOR EACH ROW EXECUTE FUNCTION Delete_Ligne();

Je pense avoir testé tous les cas, et ça a l'air de bien fonctionner ...
Mais est-ce à l'épreuve des balles de François ?

J’ai essayé de dézinguer tout ça avec mon char Patton, d’abord avec les 3 mitrailleuses, puis avec le canon de 90 :

Caramba ! ton trigger a tenu le choc et en me faisant des pieds-de-nez qui plus est...

C’est toi le meilleur...

[fsmrel]

Hello !

Je pense que je peux marquer "résolu". Des avis contraires ?

[Paprick]

Bonsoir,

Je pense que je peux marquer "résolu". Des avis contraires ?

Je pense effectivement qu'on a fait le tour du problème .
Et c'était ton 8000ème message François !!!

[SQLpro]

...
Je ne suis pas fin connaisseur de PostgreSQL, mais je suis surpris par l’approche non ensembliste : en gros, le trigger se contente d’effectuer un « open/close » et balance les lignes une par une à la fonction à laquelle il fait appel.

D'autant que l'approche ensembliste peut être détournée par un curseur pour faire du ligne à ligne, mais pas l'inverse !!!
Mais tu n'as pas vu le meilleur si l'approche ligne à ligne de PostGreSQL... Démo :

Code :

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

1
2
3
CREATE TABLE T (C INT PRIMARY KEY);
INSERT INTO T VALUES (1), (2), (3);
UPDATE T SET C = C + 1;

Jolie réponse !!!!

@Fred,

En plus de l’approche archaïque, je subodore que sur le plan des performances ça doit être moins bon qu’avec SQL Server... Qu’en penses-tu ?

Accessoirement, MySQL (create trigger ... for each row) et PostgreSQL même combat (ligne par ligne).

Au plaisir

Ben oui... ça fait des années que je dis que PG est un veau ! À la caisse de retraite des cadres Agic Arco ils ont remplacé DB2 par PostGreSQL... Bilan des opérations la base qui faisait 2 To sous DB2 (compression activée) est passé à près de 10 To sous PG... Devenue tellement lente que embauche massive de dev et DBA pour tenter de résoudre un tonneau des danaïdes ! Un vrai scandale car cela coûte à nos retraites....

A +

[fsmrel]

À la caisse de retraite des cadres Agic Arco ils ont remplacé DB2 par PostGreSQL...

 
Comme dit M’sieur Fernand dans les tontons flingueurs, je cite :
 

« Les c... ça ose tout, et c’est même à ça qu’on les reconnaît... »

 
Cela dit, ça fait 20 ans que c’est l’Agirc-Arrco qui à son tour me donne des sous. En tout cas, comme dit Raoul Volfoni :
 

« Si c’est notre pognon qu’y sont en train d’arroser, les p’ tits comiques, ça va saigner ! »

 
J’ai testé ta requête avec SQL Server et PostgreSQL, effectivement y a pas photo...
 
Ça évoque en moi le souvenir d’un film que j’ai vu vers la fin des années cinquante, « Règlements de comptes à O.K. Corral »…