Forum Stats

  • 3,817,244 Users
  • 2,259,294 Discussions
  • 7,893,711 Comments

Discussions

dynamic :new and :old values in oracle trigger

user9974866
user9974866 Member Posts: 128 Blue Ribbon
edited Jul 22, 2020 2:25AM in SQL & PL/SQL

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;
Tagged:
«1

Answers

  • EdStevens
    EdStevens Member Posts: 28,778 Gold Crown
    edited Jul 21, 2020 10:53AM

    Your posted code is totally unreadable.

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

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    edited Jul 21, 2020 10:57AM
    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
    Mark D Powell Member Posts: 5,914 Blue Diamond
    edited Jul 21, 2020 11:03AM

    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
    user9974866 Member Posts: 128 Blue Ribbon
    edited Jul 21, 2020 11:31AM

    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
    user9974866 Member Posts: 128 Blue Ribbon
    edited Jul 21, 2020 11:33AM

    unfortunately I didn't get any solution or workaround.

  • user9974866
    user9974866 Member Posts: 128 Blue Ribbon
    edited Jul 21, 2020 11:35AM

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

  • Tubby
    Tubby Member Posts: 6,987 Bronze Crown
    edited Jul 21, 2020 11:46AM
    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
    Saubhik Member Posts: 5,803 Gold Crown
    edited Jul 21, 2020 12:01PM

    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
    user9974866 Member Posts: 128 Blue Ribbon
    edited Jul 21, 2020 12:05PM

    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
    user9974866 Member Posts: 128 Blue Ribbon
    edited Jul 21, 2020 12:16PM

    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 ?