generic triggers in oracle

Question

I am writing a generic oracle trigger. Suppose there are many main tables like PERSON_INFO, EMPLOYEE_INFO, etc. and their corresponding audit tables like PERSON_INFO_AUDIT, EMPLOYEE_INFO_AUDIT. The structure is given below.

PERSON_INFO has columns:-

            ------------------------------------------------
            |                 PERSON_INFO                  |
            ------------------------------------------------
            |  PERSON_ID   |   FIRST_NAME     |  LAST_NAME |
            |   (NUMBER)   |   (VARCHAR2)     |  (VARCHAR2)|
            ------------------------------------------------
            |    1         |   Andrew         |  Jack      |
            ------------------------------------------------

PERSON_INFO_AUDIT has all the columns of PERSON_INFO along with two additional column OPERATIONS and AUDIT_DATE.

The requirement is that if any of the main tables gets updated or any of the rows from main table gets deleted then the old entries of the main table should be inserted in their corresponding audit table.

and then i write an update like:-

UPDATE PERSON_INFO SET FIRST_NAME= 'John';

then the old values for PERSON_INFO should be inserted in the PERSON_INFO_AUDIT Table like below:-

PERSON_INFO_AUDIT should now contains:-

-------------------------------------------------------------------------
|                         PERSON_INFO_AUDIT                              |
-------------------------------------------------------------------------
|  PERSON_ID   |   FIRST_NAME     |  LAST_NAME |  AUDIT_DATE | OPERATIONS|
|   (NUMBER)   |   (VARCHAR2)     |  (VARCHAR2)| (TIMESTAMP) |   (CHAR)  |
-------------------------------------------------------------------------
|    1         |   Andrew         |  Jack      |  30-08-2019 |     U     |
-------------------------------------------------------------------------

Here audit_date is today's date and operations specifies whether the rows in main table got deleted(D) or updated(U). To facilate the above scenario i have written the following trigger function.

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,' || SYSDATE || ',''' || operation || ''');';

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

The query formed is :

INSERT INTO PERSON_INFO_AUDIT(PERSON_ID,FIRST_NAME,LAST_NAME,AUDIT_DATE,OPERATIONS) VALUES(:OLD.PERSON_ID,:OLD.FIRST_NAME,:OLD.LAST_NAME,30-10-19,'U');

However whem i am trying to execute the query using EXECUTE IMMEDIATE final_query then i am getting an error

Answer 1

I have found a generic solution for my answer. My requirement was that their are many base table and their corresponding audit tables. If any of the base table gets updated/ deleted i want the their corresponding entries to get inserted into their audit table. For this i wanted to use a common function that does this job dynamically. I have found a solution and posting one:-

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;

Any comments/suggestion is high appreciated.

Answer 2

Redacting my solution to the compilation problem as the other posted solution is better fitted to the OP's question, but I want the discursive bit to remain.

---

However, we must really question whether generating a dynamic insert statement is the best solution. Firstly you also need to generate the projection of the VALUES clause, otherwise there's no point to the dynamism. If the table structure changes you need to change both sets of columns. Also, the audit table name is fixed (because the name of the table which owns the trigger is fixed). So what's the return on generating the INSERT statement each time? Compare it to the risk (and overhead) of dynamic DML.

The whole idea of "generic triggers" in Oracle is flawed. SQL is a strongly typed language, as is PL/SQL. They work against predefined data structures. A trigger belongs to a table and works with the current structure of the table. So any auditing solution should acknowledge that fact: work with the grain of the RDBMS not against it.

A better approach would be to generate the DDL for the trigger from the data dictionary, and have it execute a static insert statement. Yes you will need to re-generate the trigger code every time you change the structure of the table, but frankly if you change your tables' structures so often this becomes a burden then you have bigger problems in your modelling process that you need to address.

---

One last point. Since Oracle 11.2.0.4 there has been no need whatsoever to write these kind of auditing triggers. Oracle has a capability called Flashback Data Archive (previously badged as Total Recall) which automatically journals whichever tables we would like. Using built-in functionality is always preferable to rolling our own code. So, if you're using a version of Oracle which has this capability you should definitely use it. Find out more.

Answer 3

The reason for this error is:

1. There is semi-colon at the end of dynamic query. Remove that semi-colon.
2. Old values of the table should be written as variable/column name, not as constant (should not write old values in '') in preparing dynamic query.
3. Sysdate should be written properly.

Please find below corrected code:

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;

Hope this helps you:)