| Author |
 Topic  |
|
|
dayve
Forum Moderator
USA
5820 Posts |
 Posted - 03 July 2002 : 21:54:48
|
is there a method or trigger that one could write using MS SQL to syncrhonize tables between different databases when a change occurs to a "source" table??
For example. let's say I have two snitz forums and want to populate the info in the members database for both tables if someone registers on either one of the forums. I would like to see if this can be done at the database level and NOT ASP..
this is more of a curiousity question because I don't know all the little tricks MS SQL is capable of but this would sound a little enticing to do if it could be done efficiently.
Thanks.
http://www.nineinchnailz.com |
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 03 July 2002 : 22:18:07
|
Probably the most obvious way to do this would be replication. Never used it myself, but know the theoretical principles and a bit about the way SQL Server handles it. It looks like a definite possibility.
It is possible that you could even use 'normal' triggers for that, but I would need to confirm that before being more affirmative on that.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
 |
|
|
Gremlin
General Help Moderator
New Zealand
7528 Posts |
Posted - 04 July 2002 : 03:09:36
|
Replication is usually between servers rather than tables, not sure if you can Replicate within the same server.
It is perfectly feasilbe though as you point out just to setup a trigger on insert/update/delete on one table which does the insert into the backup table.
www.daoc-halo.com |
|
|
|
HuwR
Forum Admin
United Kingdom
20611 Posts |
Posted - 04 July 2002 : 03:38:04
|
you best bet would be to write triggers to do this.
To synchronise two diferent databases, you can use replication, however this may stop the forum working because all the table will be given an extra GUID field which it uses to track replication, this will in most cases stop the underlying code from working, unless it was written specifically with replication in mind.
|
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
Posted - 04 July 2002 : 07:32:17
|
quote:
To synchronise two diferent databases, you can use replication, however this may stop the forum working because all the table will be given an extra GUID field which it uses to track replication, this will in most cases stop the underlying code from working, unless it was written specifically with replication in mind.
I don't have experience with this in SQL Server, but I'd figure the GUID is added and handled by the server automatically. I had replicated Access databases and they worked perfectly without the need to insert changes to the VBA code, so I was thinking the same would happen with SQL Server.
-------------------------------------------------
Installation Guide | Do's and Dont's | MODs |
|
|
|
grazman
Junior Member
USA
193 Posts |
Posted - 04 July 2002 : 08:52:24
|
Triggers would certainly do the job. You can find two articles on creating them at http://www.sqlteam.com/item.asp?ItemID=3850 and http://www.sqlteam.com/item.asp?ItemID=6494.
I think a single table view to replace the second members table would be better though. It would work something like this:
- Create the first forum in db1 using the prefix FORUM_.
- Create the second forum in db2 using the prefix SECOND_.
- Rename SECOND_MEMBER to SECOND_MEMBER_ORIGINAL
- Create a view named SECOND_MEMBER that points back to the first member table. Something like:
Create View db2.dbo.SECOND_MEMBER
AS
Select *
From db1.dbo.FORUM_MEMBER
- Enjoy one member table used in two forums
ASP code can't tell if it's selecting from a view or a table. If a view maps to only one table you can insert, update, delete, etc. just like the view was a table - i.e. either forum can add users to both forums. You should probably list all the fields instead of the SELECT *.
The one major headache of this will be installing new versions. For that I'd just reverse the steps to restore the original table and then repeat the steps to recreate the view. You'll also need to make sure the same SQL Server login has permissions in both databases.
SQLTeam.com - For SQL Server Developers and Administrators
Snitz Info - SQL Server info on Snitz Forums |
|
|
|
ruirib
Snitz Forums Admin
Portugal
26364 Posts |
|
|
dayve
Forum Moderator
USA
5820 Posts |
|
| |
Topic |
|
Topic Locked
