Re: Cascade Update Trigger Problems

From: <dleenhouts_at_gmail.com>
Date: 9 Aug 2006 12:18:36 -0700
Message-ID: <1155151116.535082.42010_at_m79g2000cwm.googlegroups.com>

[Quoted] Ok, I've got a new trigger written, & it sets all the fk's to the new key value

update child_table
set companykey = :new.companykey
where companykey = :old.companykey;

And I also threw in a SET CONSTRAINTS ALL DEFERRED; statement in the update script (not in the trigger).

There are maybe 100 or so child tables in the trigger. However, when I run the script, it gives a unique constraint on only one of these child tables. I thought that setting the constraints all deferred turned those constraints off until a commit was completed? I tried setting the trigger to fire both before & after the update, & even put in a statement making that specific constraint initially deferred.

The error says:

ORA-00001: unique constraint (ECOMPROD.SYS_C0067551) violated
ORA-06512: at "ECOMPROD.COMPANYKEYUPDATE", line 26
ORA-04088: error during execution of trigger

'ECOMPROD.COMPANYKEYUPDATE' Is this a matter of the update itself is incorrect, or am I missing something in turning off the constraints? Received on Wed Aug 09 2006 - 21:18:36 CEST