Re: Cascade Update Trigger Problems

dleenhouts_at_gmail.com wrote:
> 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?

My read of this is that it is a case of trying to do the impossible.

The error message is plainly telling you that you are violating the constraint. You need to make sure that the value is in the parent before it is in the child. And just writing: SET CONSTRAINTS ALL DEFERRED
does nothing if the constraints are not created as deferrable.

-- 
Daniel A. Morgan
University of Washington
damorgan_at_x.washington.edu
(replace x with u to respond)
Puget Sound Oracle Users Group
www.psoug.org