Re: INSTEAD OF UPDATE triggers (Oracle8)

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Thu, 02 Sep 1999 11:45:58 GMT
Message-ID: <37d3611a.55867322@newshost.us.oracle.com>

A copy of this was sent to Andy Marden <amarden_at_altavista.net> (if that email address didn't require changing) On Thu, 02 Sep 1999 09:33:02 +0100, you wrote:

>I'm creating an INSTEAD OF UPDATE trigger on a view in Oracle8.
>Essentially the view is a UNION ALL of two tables, and the trigger is
>able to check the primary key of what's being updated and update the
>correct underlying table.
>
>To accomplish this, I've needed to specify the primary key columns in
>the update statement itself (so the trigger has something to check the
>underlying tables with).
>

why? the trigger always gets the entire :old and :new record in an update -- you need not update the primary key for this...

>The trigger is written to be nice and generic so that any number columns
>can be updated through the view. The trouble is when columns are updated
>to NULL. The :old values are always NULL in this type of trigger, and
>the :new values for columns that are not specified in the update
>statement are also NULL. This means that there seems to be no way that
>the trigger can tell if a column is mentioned in the update statement
>and set to NULL, or if the column is not being updated.
>

I also don't understand why you are inspecting the columns. You know the row has been updated so wouldn't it just look like this: tkyte_at_8.0> drop table negative_keys;

Table dropped.

tkyte_at_8.0> create table negative_keys( x int primary key, y int, z date );

Table created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> drop table positive_keys;

Table dropped.

tkyte_at_8.0> create table positive_keys( x int primary key, y int, z date );

Table created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> create or replace view all_keys

  2 as
  3 select * from negative_keys union all select * from positive_keys;

View created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> create or replace trigger all_keys_trigger

  2 INSTEAD OF UPDATE on all_keys for each row   3 begin

  4      if ( :old.x <> :old.y ) then 
         -- remember, they are primary keys they CANNOT be null
  5          if ( :old.x < 0 ) then
  6              delete from negative_keys where x = :old.x;
  7          else
  8              delete from positive_keys where x = :old.x;
  9          end if;
 10          if ( :new.x < 0 ) then
 11              insert into negative_keys values ( :new.x,:new.y,:new.z );
 12          else
 13              insert into positive_keys values ( :new.x,:new.y,:new.z );
 14          end if;
 15      else
 16          if ( :new.x < 0 ) then
 17            update negative_keys set y = :new.y, z = :new.z where x = :new.x;
 18          else
 19            update positive_keys set y = :new.y, z = :new.z where x = :new.x;
 20          end if;
 21      end if;

 22 end;
 23 /

Trigger created.

tkyte_at_8.0>
tkyte_at_8.0> insert into negative_keys values ( -1, 100, sysdate );

1 row created.

tkyte_at_8.0> insert into negative_keys values ( -2, 200, sysdate-5 );

1 row created.

tkyte_at_8.0> insert into positive_keys values ( 3, 300, sysdate+5 );

1 row created.

tkyte_at_8.0> insert into positive_keys values ( 4, 500, sysdate-10 );

1 row created.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> update all_keys set y = y+25 where x = -1;

1 row updated.

tkyte_at_8.0> update all_keys set z = z+2 where x = -2;

1 row updated.

tkyte_at_8.0>
tkyte_at_8.0> update all_keys set x = -x;

4 rows updated.

tkyte_at_8.0> 
tkyte_at_8.0> 
tkyte_at_8.0> select * from all_keys;

         X          Y Z
---------- ---------- ---------
        -3        300 07-SEP-99
        -4        500 23-AUG-99
         1        125 02-SEP-99
         2        200 30-AUG-99

there is no need to go and check each and every column....

>What's really needed is a way of retrieving the list of columns being
>updated once in the trigger. Guess it's a little gap in functionality
>(not to mention the old NULL-being-used-for-more-that-one-logical-thing
>chestnut).
>
>Does anyone know a clean workaround for this?
>
>Cheers
>
>Andy

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Thu Sep 02 1999 - 06:45:58 CDT