Identify updated column in trigger

[bjanker77]

Hi!

I have a trigger with the following prototype:

CREATE OR REPLACE TRIGGER "wua_w729salgshode12_bonusavsetning"
 after update of IkkeBeregnBonus,IkkeAvsettKostnader
 order 12 on MTS.W729SalgsHode
 referencing old as old_row new as new_row
for each row
begin
   <code>
end

This trigger is doing some calculation based on the change of one of two columns, IkkeBeregnBonus and IkkeAvsettKostnader. However, the result of the calculation is slightly different between the two. So the question is: how can I identify which of the two columns that is updated in the trigger? I guess I can check if the value in new_row and old_row is different, but is it at all possible to get the actual name of the updated column? The quick (and dirty) fix is to duplicate the trigger, but I would really like to keep it as one.

Regards,

Bjarne Anker Maritech Systems AS Norway

[bjanker77]

For some reason this ended up as a double post.

[MarkCulp]

FYI: I deleted the duplicate question.

[MarkCulp]

You can use the UPDATING( column-name ) syntax to determine if the column is being updated.

Example.

CREATE OR REPLACE TRIGGER "wua_w729salgshode12_bonusavsetning"
 after update of IkkeBeregnBonus,IkkeAvsettKostnader
 order 12 on MTS.W729SalgsHode
 referencing old as old_row new as new_row
for each row
begin
    if UPDATING( 'IkkeBeregnBonus' ) then
       <code>
    end if;
    if UPDATING( 'IkkeAvsettKostnader' ) then
       <code>
    end if;
end

See the Parameters trigger-body section in the CREATE TRIGGER documentation for more information.

[VolkerBarth]

Note, when using the UPDATING condition, the column name must bei supplied as a string, not an identifier, so I guess it should read

...
if UPDATING('IkkeBeregnBonus') then
...

[MarkCulp]

I've updated my example.

[VolkerBarth]

I guess you can use the "Trigger operation conditions" to know which column(s) have been modified, i.e. something like (the untested):

begin
   if update(IkkeBeregnBonus) then
      -- calculation A
   elseif update (IkkeAvsettKostnader) then
      -- calculation B
   end if;
end;

Apparently, I don't know what to do if both columns have been modified in the same statement.

I guess (yes, that's another one) the "trigger operation conditions" are met if the according column has been SET explicitly in the according UPDATE statement and the AFTER ROW trigger is fired, so I guess it does not necessarily mean the accordung column's value has been changed. Correction: No, the test shows that an UPDATE/UPDATING condition is only fulfilled when the value of the according column has been changed. It does not matter whether the column was contained in the SET clause of the UPDATE statement, the condition is also fulfilled if the column is changed "under the covers", say via a before trigger. (However, that particular trigger would only be triggered if for one of these columns the according values has been changed at all because it is an AFTER UPDATE trigger.)

FWIW, here's some test code in order to get rid of those guessings - and yes, it works as expected:

drop table Test;
create table Test(
   pk int primary key,
   col2 int not null,
   col3 int not null,
   col4 int null
);

create or replace trigger TUA_Test
   after update of col2, col3 on Test
   referencing old as old_row new as new_row
for each row
begin
   message 'TUA_Test fired for row with pk ' || new_row.pk;
   if update(col2) then -- syntax variant A
      message '   TUA_Test fired because col2 was updated from ' || old_row.col2 || ' to ' || new_row.col2 || '.';
   endif;
   if updating('col3') then -- syntax variant B
      message '   TUA_Test fired because col3 was updated from ' || old_row.col3 || ' to ' || new_row.col3 || '.';
   endif;
end;

insert Test values (1, 1, 1, null);
insert Test values (2, 2, 2, null);
select * from Test;

update Test set col2 = 11; -- 1st condition is met for both rows
update Test set col3 = 22 where pk = 1; -- 2nd condition is met
update Test set col4 = 44 where pk = 2; -- trigger is not fired
update Test set col2 = col3 where pk = 2; -- 1st condition is met
update Test set col2 = 22, col3 = 44 where pk = 2; -- both conditions are met
update Test set col2 = 22, col3 = 44 where pk = 2; -- trigger is not called as no values have changed
update Test set col2 = 33, col3 = 44 where pk = 2; -- only 2nd condition met as col3 is left unchanged

-- Further tests with an additional before trigger:
create or replace trigger TUB_Test
   before update of col3 on Test
   referencing old as old_row new as new_row
for each row
begin
   message 'TUB_Test fired for row with pk ' || new_row.pk;
   if new_row.col3 = 10 then
      set new_row.col2 = old_row.col2 * 2;
   end if;
end;

update Test set col3 = 10 where pk = 1;
-- messages:
/*
TUB_Test fired for row with pk 1
TUA_Test fired for row with pk 1
TUA_Test fired because col2 was updated from 11 to 22. -- condition is met because value has been changed within the before trigger
TUA_Test fired because col3 was updated from 22 to 10.
*/
update Test set col3 = 10 where pk = 1; -- only 1st condition is met as col3 stays unchanged.

[bjanker77]

Yes, this is really helpful.

Thanks guys!

Bjarne