[Oracle 9i] Triggers sur les updates de tables

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

Zestrellita

Citation:

---

Envoyé par zestrellita

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.

Citation:

---

Envoyé par zestrellita

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.

Citation:

---

Envoyé par zestrellita

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:

---

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

---

Merci pour tes réponses très rapides :D

Citation:

---

Envoyé par plaineR

Citation:

---

Envoyé par zestrellita

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

Citation:

---

Envoyé par plaineR

Citation:

---

Envoyé par zestrellita

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.

Citation:

---

Envoyé par plaineR

Citation:

---

Envoyé par zestrellita

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:

---

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

---

---

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

Citation:

---

Envoyé par zestrellita

Citation:

---

Envoyé par plaineR

Citation:

---

Envoyé par zestrellita

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:

---

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');

---

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

Merci beaucoup pour ton aide PlaineR ! :wink:

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:

---

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:

---

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 ;

---

essayez donc plutôt :

Code:

---

set uusr = USER

---

;-)

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.

:furieux:
Essayez donc !!!

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

Code:

---

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:

---

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

---

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

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

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

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