Forums PostgreSQL.fr

M-Pauline

Membre

syntaxe trigger pour remplir une clé secondaire

Bonjour,

je n'arrive pas à écrire correctement un trigger afin de permettre la saisi automatique d'un champ (clé secondaire) dans une table :

Je dispose de 2 tables :
TABLEA
id_A (clé primaire)
num_A

TABLEB
id_B
num_B
A_id (clé secondaire reliée à id_A de la TABLEA)

J'ai fais une Vue me permettant de récupérer les champs des 2 tables et qui fais appel à un Trigger :

CREATE OR REPLACE VIEW MaVue AS 
 SELECT row_number() OVER () AS id_unique,
   TableA.id_A,
   TableA.num_A,
   TableB.num_B

   FROM TableA
   LEFT JOIN TableB ON TableA.id_A = TableB.A_id;

ALTER TABLE TableA
  OWNER TO sig;

CREATE TRIGGER MonTrigger
  INSTEAD OF INSERT OR UPDATE OR DELETE
  ON MaVue
  FOR EACH ROW
  EXECUTE PROCEDURE MonTrigger();

Et voici mon Trigger :

CREATE OR REPLACE FUNCTION MonTrigger()
  RETURNS trigger AS
$BODY$
DECLARE id_A BIGINT;
BEGIN
    IF (TG_OP = 'INSERT') THEN SELECT nextval('seq_id_A') INTO id_A;
		
        INSERT INTO TableA (id_A, num_A) 
               VALUES (DEFAULT, NEW.num_A);
      

	INSERT INTO TableB (A_id, num_B) 
               VALUES (NEW.id_A, NEW.num_B);
     
        RETURN NEW;
        
    ELSIF (TG_OP = 'UPDATE') THEN
           
       	UPDATE TableA SET (num_A) 
  
                 = (NEW.num_A)
        WHERE id_A = NEW.id_A;

	UPDATE TableB SET (num_B) 
  
                 = (NEW.num_B)
        WHERE id_A = NEW.id_A;

	RETURN NEW;
    
     ELSIF (TG_OP = 'DELETE') THEN
        DELETE FROM TableA WHERE id_A = OLD.id_A;
	DELETE FROM TableB WHERE A_id = OLD.id_A;
	
   	RETURN NULL;
END IF;

Avec ceci quand j'ajoute un nouvel enregistrement par ma vue, tous mes champs de la TableA se remplissent et seulement les champs id_B et num_B de ma tableB, pas le champ A_id (clé secondaire) de la tableB...

Quelqu'un pour m'aider?

Dernière modification par M-Pauline (27/07/2018 14:13:54)

jmarsac

Membre

Re : syntaxe trigger pour remplir une clé secondaire

Bonjour,

Lors d'une insertion NEW.id_A est nul.

Il faut remplacer

    IF (TG_OP = 'INSERT') THEN SELECT nextval('seq_id_A') INTO id_A;
       
        INSERT INTO TableA (id_A, num_A)
               VALUES (DEFAULT, NEW.num_A);

par

    IF (TG_OP = 'INSERT') THEN SELECT nextval('seq_id_A') INTO NEW.id_A;
       
        INSERT INTO TableA (id_A, num_A)
               VALUES (NEW.id_A, NEW.num_A);

sinon NEW.id_A reste nul et l'utilisation de DEFAULT génère une nouvelle valeur de la séquence qui est utilisée dans tableA

mais la valeur saisie sera ignorée si vous indiquez la valeur de "id_A" dans la requête INSERT (ce qui ne devrait jamais être le cas puisque vous utilisez une séquence)

j'utiliserais plutôt deux triggers distincts :

-le premier déclenché par les modifications sur la vue qui met à jour tableA
- le second déclenché par les modifications sur tableA qui met à jour tableB

Dernière modification par jmarsac (27/07/2018 13:48:21)

M-Pauline

Membre

Re : syntaxe trigger pour remplir une clé secondaire

Merci de ton aide jmarsac,

je viens de faire les modifications et tout fonctionne parfaitement comme cela, tout se remplit correctement dans les 2 tables !!!

Encore merci

M-Pauline

Membre

Re : syntaxe trigger pour remplir une clé secondaire

Tout fonctionne correctement pour l'INSERTION de nouveaux enregistrements, par contre je ne peux pas modifier, je me retrouve avec ce message d'erreur :

Impossible de valider les changements pour la couche MaVue

Erreurs : ERREUR : 1 modification de valeur d'attribut n'a pas été effectué.
  
Erreur du fournisseur de données :
      Erreur PostGIS lors de la modification d'attribut : ERROR:  column reference "id_A" is ambiguous
    LINE 4:         WHERE id_A = NEW.id_A
                          ^
    DETAIL:  It could refer to either a PL/pgSQL variable or a table column.
    QUERY:  UPDATE TableA SET (geom, num_A) 
      
                     = (NEW.geom, NEW.num_A)
            WHERE id_A = NEW.id_A
    CONTEXT:  PL/pgSQL function MonTrigger() line 20 at SQL statement

