Trigger sur une vue pour remplir des clé secondaires

Version imprimable

Bonjour,

Je travaille sous Postgre et je dispose de 2 tables (TableA et TableB) :

Code:

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

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
CREATE TABLE TableB
(
  id_B integer NOT NULL DEFAULT nextval('seq_B'::regclass),
  num_B character varying(10),
  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;

J'ai mis en place une Vue (MaVue), afin de consulter tous les champs de mes 2 tables,
Cette vue comprend également un Trigger (MonTrigger):

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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 MaVue
  OWNER TO sig;
GRANT ALL ON TABLE MaVue TO sig;
 
 
CREATE TRIGGER vue_trg
  INSTEAD OF INSERT OR UPDATE OR DELETE
  ON MaVue
  FOR EACH ROW
  EXECUTE PROCEDURE MonTrigger();

Et voici mon Trigger :

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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
CREATE OR REPLACE FUNCTION MonTrigger()
  RETURNS trigger AS
$BODY$
DECLARE new_id_A BIGINT;
BEGIN
    IF (TG_OP = 'INSERT') THEN
	SELECT nextval('seq_A')INTO new_id_A;
 
        INSERT INTO TableA (geom, num_A) 
               VALUES (NEW.geom, NEW.num_A);
        RETURN NEW;
 
	INSERT INTO TableB (A_id, num_B) 
               VALUES (DEFAULT, 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 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;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION MonTrigger()
  OWNER TO sig;

En l'état quand je saisis dans ma vue, les enregistrements vont remplir uniquement ma TableA et pas la TableB....

Quelqu'un peut-il m'aider?

26/07/2018, 14h08
ced

Bonjour,

Peut-être parce que vous avez mis deux fois RETURN NEW; dans la partie INSERT...

Bonjour ced,

je viens de re-tester en enlevant un RETURN; j'ai donc ceci maintenant dans mon Trigger
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 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 CREATE OR REPLACE FUNCTION MonTrigger() RETURNS trigger AS $BODY$ DECLARE new_id_A BIGINT; BEGIN IF (TG_OP = 'INSERT') THEN SELECT nextval('seq_A')INTO new_id_A; INSERT INTO TableA (geom, num_A) VALUES (NEW.geom, NEW.num_A); INSERT INTO TableB (A_id, num_B) VALUES (DEFAULT, 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 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; END; $BODY$ LANGUAGE plpgsql VOLATILE COST 100; ALTER FUNCTION MonTrigger() OWNER TO sig;
j'ai relancé ma Vue et j'obtiens le message d'erreur suivant :

ERROR: record "new" has no field "new_id_A"
CONTEXT: SQL statement "INSERT INTO MaTableA (id_A, geom, num_A)
VALUES (NEW.new_id_A, NEW.geom, NEW.num_A)"
PL/pgSQL function MonTrigger() line 6 at SQL statement

********** Erreur **********

ERROR: record "new" has no field "new_id_A"
État SQL :42703
Contexte : SQL statement "INSERT INTO MaTableA (id_A, geom, num_A)
VALUES (NEW.new_id_A, NEW.geom, NEW.num_A)"
PL/pgSQL function MonTrigger() line 6 at SQL statement

26/07/2018, 14h24
SIGCMC56

J'ai essayé en enlevant la DECLARE new_id_A, mais j'ai fait cela dans le but de pouvoir permettre la saisis automatique de mes clés secondaires dans ma tableB....
Mais ça ne marche pas non plus
26/07/2018, 14h32
al1_24

C'est bizarre, le code des lignes qui retournent une erreur d'exécution n'apparait pas dans le script de la fonction... 8O

Nouveau Trigger :

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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
 CREATE OR REPLACE FUNCTION MonTrigger()
  RETURNS trigger AS
$BODY$
DECLARE new_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.new_id_A, NEW.geom, NEW.num_A);
 
 
	INSERT INTO TableB (A_id, num_B) 
               VALUES (DEFAULT, 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 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;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION MonTrigger()
  OWNER TO sig;

Et voici mon erreur retournée :

ERROR: record "new" has no field "new_id_A"
CONTEXT: SQL statement "INSERT INTO MaTableA (id_A, geom, num_A)
VALUES (NEW.new_id_A, NEW.geom, NEW.num_A)"
PL/pgSQL function MonTrigger() line 6 at SQL statement

********** Erreur **********

ERROR: record "new" has no field "new_id_A"
État SQL :42703
Contexte : SQL statement "INSERT INTO MaTableA (id_A, geom, num_A)
VALUES (NEW.new_id_A, NEW.geom, NEW.num_A)"
PL/pgSQL function MonTrigger() line 6 at SQL statement

26/07/2018, 14h55
al1_24

La pseudo table NEW possède-telle une colonne nommée new_id_A ?
Si tu veux faire référence à la variable new_id_A déclarée quelques lignes plus tôt, il ne faut pas la préfixer avec NEW..

Je viens de faire qq changements, voici mon nouveau Trigger ou dans l'INSERT j'ai remplacé NEW.id_A par DEFAULT

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
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
CREATE OR REPLACE FUNCTION MonTrigger()
  RETURNS trigger AS
$BODY$
DECLARE id_A BIGINT;
BEGIN
    IF (TG_OP = 'INSERT') THEN SELECT nextval('sep_A') INTO id_A;
		
        INSERT INTO MaTableA (id_A, geom, num_A) 
               VALUES (DEFAULT, NEW.geom, NEW.num_A);
      

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

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

	
        RETURN NEW;
    
     ELSIF (TG_OP = 'DELETE') THEN
        DELETE FROM MaTableA WHERE id_A = OLD.id_A;
	DELETE FROM MaTableB WHERE A_id = OLD.id_A;
	
   	RETURN NULL;
	
 END IF;
END;
$BODY$
  LANGUAGE plpgsql VOLATILE
  COST 100;
ALTER FUNCTION MonTrigger()
  OWNER TO sig;

Et dans ma Vue j'ai enlevé le champ id_A, et j'ai maintenant ceci :

Code:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
CREATE OR REPLACE VIEW MaVue AS 
 SELECT row_number() OVER () AS id_unique,
   MaTableA.geom,
   MaTableA.num_A,
   MaTableB.num_B
 
   FROM MaTableA
   LEFT JOIN MaTableB ON MaTableA.id_A = MaTableB.A_id;
 
 
ALTER TABLE MaVue
  OWNER TO sig;
GRANT ALL ON TABLE MaVue TO sig;
 
 
CREATE TRIGGER vue_trg
  INSTEAD OF INSERT OR UPDATE OR DELETE
  ON MaVue
  FOR EACH ROW
  EXECUTE PROCEDURE MonTrigger();

Maintenant les champs dans ma TableA et TableB se remplissent sauf ma clé secondaire dans ma TableB

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

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
25
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