pb trigger et clause returning

[amelo]

Bonjour,

Alors mon souci est le suivant. J'ai deux tables sur lesquelles j'exécute un trigger :

Code :

1
2
3
4
5
6
CREATE OR REPLACE TRIGGER nom_tgr 
    INSTEAD OF INSERT ON nom_vue 
    BEGIN 
         INSERT INTO tab_a(id_a, nom, prenom) VALUES (id_seq.NEXTVAL, :new.nom, :new.prenom, );
         INSERT INTO tab_b(id_b, id_a) VALUES (:new.id_b, id_seq.NEXTVAL);
END nom_tgr ;

Et à son déclenchement se produit l'erreur suivante :

Code :

1
2
 
ORA-22816: fonction non prise en charge avec la clause RETURNING

J'ai essayé tout un tas de choses comme appliquer ce trigger sur la plus simple des vues imaginables, remplacer la fonction INSERT INTO par un SELECT avec des déclarations de variables... Le résultat est toujours identique.
Et j'avoue ne pas bien comprendre ce qu'est la clause returning.

J'ai retrouvé ce problème expliqué ici, si cela est plus clair.

Voilà en remerciant d'avance ceux qui pourraient éclaircir mon problème.

[Waldar]

En 11gR1, je n'arrive pas à reproduire votre problème.

Objets

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 TABLE tab_a
(
    id_a    number(1)        NOT NULL,
    nom     varchar2(1 char),
    prenom  varchar2(1 char),
    constraint pk_tab_a
      PRIMARY KEY (id_a)
);
-- Table created.
 
CREATE TABLE tab_b
(
    id_b    number(1)        NOT NULL,
    id_a    number(1)        NOT NULL,
    constraint pk_tab_b
      PRIMARY KEY (id_b)             ,
    constraint fk_tab_b_a
      FOREIGN KEY (id_a)
      REFERENCES tab_a (id_a)
);
-- Table created.
 
CREATE OR REPLACE VIEW nom_vue
AS
SELECT a.id_a, b.id_b, a.nom, a.prenom
  FROM tab_a a
       INNER JOIN tab_b b
         ON b.id_a = a.id_a;
-- View created.
 
CREATE sequence id_seq
  start WITH 1
  minvalue 1
  nocycle
  nocache
  noorder;
-- Sequence created.
 
CREATE OR REPLACE TRIGGER nom_tgr
instead of INSERT ON nom_vue
begin
    INSERT INTO tab_a (id_a, nom, prenom) VALUES (id_seq.NEXTVAL, :new.nom, :new.prenom);
    INSERT INTO tab_b (id_b, id_a)        VALUES (:new.id_b, id_seq.currval);
end nom_tgr ;
-- Trigger created.

Vous remarquerez que j'ai utilisé un currval pour la table b et que j'ai supprimé une virgule en trop dans l'insert de la table a.

Données

Code :

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO nom_vue (id_b, nom, prenom) VALUES (1, 'A', 'A');
-- 1 row created.
 
INSERT INTO nom_vue (id_b, nom, prenom) VALUES (2, 'B', 'B');
-- 1 row created.
 
INSERT INTO nom_vue (id_b, nom, prenom) VALUES (3, 'C', 'C');
-- 1 row created.
 
commit;
-- Commit complete.

Résultats

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SELECT * FROM tab_a;
 
 ID_A NOM PRENOM
----- --- ------
    1 A   A
    2 B   B
    3 C   C
 
SELECT * FROM tab_b;
 
 ID_B  ID_A
----- -----
    1     1
    2     2
    3     3
 
SELECT * FROM nom_vue;
 
 ID_A  ID_B NOM PRENOM
----- ----- --- ------
    1     1 A   A
    2     2 B   B
    3     3 C   C

Nettoyage

Code :

1
2
3
4
5
6
7
8
9
10
11
12
13
14
DROP TRIGGER nom_tgr;
-- Trigger dropped.
 
DROP VIEW nom_vue;
-- View dropped.
 
