I have a trigger with the following prototype:
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.
Bjarne Anker Maritech Systems AS Norway
You can use the UPDATING( column-name ) syntax to determine if the column is being updated.
See the Parameters trigger-body section in the CREATE TRIGGER documentation for more information.
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.
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.
Yes, this is really helpful.
answered 30 Oct '15, 04:57