Triggers Oracle automatisés

Bonjour,

Je travaille sur un script qui me permettrai de créer des triggers automatiques sur certaines des tables de ma base.

Je voudrai un trigger before insert pour les tables voulues qui stocke dans une table (créée au préalable) la valeur de tous les champs de la ligne nouvellement créée.

je ne sais pas si je suis bien clair:

Code:

---

1 2 3 4 5 6 7 8 9

CREATE OR REPLACE TRIGGER TRI_MATABLE BEFORE INSERT ON SA.MATABLE FOR EACH ROW BEGIN IF INSERTING THEN insert into XTRIGTRC (NOMTAB,TYPFIRE,VALEURS) values ('MATABLE','I', :new.CHAMP1|| ';' || :new.CHAMP2|| ';' || :new.CHAMP3); END IF; END; /

---

Ce trigger fonctionne: il me ramène dans VALEURS la liste des champs de ma ligne, séparées par des points virgules. C'est bien le résultat voulu.

Mais je souhaite l'automatiser pour plusieurs tables, et je me vois mal écrire à la main la liste des champs de chaque table...

J'avais donc pensé à la requête suivante:

Code:

---

Select replace(wmsys.wm_concat(':new.' || COLUMN_NAME), ',', ' || '';'' || ') COL from USER_TAB_COLUMNS where TABLE_NAME='MATABLE';

---

Pour récupérer les champs de chaque table, et donc pouvoir faire quelque chose du genre:

Code:

---

1 2 3 4 5 6 7 8 9

CREATE OR REPLACE TRIGGER TRI_MATABLE BEFORE INSERT ON SA.MATABLE FOR EACH ROW BEGIN IF INSERTING THEN insert into XTRIGTRC (NOMTAB,TYPFIRE,VALEURS) values ('MATABLE','I', (Select replace(wmsys.wm_concat(':new.' || COLUMN_NAME), ',', ' || '';'' || ') COL from USER_TAB_COLUMNS where TABLE_NAME='MATABLE')); END IF; END; /

---

Seulement, je n'ai pas le résultat voulu... Le champ VALEURS de la table XTRIGTRC vaut : :new.CHAMP1|| ';' || :new.CHAMP2|| ';' || :new.CHAMP3 (forcément)

Du coup je bloque sur cette requête: comment faire pour récupérer de manière automatique les champs d'une table donnée, à l'intérieur d'un trigger ?

Merci d'avance de votre aide.

Il faut le faire en deux étapes :

1. créez un bloc PL/SQL qui générera le SQL du déclencheur
2. exécutez-le

Bonjour,

J'ai essayé de cette façon

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

select 'CREATE OR REPLACE TRIGGER TRITNR_' || table_name || ' BEFORE INSERT ON SA.' || table_name || ' FOR EACH ROW BEGIN IF INSERTING THEN insert into XTNRTRIGTRC (NOMTAB,TYPFIRE,VALEURS) values (''' || table_name || ''',''I'', ' || (Select replace(wmsys.wm_concat(':new.' || COLUMN_NAME), ',', ' || '';'' || ') COL from USER_TAB_COLUMNS where TABLE_NAME=table_name) || ';' || ' END IF;'|| ' END;'|| ' /' from dba_tables where owner = 'SA' and table_name = 'MA_TABLE' ;

---

Mais la requête tourne pendant très longtemps puis finie par me donner le message d'erreur suivant:

ORA-01652: impossible d'étendre le segment temporaire de 128 dans le tablespace TEMP
ORA-06512: à "WMSYS.WM_CONCAT_IMPL", ligne 22
01652. 00000 - "unable to extend temp segment by %s in tablespace %s"
*Cause: Failed to allocate an extent of the required number of blocks for
a temporary segment in the tablespace indicated.
*Action: Use ALTER TABLESPACE ADD DATAFILE statement to add one or more
files to the tablespace indicated.

Si j'essaye de me passer de "wmsys.wm_concat", et plutôt utiliser LISTAGG, j'ai encore un souci:

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

select 'CREATE OR REPLACE TRIGGER TRITNR_' || table_name || ' BEFORE INSERT ON SA.' || table_name || ' FOR EACH ROW BEGIN IF INSERTING THEN insert into XTNRTRIGTRC (NOMTAB,TYPFIRE,VALEURS) values (''' || table_name || ''',''I'', ' || (SELECT LISTAGG(':new.' || COLUMN_NAME, ' || '';'' || ') WITHIN GROUP (ORDER BY TABLE_NAME, COLUMN_ID) COL FROM USER_TAB_COLUMNS WHERE TABLE_NAME = table_name) || ';' || ' END IF;'|| ' END;'|| ' /' from dba_tables where owner = 'SA' and table_name = 'MA_TABLE' ;

---

ORA-01489: résultat de concaténation de chaîne trop long
01489. 00000 - "result of string concatenation is too long"
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size.

Vous faites un joli produit cartésien entre DBA_TABLES et USER_TAB_COLUMNS.
Changez ce bout de code :

Code:

---

FROM USER_TAB_COLUMNS WHERE TABLE_NAME = table_name

---

Par :

Code:

---

FROM USER_TAB_COLUMNS WHERE USER_TAB_COLUMNS.TABLE_NAME = dba_tables.table_name

---

Vous devriez utiliser USER_TABLES voire ALL_TABLES plutôt que DBA_TABLES.

En effet, cela marche beaucoup mieux comme ça.

Merci beaucoup pour l'aide.

Pourquoi utiliser ALL_TABLES plutôt que DBA_TABLES ?

J'ai parlé un peu trop vite.

Cela fonctionne pour une table ou quelques tables, mais si j'augmente le nombre de tables, j'ai de nouveau mon erreur :

Code:

---

1 2 3 4 5 6 7 8 9 10 11 12 13

select 'CREATE OR REPLACE TRIGGER TRITNR_' || table_name || ' BEFORE INSERT ON SA.' || table_name || ' FOR EACH ROW BEGIN IF INSERTING THEN insert into XTRIGTRC (NOMTAB,TYPFIRE,VALEURS) values (''' || table_name || ''',''I'', ' || (SELECT LISTAGG(':new.' || COLUMN_NAME, ' || '';'' || ') WITHIN GROUP (ORDER BY TABLE_NAME, COLUMN_ID) COL FROM USER_TAB_COLUMNS WHERE USER_TAB_COLUMNS.TABLE_NAME = dba_tables.table_name) || ');' || ' END IF;'|| ' END;'|| ' /' from dba_tables where owner = 'SA' and table_name != 'XTRIGTRC' ;

---

ORA-01489: résultat de concaténation de chaîne trop long
01489. 00000 - "result of string concatenation is too long"
*Cause: String concatenation result is more than the maximum size.
*Action: Make sure that the result is less than the maximum size.

Y a t'il un moyen de faire cette requête sur un nombre de tables important ?

Et bien vous avez une table où il y a beaucoup de colonnes et la concaténation des noms de colonnes dépasse 4000. :

Code:

---

1 2 3 4

select table_name, sum(length(column_name) + 1) as longueur_nom_col from user_tab_columns group by table_name order by longueur_nom_col desc;

---

Effectivement, en découpant selon le nombre de colonnes (max(USER_TAB_COLUMNS.COLUMN_ID)), et en concaténant les morceaux ça passe.

J'arrive à créer mes triggers pour chaque table.

Je vous remercie pour l'aide apportée.