Discussion :

[amelo]

Bonjour,

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

Code :

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

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 :

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

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 :

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

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

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 :

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

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

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