DROP sequence id_seq;
-- Sequence dropped.
 
DROP TABLE tab_b;
-- Table dropped.
 
DROP TABLE tab_a;
-- Table dropped.

[amelo]

Tout d'abord merci.

J'utilise la version 10G.
J'ai remplacé le NEXTVAL par un CURRVAL (ce qui est une très bonne idée) et la virgule était un oubli de l'adaptation du code pour ce message.
J'ai également modifié le code pour la création de ma vue en réalisant un INNER JOIN au lieu d'une clause WHERE au cas où l'erreur vienne de là (bien que, après divers tests, j'ai pu noter que le problème persistait sur une vue n'impliquant qu'une seule table)
...
résultat : la même erreur s'affiche, du coup je me demande si cela vient du code.

[skuatamad]

Le problème doit effectivement venir de l'insert, à priori tu utilises la clause RETURNING pour récupérer id_a :
http://download.oracle.com/docs/cd/E...766/e19999.htm

ORA-22816: unsupported feature with RETURNING clause
Cause: RETURNING clause is currently not supported for object type columns, LONG columns, remote tables, INSERT with subquery, and INSTEAD OF Triggers.
Action: Use separate select statement to get the values.

Ou n'utilise pas de trigger instead of

[amelo]

ok merci de votre aide. Du coup je vais réfléchir à comment contourner le problème.
(Mais je trouvais ca quand même drôlement pratique un trigger instead of !...)

[skuatamad]

Mais tu peux l'utiliser, il suffit de suivre le workaroud d'oracle, utilise un select séparé après ton insert sans clause RETURNING

Tu as probablement une contrainte d'unicité sur laquelle recherchée pour récupérer la PK que tu viens d'insérer. Mais c'est sûr que c'est moins bien qu'utiliser la clause RETURNING

[amelo]

Certes...

utilise un select séparé après ton insert sans clause RETURNING

tu me conseilles de faire ça :

Code :

INSERT INTO T_TEST (ID,COL1) SELECT :new.ID, :new.col1 FROM V_TEST;

Si oui cela ne fonctionne pas non plus.

Et donc d'après ce que je comprends, il est possible d'avoir une fonction qui utilise une clause RETURNING sans que celle ci soit pour autant clairement mentionnée dans le code?

Et dans ce cas je ne comprends pas pourquoi le problème persiste lorsque j'essaye de mettre à jour une simple table avec deux colonnes (ID, COL1) à travers une vue portant sur ces deux colonnes.
Il n'est nullement question ici de clause RETURNING!

Désolée du coup je reviens à la charge, mais je suis perdue.

[skuatamad]

Non je conseillais (ou plutôt Oracle conseille), en reprenant les DDL de Waldar :

Code :

1
2
3
4
5
6
7
8
9
10
11
declare
  l_id_a number;
begin
  INSERT INTO nom_vue (id_b, nom, prenom) VALUES (2, 'B', 'B');
  SELECT id_a INTO l_id_a FROM nom_vue WHERE id_b = 2 AND nom = 'B' AND prenom = 'B';
  dbms_output.put_line('id_a = ' || l_id_a);
end;
/
id_a = 5
 
PL/SQL procedure successfully completed.

Il n'est nullement question ici de clause RETURNING!

Alors je ne connais pas ton code mais l'erreur est explicite et reproductible en couplant une clause returning et un trigger instead of.

Code :

1
2
3
4
5
6
7
8
9
10
11
12
declare
  l_id_a number;
begin
  INSERT INTO nom_vue (id_b, nom, prenom) VALUES (2, 'B', 'B') returning id_a INTO l_id_a;
  dbms_output.put_line('id_a = ' || l_id_a);
end;
/
declare
*
ERROR at line 1:
ORA-22816: unsupported feature WITH RETURNING clause
ORA-06512: at line 4

[amelo]

Merci pour votre aide, mais cette fichue erreur est rudement tenace. Rien n'y fait.
Du coup je me suis arrangée autrement en modifiant mon modèle de données.
C'est moins efficace, mais c'est viable!