How to get old values in Compound Trigger Update in oracle

Question

I searched about my issue but couldn't got any solution yet so i am posting it here. I am using Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production.

I have a Main_Table with suppose 10 Records and I have a Log_Table to log (insert) the old and new values on update of new table.

I am using a compound trigger (to avoid mutating errors) to loop through all columns of "Main_Table" dynamically and get the updating row records by filtering through new . Primarykey (UID)).

I hope i am using Compound trigger right.

I am not using :old and :new as I am looping through all columns dynamically and need to match the column values.

But it is again giving me mutating error:
Error report -
SQL Error: ORA-04091: table Main_Table  is mutating, trigger/function may not see it
ORA-06512: at "Schema.TRG_TEST", line 87
ORA-04088: error during execution of trigger 'Schema.TRG_TEST'
04091. 00000 -  "table %s.%s is mutating, trigger/function may not see it"
*Cause:    A trigger (or a user defined plsql function that is referenced in
           this statement) attempted to look at (or modify) a table that was
           in the middle of being modified by the statement which fired it.
*Action:   Rewrite the trigger (or function) so it does not read that table.


Below is my trigger code:

create or replace TRIGGER TRG_TEST
 FOR INSERT or UPDATE ON Main_Table
COMPOUND TRIGGER
  TYPE NUMBER_TABLE IS TABLE OF NUMBER;
  tblTABLE2_IDS  NUMBER_TABLE;
    TYPE VARCHAR_TABLE IS TABLE OF VARCHAR(2000);
  tblTABLE3_IDS  VARCHAR_TABLE;
   TYPE VARCHAR_TABLE1 IS TABLE OF VARCHAR(2000);
  tblTABLE4_IDS  VARCHAR_TABLE1;

  vcount NUMBER;
  colCount NUMBER;
   colCountAfter NUMBER;
  vvalue VARCHAR2(4000);
  vcolumn VARCHAR2(4000);
  sql1 VARCHAR2(4000);
  dynamicq varchar(4000);
testv varchar(2000);
testv1 varchar(2000);
ssql varchar(2000);
ssql1 varchar(2000);
maxsiteid NUMBER;
newsid varchar(2000);
newstid varchar(2000);
newuid varchar(2000);
 --Executed before DML statement

  BEFORE STATEMENT IS
  BEGIN
 tblTABLE2_IDS := NUMBER_TABLE();
    tblTABLE3_IDS:=  VARCHAR_TABLE();
    tblTABLE4_IDS:=  VARCHAR_TABLE1();
     IF UPDATING THEN
     dbms_output.put_line('Before Statement - In Updating'); 
         --dbms_output.put_line('Before Each Row - In Updating'); 
        -- tblTABLE2_IDS.EXTEND;
      --tblTABLE2_IDS(tblTABLE2_IDS.LAST) := :new.UID;
      END IF;
  END BEFORE STATEMENT;

  --Executed before each row change- :NEW, :OLD are available
     BEFORE EACH ROW IS
     BEGIN
        IF UPDATING THEN
         dbms_output.put_line('Before Each Row - In Updating'); 
         tblTABLE2_IDS.EXTEND;
      tblTABLE2_IDS(tblTABLE2_IDS.LAST) := :new.UID;
     -- FOR columnlist IN
      --(SELECT COLUMN_NAME AS COLUMN_NAME  FROM all_tab_columns  WHERE lower(TABLE_NAME) = 'Main_Table'
      -- AND lower(COLUMN_NAME) NOT IN ( 's_id' ,'msid' ,'st' ,'u_id' ,'db_flag' )) 

      --LOOP
      --colCount:=colCount+1;
      --ssql1:='select '||columnlist.COLUMN_NAME||' from Main_Table where UID='||tblTABLE2_IDS(tblTABLE2_IDS.LAST)||'';
      --dbms_output.put_line(ssql1);
      --execute immediate ssql1 into testv;
      --tblTABLE3_IDS(colCount):=testv;
      --dbms_output.put_line(testv);
      --END LOOP;
           END IF;
     END BEFORE EACH ROW;

 --Executed aftereach row change- :NEW, :OLD are available
  AFTER EACH ROW IS
  BEGIN

    IF UPDATING THEN
         dbms_output.put_line('After Each Row - In Updating'); 
      FOR columnlist IN
      (SELECT COLUMN_NAME AS COLUMN_NAME  FROM all_tab_columns  WHERE lower(TABLE_NAME) = 'Main_Table'
       AND lower(COLUMN_NAME) NOT IN ( 's_id' ,'msid' ,'st' ,'u_id' ,'db_flag' )) 

      LOOP
      colCount:=colCount+1;
      ssql1:='select '||columnlist.COLUMN_NAME||' from Main_Table where UID='||tblTABLE2_IDS(tblTABLE2_IDS.LAST)||'';
      dbms_output.put_line(ssql1);
      execute immediate ssql1 into testv;
      tblTABLE3_IDS(colCount):=testv;
      dbms_output.put_line(testv);
      END LOOP;
           END IF;
  END AFTER EACH ROW;

--Executed after DML statement
  AFTER STATEMENT IS
  BEGIN

      IF UPDATING THEN
            dbms_output.put_line('After Statement - In Updating'); 
          FOR columnlist IN
      (SELECT COLUMN_NAME AS COLUMN_NAME  FROM all_tab_columns  WHERE lower(TABLE_NAME) = 'Main_Table'
       AND lower(COLUMN_NAME) NOT IN ( 's_id' ,'msid' ,'st' ,'u_id' ,'db_flag' )) 

      LOOP
      colCountAfter:=colCountAfter+1;
      dbms_output.put_line('loop started'); 

       ssql1:='select '||columnlist.COLUMN_NAME||' from Main_Table where UID='||tblTABLE2_IDS(tblTABLE2_IDS.LAST)||'';

      execute immediate ssql1 into testv1;
      dbms_output.put_line(testv);
      tblTABLE3_IDS(colCountAfter):=testv1;

       IF ((testv) IS NOT NULL) THEN
       FOR i IN tblTABLE3_IDS.FIRST..tblTABLE2_IDS.LAST LOOP
       dbms_output.put_line('Values No :' ||i||' is ' || tblTABLE3_IDS(i) || ' and ' ||tblTABLE4_IDS(i));
       IF(tblTABLE3_IDS(i)=tblTABLE4_IDS(i)) THEN
         dbms_output.put_line(testv1);


       ELSE
       -- dbms_output.put_line('select :new.'|| columnlist.COLUMN_NAME||' from dual');
        dbms_output.put_line(testv1);           


         INSERT INTO Log_Table
              (
                user_id,  
                log_action,
                log_table_name,
                schema_name,
                log_column_name,
                col_old_val,
                col_new_val,
                ne_type,
                ne_id,
                system_id
              )
              VALUES
              (
                newuid,
                'UPDATE',
                'Main_Table',
                'SCHEMA'
                ,columnlist.COLUMN_NAME
                ,tblTABLE3_IDS(i)
                ,tblTABLE4_IDS(i)
                ,'S'
                ,newstid
                ,newsid
              );
       END IF;
       END LOOP;


       END IF;
      END LOOP;
           END IF;

  END AFTER STATEMENT;
  END TRG_TEST;

Initially i had tried accessing the updating table in "Before Each Row" then i tried accessing it in "After Each Row", same error in both cases.

I am struggling to find a solution to it even after using compound trigger,however i achieved the same for insert.

Can anyone help in how to achieve it. Thanks in Advance.

Please post database edition and version. I assume you reinvite the wheel . There are some Oracle products that do the same. — Evgeniy K., Jul 27 '16 at 13:44
I am using Oracle Database 12c Enterprise Edition Release 12.1.0.1.0 - 64bit Production. — nks, Jul 27 '16 at 13:49
No. Just, no. This idea of generating, compiling and running dynamic SQL *for every single DML statement at runtime* is really awful. If you must use a trigger to track all changes, generate your trigger code for each table individually. You will save yourself a LOT of pain and hassle, believe me. — Jeffrey Kemp, Jul 29 '16 at 06:51

score 0 · Answer 1 · answered Jul 27 '16 at 13:32

0

Usually an after statement row level trigger is sufficient to fill a logging table. Old and new column values are available in the :old and :new pseudo records.

A very basic example:

create or replace trigger my_trigger
   after insert or update on my_table
   for each row
declare

    begin

       if inserting
       then
          insert into logging table
             (id
             ,my_column_old
             ,my_column_new)
          values
             (:new.id
             ,null      -- no old value in case of insert
             ,:new.my_column);
       else
          insert into logging table
             (id
             ,my_column_old
             ,my_column_new)
          values
             (:new.id
             ,:old.my_column
             ,:new.my_column);
       end if;
    end;

answered Jul 27 '16 at 13:32

Rene

10,391
5
33
46

Hi Rene,Thanks for the answer,please look at my edited question.I cant use new and old as i am getting the table column names dynamically. – nks Jul 27 '16 at 13:34
I need to check all column values dynamically first and then match with new.(all_columns) values,if there is a difference then i shall log that difference with old and new values.I dont know the column names at runtime. – nks Jul 27 '16 at 13:38
You can do that without dynamic statements. Just program it out for every column you need to check. Too much calculation in the trigger code itself will slow your application down. – Rene Jul 27 '16 at 13:51
My_Table has almost 100 columns and there are 50 more tables like this.I was looking for a generic solution for a generic trigger code which works for all table in one time.(only thing i needed to change is tablename for everytable) – nks Jul 27 '16 at 13:54
Maybe create a script that generates the trigger code. B.t.w. Oracle let's you check for updates on specific columns. http://stackoverflow.com/questions/11201045/trigger-for-only-changed-values – Rene Jul 27 '16 at 13:55
Yes,But i dont know the column names for which to check update on.that will be known at runtime.Also, I am updating all columns at update but old and new values might be different for 1 or 2 columns.so,check for updates on specific columsn gives me all columns even though their old values are same – nks Jul 27 '16 at 14:22
You can use updating (column) but it will only tell you that the column itself is being updated and not if the value has changed. If you need to determine if the value is changing, you'll still need to code comparison logic for :old and :new values.(which i dont want) – nks Jul 27 '16 at 14:29

score 0 · Answer 2 · answered Jul 27 '16 at 13:55

0

As I understand from one of your comments you want track history of new added column(users can add and drop column) and you want not recompile trigger.

Then you can use Oracle Total Recall

Happily you have Oracle 12c EE(most bugs are fixed).

answered Jul 27 '16 at 13:55

Evgeniy K.

1,137
7
11

Thanks.I see its a new concept which requires understanding;I will look into it,but for now due to some reason i am not looking at implementing this due to time constraint .Currently,A sql trigger help will save my day. – nks Jul 27 '16 at 14:08
Also, i want to track history of any column old and new updated values not the columns itself. – nks Jul 27 '16 at 14:25

How to get old values in Compound Trigger Update in oracle

2 Answers2