déclencheurs génériques dans oracle

Rédaction de ma solution au problème de compilation comme l'autre solution publiée est mieux adaptée à la question du PO, mais je veux le morceau discursif à rester.

Cependant, nous devons vraiment nous demander si la génération d'une instruction d'insertion dynamique est la meilleure solution. Tout d'abord, vous devez également générer la projection de la clause VALUES, sinon le dynamisme ne sert à rien. Si la structure de la table change, vous devez modifier les deux ensembles de colonnes. De plus, le nom de la table d'audit est fixe (car le nom de la table qui possède le déclencheur est fixe). Alors, quel est le retour sur la génération de l'instruction INSERT à chaque fois? Comparez-le au risque (et à la surcharge) du DML dynamique.

L'idée même des "déclencheurs génériques" dans Oracle est erronée. SQL est un langage fortement typé, tout comme PL / SQL. Ils fonctionnent avec des structures de données prédéfinies. Un déclencheur appartient à une table et fonctionne avec la structure actuelle de la table. Toute solution d'audit doit donc reconnaître ce fait: travailler avec le grain du SGBDR et non contre lui.

Une meilleure approche serait de générer le DDL pour le déclencheur à partir du dictionnaire de données, et de lui faire exécuter une instruction d'insertion statique. Oui, vous devrez régénérer le code de déclenchement chaque fois que vous modifiez la structure de la table, mais franchement, si vous changez si souvent les structures de vos tables, cela devient un fardeau, vous avez de plus gros problèmes dans votre processus de modélisation que vous devez résoudre. .

Un dernier point. Depuis Oracle 11.2.0.4, il n’a été nul besoin du tout d’écrire ce type de déclencheurs d’audit. Oracle a une capacité appelée Flashback Data Archive (précédemment appelée Total Recall) qui journalise automatiquement les tables que nous souhaitons. L'utilisation de fonctionnalités intégrées est toujours préférable à la diffusion de notre propre code. Donc, si vous utilisez une version d'Oracle qui a cette capacité, vous devez absolument l'utiliser. En savoir plus .

La raison de cette erreur est:

1. Il y a un point-virgule à la fin de la requête dynamique. Supprimez ce point-virgule.
2. Les anciennes valeurs de la table doivent être écrites sous forme de nom de variable / colonne et non de constante (ne doit pas écrire d'anciennes valeurs dans '') lors de la préparation d'une requête dynamique.
3. Sysdate doit être écrit correctement.

Veuillez trouver ci-dessous le code corrigé:

create or replace TRIGGER trig_PERSON_INFO_deleteupdate
after UPDATE OR DELETE 
ON PERSON_INFO
FOR EACH ROW
DECLARE

 base_table_name clob;
 audit_table_name clob;
 base_table_cols_in_string clob;
 audit_table_cols_in_string clob;
 operation char;
 final_query clob;      
BEGIN

 base_table_name:= 'PERSON_INFO';
 audit_table_name := base_table_name || '_AUDIT';

 IF UPDATING THEN 
      operation:= 'U';
 ELSE
      operation:= 'D';
 END IF;

 SELECT LISTAGG(COLUMN_NAME, ',') WITHIN GROUP (ORDER BY column_id)
 INTO base_table_cols_in_string
 FROM ALL_TAB_COLUMNS
 WHERE TABLE_NAME= 'PERSON_INFO';

 audit_table_cols_in_string:= base_table_cols_in_string || ',AUDIT_DATE,OPERATIONS';

 final_query:= 'INSERT INTO ' ||  audit_table_name || '(' || audit_table_cols_in_string 
    || ') VALUES(''' || :OLD.PERSON_ID || ''',''' || :OLD.FIRST_NAME || ''',''' || :OLD.LAST_NAME 
    || ''',date ''' || to_char(SYSDATE,'yyyy-mm-dd)' || ''',''' || operation || ''')';

 dbms_output.put_line(final_query); 
 EXECUTE IMMEDIATE final_query;
END;

J'espère que cela vous aidera :)

J'ai trouvé une solution générique pour ma réponse. Mon exigence était qu'il existe de nombreuses tables de base et leurs tables d'audit correspondantes. Si l'une des tables de base est mise à jour / supprimée, je veux que leurs entrées correspondantes soient insérées dans leur table d'audit. Pour cela, je voulais utiliser une fonction commune qui effectue ce travail de manière dynamique. J'ai trouvé une solution et j'en ai posté une: -

CREATE OR REPLACE procedure IR_DEV.audit_trigger(main_table_name varchar2)
AS
    audit_table_name clob;
    main_table_col_list clob;
    trig_struct clob;
BEGIN

audit_table_name := main_table_name || '_AUDIT';

select LISTAGG(COLUMN_NAME,',') WITHIN GROUP(ORDER BY column_id)
into main_table_col_list
from COLS where table_name=upper(main_table_name);

trig_struct:='CREATE or REPLACE TRIGGER trig_'||main_table_name ||'_deleteupdate'||chr(10)
             ||'AFTER UPDATE OR DELETE ON '|| main_table_name||chr(10)  
             ||'FOR EACH ROW'||chr(10)
             ||'DECLARE'||chr(10)
             ||'   opt varchar2(1);'||chr(10)
             ||'BEGIN'||chr(10)
             ||'   IF UPDATING THEN'||chr(10) 
             ||'       opt:=''U'';'||chr(10)
             ||'   ELSE'||chr(10)    
             ||'       opt:=''D'';'||chr(10)
             ||'   END IF;'||chr(10)||chr(10)

             ||'   INSERT INTO ' || audit_table_name || ' ('||main_table_col_list||',audit_date,operations )'||chr(10)
             ||'    VALUES ('||':old.'||REPLACE(main_table_col_list,',',',:old.')||',sysdate,opt);'||chr(10)||chr(10)

             ||'END;';

           dbms_output.put_line(trig_struct); 
           execute immediate trig_struct;        
END;

Tout commentaire / suggestion est très apprécié.