Discussion :

[Aegnor]

Bonjour,

J'ai un souci d'écriture sur un trigger, ma faible connaissance de PL/SQL ne me permettant pas de m'en sortir.
Ma problématique est la suivante : j'ai une table qui regroupe une grosse quantité d'information pour chaque salarié (99 champs dans cette table actuellement). Ces informations proviennent de divers logiciels (RH, PAIE, Services Généraux, Active Directory, Compta, etc...) à travers de multiples interfaces. D'ailleurs, il y a tellement d'interfaces que je suis perdu dans qui met à jour quelle colonne et quand... De plus je me retrouve actuellement avec une mise à jour de plusieurs champs cruciaux sans savoir d'où ça vient. Je souhaite donc tracer chaque mise à jour de ma table. Je souhaite donc créer un trigger qui va me lister toutes les mises à jour de chaque champ.
Comme cette table est une table dont la structure évolue très régulièrement en fonction des nouveaux besoins, je souhaite écrire un trigger "générique" qui va me lister tous les champs de ma table et pour chaque champ vérifier s'il y a eu une mise à jour et inscrire cette msie à jour dans une table temporaire.

Ma table originale s'appelle NIN.
Ma table de log s'appelle NIN_TRACE.
Le IF avec les différents DERMAJ, c'est pour exclure les mises à jour effectuées par des process qui sont sous contrôle et dont je maitrise leur update sur la table NIN.

Mais je coince sur le code car je n'arrive pas à faire la liaison entre le :new (ou :old) et un nom de champ qui serait variable. Voici mon idée de code :

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
 
CREATE OR REPLACE
TRIGGER TRG_NIN_BEFORE_UPDATE 
BEFORE UPDATE ON NIN
FOR EACH ROW
DECLARE
  nom_champ VARCHAR2(40);
  insert_sql VARCHAR2(255);
  cursor curs_table_champ is Select COLUMN_NAME from USER_TAB_COLUMNS where TABLE_NAME = 'NIN' ORDER BY COLUMN_NAME;
BEGIN
  IF (nvl(TO_CHAR(:old.DERMAJ_PPS),' ') = nvl(TO_CHAR(:new.DERMAJ_PPS),' '))
    AND (nvl(TO_CHAR(:old.DERMAJ_APERTURE),' ') = nvl(TO_CHAR(:new.DERMAJ_APERTURE),' ')) 
    AND (nvl(TO_CHAR(:old.DERMAJ_CARTE_AFF),' ') = nvl(TO_CHAR(:new.DERMAJ_CARTE_AFF),' ')) 
    AND (nvl(TO_CHAR(:old.DERMAJ_EXCHANGE),' ') = nvl(TO_CHAR(:new.DERMAJ_EXCHANGE),' ')) 
    AND (nvl(TO_CHAR(:old.DERMAJ_FSP),' ') = nvl(TO_CHAR(:new.DERMAJ_FSP),' ')) 
    AND (nvl(TO_CHAR(:old.DERMAJ_MOBILE),' ') = nvl(TO_CHAR(:new.DERMAJ_MOBILE),' ')) 
    AND (nvl(TO_CHAR(:old.DERMAJ_OCCUPANT),' ') = nvl(TO_CHAR(:new.DERMAJ_OCCUPANT),' ')) 
    AND (nvl(TO_CHAR(:old.DERMAJ_PHOTO),' ') = nvl(TO_CHAR(:new.DERMAJ_PHOTO),' ')) 
    AND (nvl(TO_CHAR(:old.DERMAJ_TEL),' ') = nvl(TO_CHAR(:new.DERMAJ_TEL),' ')) 
    AND (nvl(TO_CHAR(:old.DERMAJ_VEHICULE),' ') = nvl(TO_CHAR(:new.DERMAJ_VEHICULE),' ')) 
    /* AND (nvl(TO_CHAR(:old.DERMAJ_LD),' ') = nvl(TO_CHAR(:new.DERMAJ_LD),' ')) à rajouter lorsque la demande 2540 sera en prod */
    /* AND (nvl(TO_CHAR(:old.DERMAJ_TS_GRI),' ') = nvl(TO_CHAR(:new.DERMAJ_TS_GRI),' ')) à rajouter lorsque la demande 2717 sera en prod */
  THEN
    FOR table_champ in curs_table_champ
    LOOP
      BEGIN
        nom_champ:=table_champ.COLUMN_NAME;
        IF :new.<nom_champ> != :old.<nom_champ> THEN
          insert_sql:= 'insert into NIN_TRACE (NIN, DATE_MODIF, CHAMP, OLD_VALEUR, NEW_VALEUR) values (:c1, :c2, :c3, :c4, :c5)';
          EXECUTE IMMEDIATE insert_sql USING :old.NIN, sysdate, nom_champ, :old.<nom_champ>, :new.<nom_champ>;
        END IF;
      END;
    END LOOP;
   END IF;
END;

Merci d'avance de votre aide.

[mnitu]

Bref, vous pouvez écrire une procédure pl/sql qui pourrait (ré)générer le trigger chaque fois que la table change de définition.

[Aegnor]

En quelque sorte, oui. En tout cas je souhaite m'affranchir de tout problème lié à la modification de la structure de ma table.

[danvoiculet]

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
-- table pour test
CREATE TABLE plch_test (ID NUMBER, nom VARCHAR2(100));
 
-- logging table
CREATE TABLE ddl_log (
operation   VARCHAR2(30),
obj_owner   VARCHAR2(30),
object_name VARCHAR2(30),
sql_text    VARCHAR2(2000),
attempt_by  VARCHAR2(30),
attempt_dt  DATE);
 
-- gâchette sur schéma
CREATE OR REPLACE TRIGGER plch_trg
BEFORE ALTER
ON SCHEMA
DECLARE
 sql_text ora_name_list_t;
 stmt VARCHAR2(2000);
 n BINARY_INTEGER;
BEGIN
 
  n := ora_sql_txt(sql_text);
  FOR i IN 1..n LOOP
    stmt := stmt || sql_text(i);
  END LOOP;
 
  INSERT INTO ddl_log
  SELECT ora_sysevent, ora_dict_obj_owner,
  ora_dict_obj_name, stmt, USER, SYSDATE
  FROM DUAL;
END plch_trg;
 
-- test
ALTER TABLE plch_test RENAME COLUMN nom TO nouveau_nom
 
-- vérifier les résultats
SELECT * FROM   ddl_log

les résultats sont comme ça:

OPERATION OBJ_BROWSER OBJECT_NAME SQL_TEXT ATTEMPT_BY ATTEMPT_DT
ALTER BS PLCH_TEXT ALTER TABLE plch_test RENAME COLUMN nom TO nouveau_nom BS 20-Feb-14 3:14:31 PM