March 25, 2010 at 7:25 am
I want to use the Microsoft aspnetdb to control membership/profile/login etc. However some items in another database (on the same server) must be associated with a userID
I could run a job periodically to get new userIDs into the 2nd database, or I could write an AFTER INSERT trigger to maintain a copy of aspnet_Membership in the 2nd database.
I prefer the trigger idea - I'm just asking for a sanity check here.
March 25, 2010 at 10:42 am
if the second table (the one maintained by the trigger) is involved in a a lot of transactions then the trigger might be a bad idea. In that case use some form of replication.
If its basically a read only table then the trigger idea should be okay.
The probability of survival is inversely proportional to the angle of arrival.
March 25, 2010 at 11:35 am
if the databases are on the same server, you could just reference the table directly in the other database by using the three-part naming convention
database.owner.table
March 25, 2010 at 12:02 pm
I would recommend any option that does not involve triggers. Triggers fire within the scope of the transaction so using a trigger to populate a table in another database would mean that the transaction would be held open for each INSERT until the subsequent INSERT into the second DB happens. It's just not a good idea.
I would vote for replication of that table, or defining a view in your second database that used a linked server to get the data from the first table.
June 8, 2015 at 12:42 pm
If you have any concern for disaster recovery, you'll also want to keep in mind that cross-database transactions are not supported in Database Mirroring or Availability Groups...
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply