The thing is that my app is not really aware of the col2 index value. I
just
want to change the col1 value from a to b which would cause a PK viol. The
col2
index is maintained by the db, on inserts and at upates..




Helen Borrie
<helebor@... Till: firebird-support@yahoogroups.com
.au> Kopia:
Ärende: Re: [firebird-support] Execution order for cascaded updates and before
2004-02-03 12:14 update triggers
Sänd svar till
firebird-support





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




Yahoo! Groups Links

To visit your group on the web, go to:
http://groups.yahoo.com/group/firebird-support/

To unsubscribe from this group, send an email to:
firebird-support-unsubscribe@yahoogroups.com

Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/