Re: Updating foreign keys with triggers

Hi, Francis!

The solution is not to declare referential constraint but to write additional trigger on an insert into table2 that checks referential intehrity. It avoid mutation.

Francis Small wrote:
>
> 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.
>

-- 
Regards. Alexey Neckrasow. Leaves Inc. Russia.