Very Computer

This is an "advice needed" kind of issue.

I'm using ERwin (IDEF1X Modeling) to create tables
that are pretty straight forward. Sybase System 10.0.4
is the DBMS, but there seems to be a problem that
is just idiotic to me. Perhaps i'm "just not getting"
something here..

Here's the example:

Referential Integrity: (on all tables)
--------------------------------------
Cascade : Parent Update  <-- This is important
                             -----------------
 -------------------
|tbl:country            |
 -------------------
|country_number (PK)|
 -------------------
|                   |
 --------------------
      |
      |
      |P
      O one to many
 ---------------------
|tbl:state                |
 ---------------------
|state_number(PK)     |
|country__number (FK) |
 ---------------------
|                     |
 ---------------------
      |
      |
      |P
      O one to many
 ---------------------
|tbl:City                 |
 ---------------------
|city_number(PK)      |
|state_number(FK)     |
|country_number (FK)  |
 ---------------------
|                     |
 ---------------------

Pretty easy stuff, until I try to cascade a change made
from table Country to all the children tables.

Here's the values:

Table:Country
------------
country_number
1

Table: State
----------------
country_number | state_number
1              | 1
1              | 2

Table: City
----------------
country_number | state_number | city_number
1              | 1           | 1
1              | 1           | 2
1              | 1           | 3

Now try this SQL statement

update country
set country_number=99
from country
where country_number=1

With cascading RI, table State will trigger 2 updates
and the update on table City will fail. I looked at the
trigger code that ERwin uses for Parent Update Cascade
and saw this:

 [do update]
else
 fail

So..That means that when table City gets the

statement but Sybase reported something like:

Msg x : "Multiple insert on index failed.."

I checked some books and came across this statement:

"Updates to primary keys of multiple rows cannot
 be supported in the relational model."

So how does one cascade this kind of change? I tried
this in MS Access V7.0 and it worked fine, but i'm not
100% sure that Access uses fully relational models (as
opposed to Sybase).

It seems to me this is a very common type of relationship to
have, and the fact that Sybase is not letting me do this
leads me to believe that I'm going about this all wrong.

Any tips or advice is very welcomed...

Kevin Stussman

Quote:

>Table: State
>----------------
>country_number | state_number
>1              | 1
>1              | 2

Very good question!
Understanding the problem depends on understanding the rest of the trigger.
In an Update, Sybase has "tables" called 'inserted' and 'deleted'.

In order to cascade the update, Sybase needs to find all of the rows
in City that match deleted.country and apply the change found in
inserted.country.

If there are more than one row updated in State, how is Sybase to
match the rows from inserted to the rows in deleted? Sure, with your
example, it doesn't matter. But Sybase doesn't know that.

One possibility is to have the cascade update go directly from
Country to City as well as Country to State. That way a single
row affects all the tables. Another possible approach is to possibly
have a cursor in the country update trigger going across the states.
That way only one state is updated at a time. Finally, you might add
a 'hidden' identity column (since you are using system 10) to be able
to identify the states.

I haven't tried to do any of these myself, but with several solutions
to try one of the's got to work!

Bob Munson

--

Update... Where tsequal(timestamp,convert(varbinary(8),convert(money,