Post reply

How to trigger a auto update

  • November 18, 2008 at 4:53 am

    #195964

    Dear Friend,

    My situation as follows

    i have two databases , each databases are in different location for example one is in Washingten and another one is in california.each databse have two tables.

    washingten databse("DB1") have table called ("Emp","Status")

    california databse("DB2") have table called("employee","Status")

    so my need is when ever any updates done in "Status" table of washingten database that should be automatically upadted in "Status" table of california database also.

    how this logic could be achieved? do i need to use trigger and procedure for this ? is there any examples for this please provide me so that i can understand please

  • November 18, 2008 at 5:06 am

    #899798

    i would think you'd need to add a linked server on washington to point to The california server, and repeat the process on the Cali server to point to washington.

    once you have linked servers, you could do updates based on your requirement:

    the problem with a trigger is that if the update to the linked server fails, the transaction would roll back if there was a network issue or something like that. just be aware

    example code on the washington server mught run this command in your proposed trigger :

    UPDATE California.DB2.dbo.employee cali

    set cali.Status = = INSERTED.Status,

    UpdatedDate = getdate()

    WHERE cali.employeeID = INSERTED.employeeID

    to do it in a stored proc, you need to have a WHERE statement to find the changes at a later time....is there an updatedDate column or somethign you can determine which records changed outside of a trigger? do you really need instant updates rom a trigger, or could it be once a day/hour or something?

    for example:

    UPDATE California.DB2.dbo.employee cali

    set cali.Status = wash.Status,

    cali.UpdatedDate = wash.UpdatedDate

    from Emp wash

    WHERE cali.employeeID = wash.employeeID

    and wash.UpdatedDate >= getdate() -1 ---everything less than a day old?

    and cali.Status <> wash.Status --don't re-update items we touched on a previous pass

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply