Using trigger predicate UPDATING on a column
Can I use the trigger predicate "updating" on a column? Can I say inside a trigger "if updating (column_name)"...
Continue Reading This Article
Enjoy this article as well as all of our content, including E-Guides, news, tips and more.
?
It works, but it won't tell you if the value changed, only that the column is being updated (even if the update doesn't change the value). Many applications update all of the columns in a table -- setting them to their old value, rather then trying to figure out exactly which columns have been updated by the client. For example, Oracle Forms does this by default.
Let's look at a simple example:
SQL> create table test ( x int, y int, z int ); Table created. SQL> SQL> create or replace trigger test_trigger 2 after update of x,y on test 3 for each row 4 begin 5 if updating( 'X' ) then 6 dbms_output.put_line( 'X is being updated' ); 7 end if; 8 9 if updating( 'Y' ) then 10 dbms_output.put_line( 'Y is being updated' ); 11 end if; 12 end; 13 / Trigger created. SQL> insert into test values ( 0, 0, 0 ); 1 row created. SQL> SQL> select * from test ; X Y Z ---------- ---------- ---------- 0 0 0 SQL> SQL> update test set x = 1; X is being updated 1 row updated. SQL> SQL> update test set y = 1; Y is being updated 1 row updated. SQL> SQL> update test set x=1,y=1; X is being updated Y is being updated 1 row updated. SQL> SQL> rollback; Rollback complete. SQL>
So, the answer is yes. You can use updating (column) but it will only tell you that the column itself is being updated and not if the value has changed. If you need to determine if the value is changing, you'll still need to code comparison logic for :old and :new values.