Trigger Oracle Générique de duplication de lignes

**tagada37** · 03/10/2018, 17h01

Bonjour,

J’ai un trigger qui se déclenche lorsque j’ai un update d’une ligne dans ma table RUE.
Je récupère cette même ligne et l’insére dans la même table , mais dans deux autres schémas (par dblink).
En gros un copier coller de la ligne…vers deux autres bases.

ça fonctionne, mais c'est fastidieux de devoir mettre les noms des colonnes en variables.
j'ai plein de triggers à créer sur ce modèle et donc j'aimerai en créer un générique, qui marche avec n'importe quelle table.

mon trigger :

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
create or replace TRIGGER RUE_AFT_INS 
AFTER INSERT ON RUE 
FOR EACH ROW 
DECLARE
    ov_id_rue                   rue.id_rue%TYPE;
    ov_CD_CODCMU_RUE            rue.CD_CODCMU_RUE%TYPE;
    ov_CD_CODDPT_RUE            rue.CD_CODDPT_RUE%TYPE;
    ov_codvoi_rue               rue.cd_codvoi_rue%TYPE;  
    ov_lb_rue                   rue.lb_rue%TYPE;
    ov_ref_ext_rue               rue.ref_ext_rue%TYPE;
BEGIN
   ov_id_rue := :NEW.id_rue;
   Ov_Cd_Codcmu_Rue := :New.Cd_Codcmu_Rue;
   Ov_Cd_Coddpt_Rue := :New.Cd_Coddpt_Rue;  
   ov_codvoi_rue := :NEW.CD_CODVOI_RUE;
   ov_lb_rue := :NEW.lb_rue;   
   ov_ref_ext_rue := :NEW.ref_ext_rue;
 
 
 
   /* Insertion de la rue dans 2 autres bases par dblink */
   INSERT INTO RUE@GENASEE VALUES (ov_id_rue, Ov_Cd_Codcmu_Rue, Ov_Cd_Coddpt_Rue, ov_codvoi_rue,ov_lb_rue, ov_ref_ext_rue, 'O');   
   INSERT INTO RUE@GENINSE VALUES (ov_id_rue, Ov_Cd_Codcmu_Rue, Ov_Cd_Coddpt_Rue, ov_codvoi_rue,ov_lb_rue, ov_ref_ext_rue, 'O');   
 
 
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
END;

merci

**McM** · 03/10/2018, 18h16

D'une part : Attention quand tu ne mets pas les noms de colonne dans les inserts. En cas d'ajout de colonne sur une base ça va planter.. et cas de nom de colonne inversé, suivant le type ça peut passer (donc avec les données incohérentes).

Tu peux générer ton script en plsql, en bouclant sur les colonnes de ta table (USER_TAB_COLUMNS) avec du DBMS_OUPUT

**tagada37** · 05/10/2018, 12h10

ce sont des tables qui n'ont pas vocation à recevoir de nouvelles colonnes. c'est pourquoi j'aimerai plutot utiliser un script générique.

j'ai peut être trouvé une fonction (appelé par le trigger) qui me rapprocherait de ce que je souhaite faire.
pour l'insertion on récupère tout ce que contient le .NEW et on l’insert dans l'autre table.
(attention, c'est du postgre, et je suis en Oracle..il y a sûrement des adaptations à faire...).

Exemple 37.3. Une procédure d'audit par trigger en PL/pgSQL

Cet exemple de trigger nous assure que toute insertion, modification ou suppression d'une ligne dans la table emp est enregistrée dans la table emp_audit. L'heure et le nom de l'utilisateur sont conservées dans la ligne avec le type d'opération réalisé.

Code SQL :

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
 
CREATE TABLE emp (
    nom_employe       text NOT NULL,
    salaire           integer
);
 
CREATE TABLE emp_audit( 
    operation         char(1)   NOT NULL,
    tampon            timestamp NOT NULL,
    id_utilisateur    text      NOT NULL,
    nom_employe       text      NOT NULL,
    salaire           integer
);
 
CREATE OR REPLACE FUNCTION audit_employe() RETURNS TRIGGER AS $emp_audit$
BEGIN
    --
    -- Ajoute une ligne dans emp_audit pour refléter l'opération réalisée
    -- sur emp,
    -- utilise la variable spéciale TG_OP pour cette opération.
    --
    IF (TG_OP = 'DELETE') THEN
        INSERT INTO emp_audit SELECT 'D', now(), user, OLD.*;
        RETURN OLD;
    ELSIF (TG_OP = 'UPDATE') THEN
        INSERT INTO emp_audit SELECT 'U', now(), user, NEW.*;
        RETURN NEW;
    ELSIF (TG_OP = 'INSERT') THEN
        INSERT INTO emp_audit SELECT 'I', now(), user, NEW.*;
        RETURN NEW;
    END IF;
    RETURN NULL; -- le résultat est ignoré car il s'agit d'un trigger AFTER
END;
$emp_audit$ language plpgsql;
 
CREATE TRIGGER emp_audit
    AFTER INSERT OR UPDATE OR DELETE ON emp
    FOR EACH ROW EXECUTE PROCEDURE audit_employe();

**tagada37** · 05/10/2018, 15h55

après recherche, pouvoir insérer toute la ligne (:NEW.*) n'existe pas dans Oracle PL/SQL.
je m'achemine donc sur une solution en indiquant tous les champs.. (:NEW.id_rue, :NEW.Cd_Codcmu_Rue, ...)
Mais j'ai réussi à simplifier ma première monture (nul besoin de déclarer les OLD et NEW en variables si on les manipulent pas).

Code SQL :

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
 
create or replace TRIGGER RUE_AFT_INS
AFTER INSERT ON RUE
FOR EACH ROW
DECLARE
 
BEGIN
 
  --Insertion de la rue dans l'autre base par dblink 
 
   INSERT INTO RUE@GENINSF (ID_RUE, CD_CODCMU_RUE, CD_CODDPT_RUE, CD_CODVOI_RUE, LB_RUE, REF_EXT_RUE, A_CONS_RUE) 
   VALUES (:NEW.id_rue, :NEW.Cd_Codcmu_Rue,:NEW.Cd_Coddpt_Rue, :NEW.cd_codvoi_rue,:NEW.lb_rue, :NEW.ref_ext_rue, 'O');
 
 
EXCEPTION
   WHEN NO_DATA_FOUND
   THEN
      NULL;
END;

**McM** · 05/10/2018, 17h04

Le NO_DATA_FOUND sur de l'insert ne sert à rien.

As-tu vu que j'avais indiqué que pour faire un script générique il fallait utiliser la vue USER_TAB_COLUMNS ?
Soit tu utilises du Spool, soit en dbms_output pour générer le texte de création du trigger

Pseudo code

Code :

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

1
2
3
4
5
6
7
8
9
10
11
dbms_output.put_line('create or replace TRIGGER '|| v_table ||'_AFT_INS');
dbms_output.put_line('AFTER INSERT ON '|| v_table);
...
FOR rc IN (select column_name from user_tab_columns where table_name = v_table order by column_id)
loop
 v_cols := v_cols ||','|| rc.column_name;
 v_vals := v_vals ||', :new.'|| rc.column_name;
end loop;
 
   dbms_output.put_line('INSERT INTO xxxx (' || v_cols ||')';
   dbms_output.put_line('VALUES ('||v_vals ||');');