J'ai rajouté dans mes 2 Tables les contraintes suivantes :

CREATE TABLE TableA
(
  id_A integer NOT NULL DEFAULT nextval('seq_A'::regclass),
  geom geometry(Point,2154),
  num_A character varying(10),
  CONSTRAINT pk_id_A_tot PRIMARY KEY (id_A),
  CONSTRAINT enforce_geotype_geom CHECK (st_geometrytype(geom) = 'ST_Point'::text OR geom IS NULL),
  CONSTRAINT enforce_srid_geom CHECK (st_srid(geom) = 2154)
)
WITH (
  OIDS=TRUE
);
ALTER TABLE TableA
  OWNER TO sig;
GRANT ALL ON TABLE TableA TO sig;

CREATE TABLE TableB
(
  id_B integer NOT NULL DEFAULT nextval('seq_B'::regclass),
  num_B character varying(4),
  A_id integer,
  CONSTRAINT pk_id_B PRIMARY KEY (id_B),
  CONSTRAINT fk_B FOREIGN KEY (A_id)
      REFERENCES TableA (id_A) MATCH SIMPLE
      ON UPDATE CASCADE ON DELETE CASCADE
)
WITH (
  OIDS=TRUE
);
ALTER TABLE TableB
  OWNER TO sig;
GRANT ALL ON TABLE TableB TO sig;
;

Mais je ne peux tjs pas valider une modifications sur une enregistrements

Dernière modification par M-Pauline (27/07/2018 14:15:26)

M-Pauline

Membre

Re : syntaxe trigger pour remplir une clé secondaire

Je suis toujours en galère pour faire fonctionner mon Trigger pour qu'il puisse permettre la Modification et la Suppression.
Actuellement seul l'Insertion fonctionne correctement....

CREATE OR REPLACE FUNCTION MonTrigger()
  RETURNS trigger AS
$BODY$
DECLARE id_A BIGINT;
BEGIN
    IF (TG_OP = 'INSERT') THEN SELECT nextval('seq_A') INTO NEW.id_A;
		
        INSERT INTO TableA (id_A, geom, num_A) 
               VALUES (NEW.id_A, NEW.geom, NEW.num_A);
      

	INSERT INTO TableB (A_id, num_B) 
               VALUES (NEW.id_A, NEW.num_B);
        RETURN NEW;
        
    ELSIF (TG_OP = 'UPDATE') THEN
           
       	UPDATE TableA SET (geom, num_A) 
  
                 = (NEW.geom, NEW.num_A)
        WHERE id_A = NEW.id_A;

	UPDATE TableB SET (num_B) 
  
                 = (NEW.num_B)
        WHERE A_id = NEW.id_A;

	        RETURN NEW;
    
     ELSIF (TG_OP = 'DELETE') THEN
        DELETE FROM TableA WHERE id_A = OLD.id_A;
	DELETE FROM TableB WHERE A_id = OLD.id_A;
	
   	RETURN NULL;
	
 END IF;
END;
$BODY$

Dernière modification par M-Pauline (31/07/2018 09:51:19)

gleu

Administrateur

Re : syntaxe trigger pour remplir une clé secondaire

L'erreur est assez claire : "ERROR:  column reference "id_A" is ambiguous". Vous devez donc indiquer que id_A vient de la table TableA dans la première requête et TableB dans la deuxième.

---

Guillaume.

M-Pauline

Membre

Re : syntaxe trigger pour remplir une clé secondaire

Bonjour gleu,

" Vous devez donc indiquer que id_A vient de la table TableA dans la première requête et TableB dans la deuxième."

C'est bien ce que j'essaye de faire, mais je n'y arrive pas.

Cette partie est censée dire que " id_A vient de la table TableA"

UPDATE TableA SET (geom, num_A)
 
                 = (NEW.geom, NEW.num_A)
        WHERE id_A = NEW.id_A;

Et celle-ci que " id_A vient de la table TableB" car A_id est ma clé secondaire qui est égale au id_A de la TableA.

UPDATE TableB SET (num_B)
 
                 = (NEW.num_B)
        WHERE A_id = NEW.id_A;

            RETURN NEW;

Même chose dans le DELETE...

M-Pauline

Membre

Re : syntaxe trigger pour remplir une clé secondaire

Je viens de résoudre mon soucis en écrivant mon trigger comme ceci dans la partie UPDATE ET DELETE:

   

 ELSIF (TG_OP = 'UPDATE') THEN
           
       	UPDATE TableA SET (geom, num_A) 
  
                 = (NEW.geom, NEW.num_A) 
        WHERE TableA.id_A = NEW.id_A ;
        

	UPDATE TableB SET (num_B) 
  
                 = (NEW.num_B)
        WHERE TableB.A_id = NEW.id_A ;

	
        RETURN NEW;
    
     ELSIF (TG_OP = 'DELETE') THEN
        DELETE FROM TableA WHERE TableA.id_A = OLD.id_A;
	DELETE FROM TableB WHERE TableB.A_id = OLD.id_A;
	
   	RETURN NULL;
	
 END IF;
END;
$BODY$

Merci à tous pour votre aide