At 11:44 AM 3/02/2004 +0100, you wrote:

>Hi there,
>
>I am cascading an update. One column is a sequential index within the value
>of another column
>
>Col1 Col2 Col3
>a 1 abc
>a 2 abc
>b 1 abc
>b 2 abc
>
>where col1+col2 make up the PK and the col2 'index' is set by an on insert
>trigger.
>If I want to change the value of col1 from a to b for a record, I obviously
>wld have to change the col2 index in a before update trigger.
>I then cascade the update of these two columns to the dependent tables.
>
>It seems, however, as if the cascade occurs before the on update trigger,
>resulting in a key viol in the dependent table.
>
>Is this what happens, and is there a workaround?

I'm struggling to understand why you are using the BeforeUpdate trigger to
alter the key. The way the cascade works, you change the key in your SQL
with a SET statement and it takes care of the cascade action. The
new.pk-value is already there by BeforeUpdate time. If you change the key
again, you'll break the cascade that has already been performed, based on
the "new." values of the primary key. Either use the cascade, or use
custom triggers, but don't do both.

/hb