Re: updating a referencing field via trigger

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Sat, 29 May 1999 16:15:25 GMT
Message-ID: <3752127c.3806383@newshost.us.oracle.com>

A copy of this was sent to Thomas Bierhance <Bierhance_at_orbital-computer.de> (if that email address didn't require changing) On Sat, 29 May 1999 13:51:28 +0200, you wrote:

>Hi,
>
>I'm having a problem in following situation. There is a table 'A' that
>references another table 'B'. When deleting a row in table 'B' I want
>the reference in table 'A' set to NULL. I created a trigger for that:
>
>before delete on B for each row
>begin
> update
> A
> set
> A_B_ID = null
> where
> A_B_ID = :old.B_ID;
>end;
>
>But it does not work as oracle says that i can't update this reference
>when changes (deleting) are applied to the referenced table.
>
>Any suggestions?
>
>Thanks
>
>Thomas Bierhance

You'll be happen to know that in Oracle8i, release 8.1:

SQL> create table p ( x int primary key ); Table created.

SQL> create table c ( y int primary key, x references p on delete SET NULL ); Table created.

SQL> insert into p values ( 1 );
1 row created.

SQL> insert into c values ( 1, 1 );
1 row created.

SQL> select * from c;

         Y X
---------- ----------

         1 1

SQL> delete from p;
1 row deleted.

SQL> select * from c;

         Y X
---------- ----------

         1

Its a native feature -- on delete set null.

In 8.0, the following will work (you don't specify a version)... The trick here is to use a deferrable constraint -- the foreign keys won't be verified until the transaction ends. Beware -- a COMMIT will generate an error now (most programs don't check!)

SQL> create table p ( x int primary key ); Table created.

SQL> create table c ( y int primary key,

  2                   x int references p deferrable initially deferred);

Table created.

SQL> create or replace package p_pkg
  2 as
  3 type numArray is table of number index by binary_integer;   4

  4          deleted numArray;
  5          empty   numArray;

  6 end;
  7 /

Package created.

SQL> create or replace trigger p_bd
  2 before delete on p
  3 begin
  4 p_pkg.deleted := p_pkg.empty;   5 end;
  6 /

Trigger created.

SQL> create or replace trigger p_bdfer
  2 before delete on p for each row
  3 begin
  4 p_pkg.deleted( p_pkg.deleted.count+1 ) := :old.x;   5 end;
  6 /

Trigger created.

SQL> create or replace trigger p_ad
  2 after delete on p
  3 begin

  4          for i in 1 .. p_pkg.deleted.count
  5          loop
  6                  update c set x = NULL
  7                   where x = p_pkg.deleted(i);
  8          end loop;

  9 end;
 10 /

Trigger created.

SQL> insert into p values ( 1 );
1 row created.

SQL> insert into c values ( 1, 1 );
1 row created.

SQL> commit;
Commit complete.

SQL> select * from p;

         X

---

         1

SQL> select * from c;

         Y X
---------- ----------

         1 1

SQL>
SQL> delete from p;

1 row deleted.

SQL>
SQL> select * from p;

no rows selected

SQL> select * from c;

         Y X
---------- ----------

         1

SQL>
SQL> commit;

Commit complete.

In 7.x, i don't think you can do this without writing a 'delete' procedure and using that in place of delete if you are using declaritive RI.

See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'...  

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 Sat May 29 1999 - 11:15:25 CDT