Discussion :

[MagicThot42]

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 :

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

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 :

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

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 :

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

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.

[Waldar]

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

[MagicThot42]

Bonjour,

J'ai essayé de cette façon

Code :

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

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.

[MagicThot42]

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

Code :

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

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.

[Waldar]

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

Code :

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

FROM USER_TAB_COLUMNS WHERE TABLE_NAME = table_name

Par :

Code :

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

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.

[MagicThot42]

En effet, cela marche beaucoup mieux comme ça.

Merci beaucoup pour l'aide.

Pourquoi utiliser ALL_TABLES plutôt que DBA_TABLES ?

[MagicThot42]

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 :

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

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 ?

[Waldar]

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 :

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

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;

[MagicThot42]

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.