Cascading updates for two relationships relating to the same table (SQL 2000 B2)

Cascading updates for two relationships relating to the same table (SQL 2000 B2)

Post by Sven Aelterma » Mon, 11 Sep 2000 22:15:29



Hi again

After finally figuring out that I don't need triggers in SQL 2000 to
implement cascading updates and deletes, I have come across the following
problem:

Our customers are able to have orders invoiced to other customers. E.g.
Customer A can create an order, but he wants the invoice sent to Customer B.

Customer A can send invoices to many other customers and customer B can
receive invoices from many other customers. Result: an intersection table.

Customer    1---------oo    CustomerInvoices    oo----------1    Customer

If the customer ID changes, I want to have the CustomerInvoices table also
updated. But when creating this cascading relationship, SQL Server says that
there is a risk of an update cycle. I can perfectly well understand this.
This would occur when Customer A would be able to invoice Customer A. This
will be avoided in the business layer.

Is there a workaround for this. The issue is that this is not the only
relationship I have like this: there are at least 4 similar relationships,
so I'm really looking for a solution.

Should I go back to using triggers for this relationship? Or can I force SQL
Server into accepting this?

TIA

--

Kind regards

Sven Aelterman
IT
Global Communication N.V.

Please reply to the newsgroup.

 
 
 

Cascading updates for two relationships relating to the same table (SQL 2000 B2)

Post by Ivan Arjentinsk » Mon, 11 Sep 2000 22:47:58


Triggers. Sorry.

--
Ivan Arjentinski
-----------------------------------------------
Please answer only to the newsgroups.
I'll not answer any direct emails.
-----------------------------------------------


Quote:> Hi again

> After finally figuring out that I don't need triggers in SQL 2000 to
> implement cascading updates and deletes, I have come across the following
> problem:

> Our customers are able to have orders invoiced to other customers. E.g.
> Customer A can create an order, but he wants the invoice sent to Customer
B.

> Customer A can send invoices to many other customers and customer B can
> receive invoices from many other customers. Result: an intersection table.

> Customer    1---------oo    CustomerInvoices    oo----------1    Customer

> If the customer ID changes, I want to have the CustomerInvoices table also
> updated. But when creating this cascading relationship, SQL Server says
that
> there is a risk of an update cycle. I can perfectly well understand this.
> This would occur when Customer A would be able to invoice Customer A. This
> will be avoided in the business layer.

> Is there a workaround for this. The issue is that this is not the only
> relationship I have like this: there are at least 4 similar relationships,
> so I'm really looking for a solution.

> Should I go back to using triggers for this relationship? Or can I force
SQL
> Server into accepting this?

> TIA

> --

> Kind regards

> Sven Aelterman
> IT
> Global Communication N.V.

> Please reply to the newsgroup.


 
 
 

Cascading updates for two relationships relating to the same table (SQL 2000 B2)

Post by Sven Aelterma » Tue, 12 Sep 2000 00:59:42


Don't be sorry.

At least all the work I put into triggers has not been for nothing.

I'm going to put the checks for CustomerID1 = CustomerID2 in the trigger
too.

--

Kind regards

Sven Aelterman
IT
Global Communication N.V.

Please reply to the newsgroup.

http://www.aelterman.com


> Triggers. Sorry.

> --
> Ivan Arjentinski
> -----------------------------------------------
> Please answer only to the newsgroups.
> I'll not answer any direct emails.
> -----------------------------------------------



> > Hi again

> > After finally figuring out that I don't need triggers in SQL 2000 to
> > implement cascading updates and deletes, I have come across the
following
> > problem:

> > Our customers are able to have orders invoiced to other customers. E.g.
> > Customer A can create an order, but he wants the invoice sent to
Customer
> B.

> > Customer A can send invoices to many other customers and customer B can
> > receive invoices from many other customers. Result: an intersection
table.

> > Customer    1---------oo    CustomerInvoices    oo----------1
Customer

> > If the customer ID changes, I want to have the CustomerInvoices table
also
> > updated. But when creating this cascading relationship, SQL Server says
> that
> > there is a risk of an update cycle. I can perfectly well understand
this.
> > This would occur when Customer A would be able to invoice Customer A.
This
> > will be avoided in the business layer.

> > Is there a workaround for this. The issue is that this is not the only
> > relationship I have like this: there are at least 4 similar
relationships,
> > so I'm really looking for a solution.

> > Should I go back to using triggers for this relationship? Or can I force
> SQL
> > Server into accepting this?

> > TIA

> > --

> > Kind regards

> > Sven Aelterman
> > IT
> > Global Communication N.V.

> > Please reply to the newsgroup.