|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Releationship between 2 tables which are in 2 different databasesHi,
I have 2 tables namely table1 and table2 and 2 databases namely database1 and database2. table1 is in database1 and table2 is in database2. I want to define relationship between these 2 tables which are in 2 different databases. Is this possible? If so, How can I do this? Thanks and Regards, Sevugan.C Hi,
Using TRIGGERS. Go for Insert, Update and Delete Triggers. Thanks Hari SQL Server MVP Show quote "Sevugan" <Sevu***@discussions.microsoft.com> wrote in message news:6A6667AD-345E-46EF-942B-1CE2B0779C02@microsoft.com... > Hi, > > I have 2 tables namely table1 and table2 and 2 databases namely > database1 > and database2. table1 is in database1 and table2 is in database2. > I want to define relationship between these 2 tables which are in 2 > different databases. > > Is this possible? If so, How can I do this? > > > Thanks and Regards, > > Sevugan.C This is not possible with DRI. Try creating a function and check constraint.
Use triggers. Show quote "Sevugan" <Sevu***@discussions.microsoft.com> wrote in message news:6A6667AD-345E-46EF-942B-1CE2B0779C02@microsoft.com... > Hi, > > I have 2 tables namely table1 and table2 and 2 databases namely > database1 > and database2. table1 is in database1 and table2 is in database2. > I want to define relationship between these 2 tables which are in 2 > different databases. > > Is this possible? If so, How can I do this? > > > Thanks and Regards, > > Sevugan.C "Sevugan" <Sevu***@discussions.microsoft.com> wrote in message Triggers.news:6A6667AD-345E-46EF-942B-1CE2B0779C02@microsoft.com... > Is this possible? If so, How can I do this? Cross-database referential integrity can be achieved through the use of
triggers. You'll need triggers that prevent insert/update statements from creating orphans in the foreign key table: either by inserting erroneous references to the primary key table into the foreign key table, or by deleting rows from the primary key table that are referenced by rows in the foreign key table. The same goes for cascading actions - can be done with triggers. For details on how to design your triggers post DDL. ML Hi Sevugan
you cannot have reference accross databases. There is a work around for this. You can create a Instead of Insert and Instead of Update trigger, delete trigger. and keep the integrity intact please let me know if u have any questions -- Show quotebest Regards, Chandra http://chanduas.blogspot.com/ http://groups.msn.com/SQLResource/ --------------------------------------- "Sevugan" wrote: > Hi, > > I have 2 tables namely table1 and table2 and 2 databases namely database1 > and database2. table1 is in database1 and table2 is in database2. > I want to define relationship between these 2 tables which are in 2 > different databases. > > Is this possible? If so, How can I do this? > > > Thanks and Regards, > > Sevugan.C Hi All
Can anyone give me a small example which will be very helpful for me? Thanks and Regards, Sevugan.C Show quote "Chandra" wrote: > Hi Sevugan > you cannot have reference accross databases. > > There is a work around for this. You can create a Instead of Insert and > Instead of Update trigger, delete trigger. > > and keep the integrity intact > > please let me know if u have any questions > > -- > best Regards, > Chandra > http://chanduas.blogspot.com/ > http://groups.msn.com/SQLResource/ > --------------------------------------- > > > > "Sevugan" wrote: > > > Hi, > > > > I have 2 tables namely table1 and table2 and 2 databases namely database1 > > and database2. table1 is in database1 and table2 is in database2. > > I want to define relationship between these 2 tables which are in 2 > > different databases. > > > > Is this possible? If so, How can I do this? > > > > > > Thanks and Regards, > > > > Sevugan.C Hi
this is a pseudo code, probably this will help you CREATE TRIGGER <TRIGGER> ON <ChildTable> INSTEAD OF INSERT, UPDATE AS IF EXISTS(SELECT * FROM <PARENT> WHERE <PARENT>.<key> = INSERTED.<Key>) BEGIN INSERT INTO <ChildTable> SELECT * FROM INSERTED END GO -- Show quotebest Regards, Chandra http://chanduas.blogspot.com/ http://groups.msn.com/SQLResource/ --------------------------------------- "Sevugan" wrote: > Hi All > Can anyone give me a small example which will be very helpful for me? > > Thanks and Regards, > > Sevugan.C > > > "Chandra" wrote: > > > Hi Sevugan > > you cannot have reference accross databases. > > > > There is a work around for this. You can create a Instead of Insert and > > Instead of Update trigger, delete trigger. > > > > and keep the integrity intact > > > > please let me know if u have any questions > > > > -- > > best Regards, > > Chandra > > http://chanduas.blogspot.com/ > > http://groups.msn.com/SQLResource/ > > --------------------------------------- > > > > > > > > "Sevugan" wrote: > > > > > Hi, > > > > > > I have 2 tables namely table1 and table2 and 2 databases namely database1 > > > and database2. table1 is in database1 and table2 is in database2. > > > I want to define relationship between these 2 tables which are in 2 > > > different databases. > > > > > > Is this possible? If so, How can I do this? > > > > > > > > > Thanks and Regards, > > > > > > Sevugan.C Hi Chandra,
This is will help me to prevent inserting orphan records into the child table. I have 2 doubts regarding this. Can you please clarify them? 1. When these triggers will fire? 2. How would I define the referential integrity(creation of tables)? Thanks and Regards, Sevugan.C Show quote "Chandra" wrote: > Hi > > this is a pseudo code, probably this will help you > > CREATE TRIGGER <TRIGGER> > ON <ChildTable> > INSTEAD OF INSERT, UPDATE > AS > > IF EXISTS(SELECT * FROM <PARENT> WHERE <PARENT>.<key> = INSERTED.<Key>) > BEGIN > INSERT INTO <ChildTable> SELECT * FROM INSERTED > END > > GO > > > -- > best Regards, > Chandra > http://chanduas.blogspot.com/ > http://groups.msn.com/SQLResource/ > --------------------------------------- > > > > "Sevugan" wrote: > > > Hi All > > Can anyone give me a small example which will be very helpful for me? > > > > Thanks and Regards, > > > > Sevugan.C > > > > > > "Chandra" wrote: > > > > > Hi Sevugan > > > you cannot have reference accross databases. > > > > > > There is a work around for this. You can create a Instead of Insert and > > > Instead of Update trigger, delete trigger. > > > > > > and keep the integrity intact > > > > > > please let me know if u have any questions > > > > > > -- > > > best Regards, > > > Chandra > > > http://chanduas.blogspot.com/ > > > http://groups.msn.com/SQLResource/ > > > --------------------------------------- > > > > > > > > > > > > "Sevugan" wrote: > > > > > > > Hi, > > > > > > > > I have 2 tables namely table1 and table2 and 2 databases namely database1 > > > > and database2. table1 is in database1 and table2 is in database2. > > > > I want to define relationship between these 2 tables which are in 2 > > > > different databases. > > > > > > > > Is this possible? If so, How can I do this? > > > > > > > > > > > > Thanks and Regards, > > > > > > > > Sevugan.C "Sevugan" <Sevu***@discussions.microsoft.com> wrote in message That will depend how you create them. E.g. if you create an INSERT trigger news:79728647-872B-4E16-B274-117ED31ED8AA@microsoft.com... > 1. When these triggers will fire? on a table, it will fire every time a record is inserted into that table. Same with an UPDATE trigger and a DELETE trigger. Do a search for CREATE TRIGGER in BOL, paying special attention to the INSTEAD OF section... > 2. How would I define the referential integrity(creation of tables)? Er, by using triggers...Are you worried someone might drop one of the tables? In SQL 2000 there isn't
much you can do. It might slow down the 'table dropper' if you create a view on each table winth schemabinding. That way at least a dependency is created that prevents tables from being deleted. SQL 2005 introduces DDL triggers. ML  |
|||||||||||||||||||||||
Other interesting topics