October 29, 2009 at 2:12 pm
I need help in writing a trigger or just a T-SQL statement for updating a field in another database. Here is the senario. Database 1 has table A in it. Database 2 has table b. A new record is entered into Database 2 table b. Most of the same information gets entered into Database 1 table A later. An update to field x in database 2 table b is entered. I need to take the information in field x and insert it into field y on Database 1 table a. Would I want to create a trigger or just write a query?? Any help would be very much appreciated.
October 29, 2009 at 3:01 pm
I assume you can join the two tables and rows together.
The issue gets into permissions between databases. The user in DB 2 must have rights, or ownership chaining enabled, in db 1.
I would create a view in Db2 of table A in db1 (create view as select [fields] from db1.dbo.tableA)
Then if you are sure a trigger is needed, build a trigger on table B to update your view.
That way if things change, server changes, database changes, etc. you have an abstraction with your view and you are not updating lots of code, just a little.
October 29, 2009 at 3:05 pm
Did you check the examples in Books Online ??
http://msdn.microsoft.com/en-us/library/ms189799%28SQL.90%29.aspx
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
October 29, 2009 at 3:59 pm
As Steve indicated. Do this only if you really need to.
Coupling databases over SQL - especially triggers - is a dangerous thing.
Edit: Typo
October 30, 2009 at 7:53 am
Thanks for the info. The reason I have to do this is that currently we we are entering data in two places and I am just trying to make it so they only have to update it in one. Is there any other way to do that besides a trigger?
October 30, 2009 at 8:14 am
SSIS
If you don't need a real-time synchronization you can use a SSIS job to synchronize the data.
Problem: Not real-time
Handle in Client
If you need a real-time synchronization you might be able to handle it in client by using a bridge which communicates with both systems.
Problem: Duplicates the network traffic.
If you need a real-time synchronization and want to handle it by SQL...
Stored Procedures
If all C(R)UD statements are abstracted by procedures you can implement the transfer to the other database within the procedures. (Still more clean than triggers - in my opinion).
Problem: Probably duplicated source code and still coupled databases.
Trigger
At last there are still the triggers based solution.
Problem: Database are coupled and triggers are hidden behavior.
Greets
Flo
October 30, 2009 at 9:38 am
Flo has good ideas. If you have some leeway for latency (I had to do the alliteration), and could handle the data update a minute later, I'd think about:
Write a proc to scan the table for changes, take the change and apply them to the other table.
Schedule the proc as a job every minute.
Alternatively you could use Service Broker for something like this, which might be the best solution if you want to learn it.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply