dynamic :new and :old values in oracle trigger

user9974866

hi, I have to track the data changes in a single audit table test_audit which can be used in other triggers to track the changes in another tables as well. I have tried this code but stuck in populating old and new values . Any help on this is much appreciated.

create table test (ID NUMBER , ENAME VARCHAR2(10) , LOCATION VARCHAR2(10)); create or replace trigger my_trigger     after update on TEST     for each row     declare       ACTION VARCHAR2(10);     begin         IF DELETING THEN           ACTION := 'DELETE';         ELSIF UPDATING THEN           ACTION := 'UPDATE';         END IF;                       for i in (select column_name,table_name from all_tab_columns                 where table_name = 'TEST'                 )       loop         if updating(i.column_name) then           INSERT           INTO TEST_AUDIT             (                 TABLE_NAME,                 COLUMN_NAME,                 OLD_VALUE,                 NEW_VALUE,                 ACTION,                 UPDATED_BY,                 UPDATED_DT                         )             VALUES             (                  i.table_name,                 i.column_name,             --  :old.column_name,--- how to get old and new values?               --  :new.column_name,---                 ACTION,                      USER,               SYSDATE             );       end if;       end loop;           end;

EdStevens

Your posted code is totally unreadable.

I thought you got some very good answers when you asked the same question in Stack Overflow .

Tubby

user9974866 wrote:hi, I have to track the data changes in a single audit table test_audit which can be used in other triggers to track the changes in another tables as well. I have tried this code but stuck in populating old and new values . Any help on this is much appreciated.create table test (ID NUMBER , ENAME VARCHAR2(10) , LOCATION VARCHAR2(10)); create or replace trigger my_trigger after update on TEST for each row declare ACTION VARCHAR2(10); begin IF DELETING THEN ACTION := 'DELETE'; ELSIF UPDATING THEN ACTION := 'UPDATE'; END IF; for i in (select column_name,table_name from all_tab_columns where table_name = 'TEST' ) loop if updating(i.column_name) then INSERT INTO TEST_AUDIT ( TABLE_NAME, COLUMN_NAME, OLD_VALUE, NEW_VALUE, ACTION, UPDATED_BY, UPDATED_DT ) VALUES ( i.table_name, i.column_name, -- :old.column_name,--- how to get old and new values? -- :new.column_name,--- ACTION, USER, SYSDATE ); end if; end loop; end;Message was edited by: user9974866

You can't (and shouldn't) do what you are trying to do.

https://blogs.oracle.com/oraclemagazine/a-fresh-look-at-auditing-row-changes

Would be something good to read, if you need to stick with triggers on individual tables like you've posted, don't try to make it dynamic, make the modifications of the trigger part of the SDLC for the table (you add a column, you update the trigger, or have dynamic code that regenerates the trigger at worst).

Cheers,

Mark D Powell

user9974866, Ed is correct the post is unreadable.  You should format your posts so forum readers can easily read the text.  You do not actually state a clear question either in that you do not describe what problem you are having referencing :old and :new values.

- -

Always identify the full version of Oracle in use on any post.

- -

HTH -- Mark D Powell --

user9974866

If there are  1 or 2 tables I would be happy to do that for each table and columns since there are many so It is going to be tedious task to create and maintain multiple audit tables that is the reason I was looking out for this approach but couldn't find the relevant answer anywhere . I think there is no workaround for this . Thanks for reply.

user9974866

unfortunately I didn't get any solution or workaround.

user9974866

I tried to format the code but its coming in a straight line.

Tubby

user9974866 wrote:If there are 1 or 2 tables I would be happy to do that for each table and columns since there are many so It is going to be tedious task to create and maintain multiple audit tables that is the reason I was looking out for this approach but couldn't find the relevant answer anywhere . I think there is no workaround for this . Thanks for reply.

You can write code to write code if you have that many tables (scripts to generate the trigger code).

Or adopt an approach like this

https://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:59412348055

Cheers,

Saubhik

Here is the formatted code for you:

create table test (ID NUMBER , ENAME VARCHAR2(10) , LOCATION VARCHAR2(10));

create or replace trigger my_trigger    

after update on TEST     for each row    

declare      

   ACTION VARCHAR2(10);    

begin        

  IF DELETING THEN          

    ACTION := 'DELETE';        

  ELSIF UPDATING THEN          

    ACTION := 'UPDATE';        

  END IF;                      

  for i in (select column_name,table_name from all_tab_columns  where table_name = 'TEST'  )  loop        

    if updating(i.column_name) then          

    INSERT  INTO TEST_AUDIT  ( TABLE_NAME, COLUMN_NAME,   OLD_VALUE,  NEW_VALUE,   ACTION,  UPDATED_BY,  UPDATED_DT                         )

              VALUES (  i.table_name, i.column_name,   --  :old.column_name,--- how to get old and new values? 

                                                                   --  :new.column_name,--- 

                                     ACTION, USER,SYSDATE);      

     end if;      

   end loop;         

end; 

Now, What exactly you want? Why not enable AUDIT facility for these "small" number of tables? To tell you upfront updating(i.column_name) doesn't work.

user9974866

Thanks Tubby , I have already checked this , Wondering it was 20 year old thread still the discussion is going onto  this topic and there is no concrete solution to this in 20 years except that oracle has introduced in built auditing in FDA.

user9974866

Thanks Saubhik for formatting the code. I cant enable AUDIT  as there are many big tables also for auditing I just mentioned a small one moreover FDA has its own limitation. For updating(i.column_name) what issue do you see ?