| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.misc -> Re: trigger question
A copy of this was sent to "Vincent Lemaire" <v.Lemaire_at_kheops.com>
(if that email address didn't require changing)
On Wed, 9 Jun 1999 13:51:34 +0200, you wrote:
>Hi
>(using Oracle 7.3)
>I created a trigger called before update of a primary key. Here is the
>listing :
>
>create or replace trigger t_pk before update of pk on master for each row
>begin
> update slave
> set slave.fk = :new.pk
> where slave.fk = :old.pk;
>end;
>
>this trigger is supposed to update the corresponding column of another table
>referencing this primary key, but that message appears : (translated from
>french)
>
>SQLWKS> update master set pk = 2 where pk = 1;
>ORA-04091: table VINCENT.MASTER in mutation, trigger/function can't see it
>ORA-06512: in "VINCENT.T_PK", line 2
>ORA-04088: error while execution of trigger 'VINCENT.T_PK'
>
>I tried to disable the 'foreign key' constraint for the slave table : as I
>do this outside the trigger, it works, but an 'alter' statement is forbidden
>inside a trigger...
>
>so my question is : how to automatically update a foreign key when the
>primary key referenced is updated ?
>
>
See http://govt.oracle.com/~tkyte/index.html for a solution to the update_cascade. Its fairly complex to implement. Its implemented there...
See http://www.oracle.com/ideveloper/ for my column 'Digging-in to Oracle8i'... Mirrored (and more current) at http://govt.us.oracle.com/~tkyte/
Current article is "Fine Grained Access Control", added June 8'th
Thomas Kyte tkyte_at_us.oracle.com Oracle Service Industries Reston, VA USA--
 |
 |