Updating foreign keys with triggers

[ Article crossposted from comp.databases.oracle ]
[ Author was Francis Small ]
[ Posted on 29 Oct 1996 19:40:48 GMT ]

I have two tables. Let's call them:

Table1 Table2
====== ======
field1 field1

         field2

Great names, huh? Anyway, Field1 is the primary key in Table1 and a foreign key in Table2 referencing Table1. I really want to be able to do two things:

1. Insert (field1, field2) values into table2. If field1 doesn't exist in table1, that's an error. The above schema fits the bill perfectly.
2. If I modify field1 in table1, have the modifications apply automatically to table 2 through triggers. That is, some trigger on an update of table1 along the lines of:

     UPDATE table2
        SET field1 = :new.field1
      WHERE field1 = :old.field1

   Unfortunately, this results in some error message about "mutating tables".    (And I thought I had a low radiation screen.) Is there a way to accomplish    both my objectives without having to write a program for each special case?    I may want to apply this as a general rule throughout my database.    Thanks.

     Francis

--
. . . . . . . . . . | . . . . . . . . . 2 . . . . . . . . . 3 . . . . . . . .
Padres                                         Francis Small - M/S M3RA     
  Bums                                           Hewlett-Packard Company
                Rockies                            Microwave Instruments Div.
                                              Giants 1212 Valley House Dr.
                                                       Rohnert Park CA, 94928
                                                         707 794 3305
                                                           fthes_at_sr.hp.com
******************** Final National League West Standings ********************

--
. . . . . . . . . . | . . . . . . . . . 2 . . . . . . . . . 3 . . . . . . . .
Padres                                         Francis Small - M/S M3RA     
  Bums                                           Hewlett-Packard Company
                Rockies                            Microwave Instruments Div.
                                              Giants 1212 Valley House Dr.
                                                       Rohnert Park CA, 94928
                                                         707 794 3305
                                                           fthes_at_sr.hp.com
******************** Final National League West Standings ********************