Search code examples
sqloracle-databasetriggersoracle-apex

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.

Solution

  • 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) )