Update two databases at the same time

Arsi · November 9th, 2004, 06:26 PM

Hi!

I need a push in the right direction. I am trying to figure out how to update two different sql server databases at the same time. The databases are both on the same server. Not all the fields in both databases are the same. I want to know should I use triggers or should I just set up an update statement for the both databases and just run those right after each other? Any help with this question is greatly appreciated.

Thanks,

*******(*)*******

happygv · November 10th, 2004, 07:58 AM

Hi Arsi,

Yes, you could write a trigger on the table on which you run the update statement, so that on update event happening on that table would trigger another update event that updates the values to the other table on another database. It could be something like this.

Code:

CREATE TRIGGER [MyTrig] ON [TABLE1] 
FOR UPDATE
AS
If update(COLUMN1)
    Update OTHERDB..TABLE1 set COLUMN1 = i.COLUMN1
        from DELETED d, OTHERDB..TABLE1 t, INSERTED i
        where d.COLUMN1 = t.COLUMN1
return

Hope this gives you the needed push you in the right direction.;)
Cheers!

_________________________
- Vijay G
Strive for Perfection

Arsi · November 10th, 2004, 01:34 PM

Hi!

Thank you for your help! This is what I came up with, with your help. It does not update the second database though. What am I doing wrong?

ALTER TRIGGER [Update_DB] ON [dbo].[ProjectInfo]
FOR UPDATE
AS
If UPDATE(ProjectName)
    UPDATE ProjectStatus..ProjectInfo2
    SET ProjectName = i.ProjectName
    FROM ProjectInfo d, ProjectStatus..ProjectInfo t, ProjectInfo2 i
    WHERE d.ProjectName = t.ProjectName
RETURN

*******(*)*******

happygv · November 12th, 2004, 11:20 AM

Hi Arsi,

You were not using the INSERTED and DELETED tables as given in my suggested code. INSERTED and DELETED tables are the ones that can be used only within TRIGGERS, where INSERTED holds the the ROW with the NEW values that you pass for UPDATE statement, and DELETED holds the record with the OLD VALUES that were there in the orginal table before UPDATE was done.

Hope this explains better. I was under assumption that you would be having knowledge about these two table, that is the reason why I didnot explain on that.

Code:

ALTER  TRIGGER [Update_DB] ON [dbo].[ProjectInfo] 
FOR UPDATE 
AS
If UPDATE(ProjectName)
    UPDATE ProjectStatus..ProjectInfo2
    SET ProjectName = i.ProjectName
    FROM DELETED d, ProjectStatus..ProjectInfo t, INSERTED i
    WHERE d.ProjectName = t.ProjectName
RETURN

This should be how it should look like, if needed make some alterations to this code so as to work as expected.

Hope that helps.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Arsi · November 12th, 2004, 07:12 PM

Hi happygv!

Again, thank you for your help.

You're right i did not know about the deleted & inserted tables. I am very new to Sql server.

I can only do very basic things. I changed my trigger per your example, but something keeps going wrong. The update is passed to the database via a Sqlcommand and connection in C#. It updates correctly when the trigger is not on the database, but once I put the trigger on the database, the first update does not even execute. I don't understand why it doesn't allow for the code to finish executing. It errors out at dbComm.ExecuteNonQuery(); this only occurs when the trigger is placed on the database. What do you think I should do?

*******(*)*******

happygv · November 15th, 2004, 02:21 AM

Quote:

quote:the first update does not even execute

Does that mean the other UDPATE execute? But how many UPDATEs are you running? Is that you want to run an UPDATE stement that would inturn trigger the other update from the backend(from within the database) OR is that you are trying to run two update statements still?

I am little confused on that.

Did you try the Update statement from the SQL query analyser? Does that successfully run and update the other table too? If that happens, something should be wrong the way you use that in your frontend app.

Also it is better to post the error what you see when executing this.

Cheers!

_________________________
- Vijay G
Strive for Perfection

Arsi · November 15th, 2004, 12:41 PM

Sorry about making things so confusing. But here is what I'm trying to do: When one sql server database is updated, a trigger has to update and sql server database on the same server or insert that record if it does not exist. At this point I am not getting an error, it just stops and does nothing. I am going to see if I can Catch the error then I'll post it. Thank you so much for your help and patience.

Arsi

*******(*)*******

Arsi · November 15th, 2004, 12:53 PM

WOW!!!

I was able to get it working thanks to your help! I am very excited! Thank you, thank you, thank you.

I changed:

FROM DELETED d, ProjectStatus..ProjectInfo t, INSERTED i

To:
FROM DELETED d, ProjectStatus..ProjectInfo2 t, INSERTED i

now i have to work on inserting the record if it does not exist in the 2nd database. If I run into anymore problems I know where to go.

Thanks again,

Arsi

*******(*)*******

happygv · November 16th, 2004, 03:01 AM

So within the trigger check if that record exists in the other table and update if exists. Else you got to insert that.

Hope that should be easy now.
Cheers!

_________________________
- Vijay G
Strive for Perfection

Arsi · November 18th, 2004, 06:14 PM

Ok. I think I messed things up. I'm trying to do what you told me with the insert, but I have made a mess of it. Can you please help me with this too?
ALTER TRIGGER [Update_DB] ON [dbo].[ProjectInfo]
FOR INSERT, UPDATE
AS
If UPDATE(ProjectName)
IF EXISTS (SELECT ProjectStatus..ProjectName
        FROM ProjectStatus
        WHERE ProjectStatus..ProjectName = ProjectInfo.ProjectName)
    UPDATE ProjectStatus..ProjectInfo2
    SET ProjectName = i.ProjectName
    FROM DELETED d, ProjectStatus..ProjectInfo2 t, INSERTED i
    WHERE d.ProjectName = t.ProjectName
ELSE
INSERT ProjectStatus..ProjectInfo2 (ProjectName)
VALUES (ProjectInfo..ProjectName)
RETURN

I would really appreciate it. Thank you.

*******(*)*******