Cross database relationship - Diagram or trigger?

Cross database relationship - Diagram or trigger?

Post by Marti » Fri, 28 Nov 2003 13:56:11



Hi,

I have two tables in seperate databases on SQL Server 2000.

One table contains Sales Orders

When I insert update or delete a row in the Sales Order I want a row to be inserted, updated or deleted with the same Sales Order Number

The Column name is OrdLineNo

Can you have diagrams that refer to tables across different databases or does this need to be achieved with a trigger?

Any comments greatly appreciated

Martin

 
 
 

Cross database relationship - Diagram or trigger?

Post by Narayana Vyas Kondredd » Fri, 28 Nov 2003 14:00:27


A diagram is basically creating a FOREIGN KEY constraint behind the scenes.
But a FOREIGN KEY constraint is not allowed across databases. So, if you
want this to happen automatically, you'll have to use triggers. Another
option is to change your code, so that it updates both the tables.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm


Hi,

I have two tables in seperate databases on SQL Server 2000.

One table contains Sales Orders

When I insert update or delete a row in the Sales Order I want a row to be
inserted, updated or deleted with the same Sales Order Number

The Column name is OrdLineNo

Can you have diagrams that refer to tables across different databases or
does this need to be achieved with a trigger?

Any comments greatly appreciated

Martin

 
 
 

Cross database relationship - Diagram or trigger?

Post by Ryan Waigh » Fri, 28 Nov 2003 14:09:28


Here's a good reference :-

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnsq...

--
HTH
Ryan Waight, MCDBA, MCSE


Quote:> Hi,

> I have two tables in seperate databases on SQL Server 2000.

> One table contains Sales Orders

> When I insert update or delete a row in the Sales Order I want a row to be

inserted, updated or deleted with the same Sales Order Number
Quote:

> The Column name is OrdLineNo

> Can you have diagrams that refer to tables across different databases or

does this need to be achieved with a trigger?
Quote:

> Any comments greatly appreciated

> Martin

 
 
 

Cross database relationship - Diagram or trigger?

Post by John Be » Fri, 28 Nov 2003 17:34:46


Hi

I believe you want to create a foreign key between the two tables. It
is not currently possible to do this between databases, but you can
use a function called from a check constraint or implement the check
in the trigger. The trigger option should be quicker. Reference the
referenced table with a three part name e.g.
MyOtherdatabase..SalesOrders

John


> Hi,

> I have two tables in seperate databases on SQL Server 2000.

> One table contains Sales Orders

> When I insert update or delete a row in the Sales Order I want a row to be inserted, updated or deleted with the same Sales Order Number

> The Column name is OrdLineNo

> Can you have diagrams that refer to tables across different databases or does this need to be achieved with a trigger?

> Any comments greatly appreciated

> Martin