Welcome to Sign in | Help
in Search

Relationship between two databases

Last post 09-27-2010, 2:52 PM by sarateanu_sorin. 3 replies.
Sort Posts: Previous Next
  •  09-07-2010, 2:27 PM 8424

    Relationship between two databases

    Cum pot relationa doua baze de date in SQL Server 2008?
    Filed under:
  •  09-07-2010, 4:56 PM 8425 in reply to 8424

    Re: Relationship between two databases

    Eu as face asa:

    presupun db1 baza de date care contine tabela cu cheia primara. In db2 as crea o serie de tabele aproape identice in care as scoate identity iar in prima tabela as face triggere de sincronizare. In exemplul meu am folosit un trigger de insert dar poti face si pe update sau delete. Deasemenea poti sa sincronizezi continutul prin proceduri stocate pe care le executi cand doresti (eu am folosit trigger pt ca se executa la momentul insertului). Practic, trigger-ul meu copiaza continutul tabelei din db1 intr-o tabela din db2. In db2, pe tabelele copii poti pune FK catre copiile din db2 a tabelelor din db1. 

    Esti sigur ca vrei sa impui constrangeri in baze diferite ? ... O baza de date reprezinta o colectie de obiecte, daca impui constrangeri de integritate in alte baze de date trebuie sa ai mare grija la acest aspect pentru ca operatiunile de alterare de structuri (adaugare/stergere/ modificare de coloane) sau backup/restore pot fi foarte periculoase (in sensul ca trebuie sa ai sincronizezi mereu obiectele, atat tabele cat si triggere/proceduri).

    use master

    go

    CREATE DATABASE db1

    go

    CREATE DATABASE db2

    go

    use db1

    go

    CREATE TABLE dbo.Test1 (

    id int identity(1,1) primary key,

    info nvarchar(128)

    )

    go

    use db2

    go

    CREATE TABLE dbo.Test1 (

    id int primary key, --fara identity

    info nvarchar(128)

    )

    GO

    use db1

    go

    CREATE TRIGGER [Test1_insert] on dbo.Test1

    FOR INSERT

    as begin

    insert into db2.dbo.Test1(id,info)

    select id, info from inserted

    end

    go

    use db2

    go

    CREATE TABLE dbo.Test2 (

    id int identity(1,1) primary key,

    id_pk int,

    info nvarchar(128),

    constraint [fk_id] foreign key (id_pk) references dbo.Test1(id)

    )

    go

    insert into db1.dbo.Test1(info)

    values ('test')

    insert into dbo.Test2(id_pk, info)

    values(@@IDENTITY, 'test2')

    insert into dbo.Test2(id_pk, info)

    values(-2, 'test2') --EROARE, -2 nu este Primary Key in Test1

    select * from db1.dbo.Test1

    select * from dbo.Test2

    go

    drop DATABASE db1

    go

    drop DATABASE db2


    Cătălin D.
  •  09-27-2010, 2:52 PM 8440 in reply to 8425

    Re: Relationship between two databases

    ...sau utilizand Database Diagrams .... ..creeaza foreyn key's in tabele ..automat
  •  09-27-2010, 2:52 PM 8441 in reply to 8440

    Re: Relationship between two databases

    ..scuze ..acum am vazut .. 2 database ..
View as RSS news feed in XML
Powered by Community Server (Commercial Edition), by Telligent Systems