Re: Cascading Updates

Ahh, welcome to the world of triggers and "mutating" tables! Sorry, but Oracle 7.3 and 8.0 don't support "ON UPDATE CASCADE"; not sure about 8i (the doc makes it look like it's still not available). And, if you place a trigger on the parent to delete from the child (which has an RI constraint), you'll get the infamous "mutating table" message. The 8i doc has a good section on triggers and creating them to update/delete cascade. Also, check out Tom Kyte's pages at http://osi.oracle.com/~tkyte/.

HTH

--
Alan Moorman
Oracle DBA
Oracle ERP DBA (as in  "I can run adpatch")
Unisys DMS DBA (yes, "antiquated" mainframe stuff that still kicks butt!)
MS SQL Server DBA (don't ask...)
Oleg Kuzmin wrote in message ...


>Greetings!

>

>When I create a table in Oracle 8 using PL/SQL script, the following syntax

>(sample fragment) is used:

>

>CREATE TABLE TEST_TABLE

>(

>    VALUE1          VARCHAR2(10)  NOT NULL,

>    VALUE2          VARCHAR2(50)  NOT NULL,

>    VALUE3          VARCHAR2(4) NOT NULL,

>    PRIMARY KEY (VALUE1,VALUE3),

>    FOREIGN KEY (VALUE2,VALUE3) REFERENCES TEST_TABLE1(VALUE2,VALUE3) ON

>DELETE CASCADE

>);

>

>If I want to delete the record in the parent table (TEST_TABLE1), it'll

>propagate the deletions of related records in the child table (TEST_TABLE).

>However, if I try to change a value in the parent table, and there are

 child


>records with the foreign key value being changed, it gives me an error:

>

>ORA-02292: integrity constraint (TEST_DATABASE.SYS_C002255) violated -

 child


>record found

>

>Is there an easy way in Oracle to propagate updates in the parent table to

>all its children?  The only way I see it possible is through triggers, and

>I'd like to avoid it.

>

>I'll really appreciate any help on this issue.

>

>Thanks,

>

>

>Oleg

>

>