Discussion :

[zestrellita]

Bonjour,

Voici ma problématique : on me demande d'étudier la possibilité de tracer, à l'aide de triggers, les insertions ou updates effectués dans toutes les tables d'un schéma (environ 300).

Sachant que le traçage en question consiste à mettre à jour 2 champs (nom et date) appartenant à une des 4 tables "mères" dont héritent la plupart des tables.

Je me pose donc différentes questions :
1) Est-il possible de traiter tout ça à l'aide d'un seul trigger, ou bien n'y a-t-il pas d'autre possibilité que de créer un trigger pour chaque table ?

2) Pour une table donnée, comment spécifier quelle table "mère" doit être mise à jour en s'aidant de la contrainte foreign key entre les 2 tables ?

3) Dans le cas où une table "mère" est directement mise à jour (cas que je teste pour l'instant), je me heurte à l'erreur ORA-4091 de la table mutante. Comment la contourner ? J'ai lu le tutoriel de Pomalaix, très instructif, et tenté d'appliquer la gestion d'exception, et dans ce cas l'exception est bien levée, l'update de ma ligne est fait, mais pas celui de mes 2 champs de "suivi". Comment faire ?

Merci de votre aide

Zestrellita

[plaineR]

1) Est-il possible de traiter tout ça à l'aide d'un seul trigger, ou bien n'y a-t-il pas d'autre possibilité que de créer un trigger pour chaque table ?

Pas à ma connaissance.

2) Pour une table donnée, comment spécifier quelle table "mère" doit être mise à jour en s'aidant de la contrainte foreign key entre les 2 tables ?

Via les table ALL_CONTRAINTS ou USER_CONSTRAINTS et execute immediate.

3) Dans le cas où une table "mère" est directement mise à jour (cas que je teste pour l'instant), je me heurte à l'erreur ORA-4091 de la table mutante. Comment la contourner ? J'ai lu le tutoriel de Pomalaix, très instructif, et tenté d'appliquer la gestion d'exception, et dans ce cas l'exception est bien levée, l'update de ma ligne est fait, mais pas celui de mes 2 champs de "suivi". Comment faire ?

Pourquoi ne faire un simple :

Code :

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

1
2
:new.date_maj := sysdate;
:new.nom = 'MATABLE';

[zestrellita]

Merci pour tes réponses très rapides

1) Est-il possible de traiter tout ça à l'aide d'un seul trigger, ou bien n'y a-t-il pas d'autre possibilité que de créer un trigger pour chaque table ?

Pas à ma connaissance.

C'est ce qui me semblait oui

2) Pour une table donnée, comment spécifier quelle table "mère" doit être mise à jour en s'aidant de la contrainte foreign key entre les 2 tables ?

Via les table ALL_CONTRAINTS ou USER_CONSTRAINTS et execute immediate.

C'est ce que j'ai tenté d'utiliser, mais ces vues n'indiquent pas le nom de la table référencée par la contrainte, seulement le nom de la table sur laquelle porte la contrainte.
Ou bien je dois simplement déduire le nom de la table du nom de la clé primaire référencée (R_CONSTRAINT_NAME) ? Mais dans le cas d'un nom de clé primaire non conforme, ça ne marche pas.

3) Dans le cas où une table "mère" est directement mise à jour (cas que je teste pour l'instant), je me heurte à l'erreur ORA-4091 de la table mutante. Comment la contourner ? J'ai lu le tutoriel de Pomalaix, très instructif, et tenté d'appliquer la gestion d'exception, et dans ce cas l'exception est bien levée, l'update de ma ligne est fait, mais pas celui de mes 2 champs de "suivi". Comment faire ?

Pourquoi ne faire un simple :

Code :

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

1
2
:new.date_maj := sysdate;
:new.nom = 'MATABLE';

En effet, ça marche très bien, merci J'avais cherché trop compliqué.

[plaineR]

2) Pour une table donnée, comment spécifier quelle table "mère" doit être mise à jour en s'aidant de la contrainte foreign key entre les 2 tables ?

Via les table ALL_CONTRAINTS ou USER_CONSTRAINTS et execute immediate.

C'est ce que j'ai tenté d'utiliser, mais ces vues n'indiquent pas le nom de la table référencée par la contrainte, seulement le nom de la table sur laquelle porte la contrainte.
Ou bien je dois simplement déduire le nom de la table du nom de la clé primaire référencée (R_CONSTRAINT_NAME) ? Mais dans le cas d'un nom de clé primaire non conforme, ça ne marche pas.

Si j'ai compris ce que tu voulais faire :

Code :

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

1
2
3
4
5
select table_name 
from user_constraints 
where constraint_name in (select r_constraint_name
                          from user_constraints
                          where table_name = 'TABLE_FILLE');

[zestrellita]

Mais oui c'est tout à fait ça !!! J'étais partie dans un truc compliqué avec des fonctions substr et instr pour retirer le préfixe PK_ de la contrainte...

Merci beaucoup pour ton aide PlaineR !

[zestrellita]

Voilà, j'ai réussi à créer mon ensemble de triggers, par contre je me heurte à un nouveau problème.

En fait, lors de l'update d'une ligne, je dois mettre à jour un champ 'uusr' indiquant le login de la personne qui a fait l'update, ce login étant indiqué par le champ CLIENT_INFO de la vue V$SESSION.
Mais je ne vois pas du tout comment relier cette vue à la ligne :new définissant la ligne mise à jour ?

Voici les 2 types de triggers, créés via une requête select sur l'ensemble des tables du schéma :

Pour une table-fille (mise à jour de la table-mère, préalablement déterminée via la contrainte reliant les 2) :

Code :

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

1
2
3
4
5
6
7
8
create or replace trigger TEST.trace_TABLEFILLE
before insert or update on TEST.TABLEFILLE 
for each row 
begin 
update TABLEMERE 
set uusr = 'LOGIN', udate = sysdate 
where id=:new.id ;
end ;

Pour une table-mère :

Code :

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

1
2
3
4
5
6
7
8
 
create or replace trigger TEST.trace_TABLEMERE
before insert or update on TEST.TABLEMERE
for each row 
begin 
:new.uusr := 'LOGIN' ; 
:new.udate := sysdate ;
end ;

[LeoAnderson]

essayez donc plutôt :

Code :

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

set uusr = USER

;-)

[zestrellita]

Mais où je vais chercher ce USER ??

En fait là mon 'LOGIN' représente le code utilisé par chaque utilisateur pour se connecter au schéma TEST, et indiqué dans le champ CLIENT_INFO de la vue V$SESSION.

[LeoAnderson]

Essayez donc !!!

USER est une "fonction" qui vous retourne le nom de l'utilisateur connecté !

Code :

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

1
2
3
4
5
6
7
declare 
  QUISUISJE VARCHAR2(30);
begin
   QUISUISJE := USER;
   DBMS_OUTPUT.PUT_LINE(QUISUISJE);  
end;
/

Si vous tenez à aller dans v$session :

Code :

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

1
2
SELECT * FROM v$session
  WHERE AudSid = sys_context('USERENV', 'sessionid');

[zestrellita]

Oups, I'm confused Merci, j'essaie de suite mais vous fâchez pas siouplaît

[zestrellita]

Alors en effet ça marche, mais cela me donne le nom du schema (user), à savoir TEST, ce qui n'est pas ce que je veux... vous fâchez pas hein...

[LeoAnderson]

Et la 2nde partie de ma réponse ? ;-)

[zestrellita]

Yesssss, ça marche !
Merci beaucoup LeoAnderson, je n'aurais jamais trouvé