Results 1 to 7 of 7
  1. #1
    Join Date
    Jan 2002
    Posts
    2

    Unanswered: Updating of the tables in two different databases

    I am using SQL server7.0. I am having two databases ,say database A and database B.Database A is the main database which is used in two /three projects.While database B is created by me for my work.From the database A, I am using the 4/5 tables, which i have copied in to the database B.
    So, i want to update these tables in the database B as soon as any change (insert,update or delete ) occures on the tables in the database A.
    That's why I am interested in doing these work.I have tried, but it doesn't work.So, I have placed these into these forum.


    Do anybody is having script fordoing these job.Plz,mail it to me Urgently...

  2. #2
    Join Date
    Dec 2001
    Location
    USA
    Posts
    13
    You could try doing this using triggers for Update, delete and Insert changes to the first database and have it propigate to the second database.

    Are the tables very large that you couldn't set up a job to drop ad recreate the tables that you need every nite?

    Scooter McFly

  3. #3
    Join Date
    Jan 2002
    Posts
    2
    the database is not large but I just can't get the triggers to work. Any idea how I could do it please? I am new to SQL triggers and I am sure I am missing on something. A small exmaple would be really very helpful.

    Thanks in advance.

  4. #4
    Join Date
    Oct 2001
    Location
    Slovakia
    Posts
    3
    Post your current nonworking trigger...

  5. #5
    Join Date
    Oct 2001
    Location
    England
    Posts
    426
    Your triggers will need to be something like

    create trigger x on tbl for insert
    as
    insert dbase2.dbo.tbl
    select *
    from inserted
    go

    create trigger x on tbl for update
    as
    update dbase2.dbo.tbl
    set col1 = deleted.col1, ...
    from inserted
    where dbase2.dbo.tbl.pk = inserted.pk
    go

    create trigger x on tbl for delete
    as
    delete dbase2.dbo.tbl
    select *
    from deleted
    where dbase2.dbo.tbl.pk = deleted.pk
    go

    Your user will need permissions on the tables in dbase2.

    Have you also thought about restoring databases. You would have to stop all activity on the two databases to do backups to keep them in step.
    I would do this via transfer tables to get round the backup problem so that they would automatically keep in step.

  6. #6
    Join Date
    Mar 2001
    Location
    Lexington, KY
    Posts
    606
    You could always set up replication to B using the tables from A as the master.
    Thanks,

    Matt

  7. #7
    Join Date
    Aug 2006
    Posts
    1
    Hi nigelrivett this is harshvardhan

    I have tried this query in SQL server2000 but i m getting error with delete and update trigger
    My trigger is:

    create trigger Tr_Delete on TriggerTable for delete
    as
    delete DBForTrigger2.dbo.TriggerTable
    select *
    from deleted
    where DBForTrigger2.dbo.TriggerTable.TSrno = deleted.TSrno
    go

    error is:
    Server: Msg 107, Level 16, State 3, Procedure Tr_Delete, Line 4
    The column prefix 'DBForTrigger2.dbo.TriggerTable' does not match with a table name or alias name used in the query.
    and wat is about tansfer table
    can u just elaborate it

    Quote Originally Posted by nigelrivett
    Your triggers will need to be something like

    create trigger x on tbl for insert
    as
    insert dbase2.dbo.tbl
    select *
    from inserted
    go

    create trigger x on tbl for update
    as
    update dbase2.dbo.tbl
    set col1 = deleted.col1, ...
    from inserted
    where dbase2.dbo.tbl.pk = inserted.pk
    go

    create trigger x on tbl for delete
    as
    delete dbase2.dbo.tbl
    select *
    from deleted
    where dbase2.dbo.tbl.pk = deleted.pk
    go

    Your user will need permissions on the tables in dbase2.

    Have you also thought about restoring databases. You would have to stop all activity on the two databases to do backups to keep them in step.
    I would do this via transfer tables to get round the backup problem so that they would automatically keep in step.
    Last edited by Harshvardhan; 08-11-06 at 12:29.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •