Trigger Oracle Générique de duplication de lignes

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:

---

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

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

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:

---

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

---

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:

---

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;

---

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:

---

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

---

Citation:

---

Envoyé par tagada37

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

---

Ce n'est pas possible. Par contre vous pouvez créer un outil pour générer ce code comme indiqué par McM