Q
Problem solve Get help with specific problems with your technologies, process and projects.

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)"...

?

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.


Notice in the last update it indicated that x and y both were being updated (which they were), but the update didn't change the value (both x and y were 1 prior to the update which set them to 1).

Dig Deeper on Oracle database design and architecture

SearchDataManagement

SearchBusinessAnalytics

SearchSAP

SearchSQLServer

TheServerSide.com

SearchDataCenter

SearchContentManagement

SearchHRSoftware

Close