• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 262
  • Last Modified:

SQL Server multiple database update.

I have a SQL Server running with several databases on it.

One database runs the application sofware, the other runs the web application.  However, I have data in one databse table that needs to be updated on the other database on a particular table...as its updated.  

Can this be done and how?
0
lrbrister
Asked:
lrbrister
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>...as its updated.  
you will need a trigger on the table that get's updated, to "replicate" the change to the other database/table.

do you need help with the syntax? what are the tables/changes to be done?
0
 
Jai STech ArchCommented:
yes it can be done

you can write a trigger on your SOURCE database-table and in that trigger write the insert statement that will have the other database table name as

insert into dbname.dbo.tablename. (colnames) values(the values)

you can get the values in the TRIGGER using the INSERTED command...this object will have the current record thatis inserted...let me know if you need the syntax also...give me the field names if so...
0
 
lrbristerAuthor Commented:
angelIII:
  Since you had the first response...

I have a table that will have at most 3 fields that need to get updated on the other table.

Both tables have an ID that is the same (It's been manually input but I HAVE written a stored procedure that does the input now using a datasource)

When this table is updated, it's done so on an aspx page with vb behind that calles a stored procedure.

I need to update AT THE END of the SP the other table with (something like)


begin
update db2.tbl1
set
db2.tbl2.field1=db1.tbl1.field1,
db2.tbl2.field2=db1.tbl1.field2,
db2.tbl2.field3=db1.tbl1.field3

where
db2.tbl2.ID=db1.tbl1.ID


0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
with the trigger:

USE db1
go
CREATE TRIGGER trg_replicate_table
 ON tbl1
 FOR UPDATE
AS
  UPDATE t2
     SET field1 = i.field1
         , field2 = i.field2
         , field3 = i.field3
   FROM db2.dbo.tbl2 t2
   JOIN inserted i
       ON i.id = t2.id

GO


you could, of course, also modify the procedure, but that risks to get inconsistencies when someone can update the table without the procedure.
0

Featured Post

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now