CODEWITHSUNDEEP
sqloracle-databasetriggersoracle-apex
Alex Sáenz
Alex Sáenz

Reputation: 25

How to get the name of the updated columns in a trigger oracle

I'm working in an Oracle Apex Application where the users get their information about their requested orders. The administrators can edit the information on the table and change the info of the orders and I need to keep a log of which columns where updated. I created a trigger after update but now I need to get the name of the columns that where edited in the Tabular Form.

Upvotes: 1

Views: 4257

Answers (2)

mdsaghi
mdsaghi

Reputation: 1

DESCRIPTION: This code can be used for creating all if conditions for all Columns in the updated table:

here there are 2 Parts in the below solution: Part1: The query for creating the Part2(Trigger-Script)

Part2: in the Script, the changed column is detected. then the "V_COLS_UPDATED" will contain all columns that have been updated. so you can insert this variable in the specified log column!

  

--- TRIGGER(BODY) SCRIPT SAMPLE :

    BEGIN
      -- IF_UPDATING
     IF UPDATING ('EMPLOYEE_ID') THEN V_COLS_UPDATED:=V_COLS_UPDATED || 'EMPLOYEE_ID' || ',' ; END IF;  
     IF UPDATING ('FIRST_NAME') THEN V_COLS_UPDATED:=V_COLS_UPDATED || 'FIRST_NAME' || ',' ; END IF;  
     
     -- IF_UPDATING_AND_CHECK_VALUES
     IF UPDATING ('EMPLOYEE_ID') AND NVL(:NEW.EMPLOYEE_ID,-1) != NVL(:OLD.EMPLOYEE_ID,-1)  THEN V_COLS_UPDATED:=V_COLS_UPDATED || 'EMPLOYEE_ID' || ',' ; END IF;
     IF UPDATING ('FIRST_NAME') AND NVL(:NEW.FIRST_NAME,-1) != NVL(:OLD.FIRST_NAME,-1)  THEN V_COLS_UPDATED:=V_COLS_UPDATED || 'FIRST_NAME' || ',' ; END IF;

END;


------------------------------
--------  Query for generating the "TRIGGER(BODY) SCRIPT"


    select 
    
           'IF UPDATING (''' || C.COLUMN_NAME ||
           ''') THEN V_COLS_UPDATED:=V_COLS_UPDATED || ''' || C.COLUMN_NAME ||
           ''' || '','' ; END IF;' IF_UPDATING,
           
           
           'IF UPDATING (''' || C.COLUMN_NAME || ''') AND NVL(:NEW.' ||
           C.COLUMN_NAME || ',-1) != NVL(:OLD.' || C.COLUMN_NAME ||
           ',-1)  THEN V_COLS_UPDATED:=V_COLS_UPDATED || ''' || C.COLUMN_NAME ||
           ''' || '','' ; END IF;' IF_UPDATING_AND_CHECK_VALUES
           
           
      from all_tab_columns c
     where c.owner='HR'
     and c.TABLE_NAME = 'EMPLOYEES'
     order by c.COLUMN_ID;
--------------------------------------------------------------------

So you can create triggers to Detect and LOG, detail of all changed columns!!!!

Upvotes: 0

Justin Cave
Justin Cave

Reputation: 231691

Within the trigger

IF UPDATING( 'column_name' )

will tell you whether the update statement actually updated a particular column. Depending on how the application is built, however, it may well be updating every column whether or not there was actually a change. If you are trying to account for that, you'd need to compare the :new and :old values. If the columns are non-nullable

IF( :new.column_name != :old.column_name )

If you have to account for null values, it gets a bit more verbose

IF( :new.column_name != :old.column_name or
    (:new.column_name is null and :old.column_name is not null) or
    (:new.column_name is not null and :old.column_name is null) )

Upvotes: 5

Related Questions