August 9, 2006 at 11:53 pm
Hi,
Here's the scenario. We are an educational provider and provide courses from 5 different universities. We have separate databases for each university. Now we are designing student portal and we are going to have single database for this purpose integrating student records from all 5 university databases because no matter which university course they are enrolled in, they are our student.
But the problem is with updating the records in student portal database. I am planning to add student portal database as linked server and use trigger in main database to update record in student portal database for each update, insert and delete. Just wondering if that's the way to go. I don't see other option. Any expert opinions will be appreciated.
August 11, 2006 at 9:59 am
Ok, nobody bit on this one, so I guess I will.
Why wouldn't you try to use replication? You could replicate the data to a table in the student portal database.
I don't know about everyone else, but I think triggers are generally something to be avoided at all costs.
Really, what you are looking at here is going to be a management problem no matter which way you go. Replication is generally better suited for the this type of task. If you wanted to have something immediate you could go the route of 2PC. However when replication breaks, it can be problematic to maintain.
I don't know, but there's something that tells me if you try to use triggers to do this, you're asking for more trouble than its worth. At least with replication, when it breaks, you probably would have more avenues of support with Microsoft and the SQL Server community. Using triggers, you probably will have a little less help for when it goes "kaboom".
August 11, 2006 at 10:10 am
Wait, I forgot to even ask. Are all these databases on the same server? If so you may be right to go with the trigger option. However if this is the case, I fail to understand why you would use a linked server to do it.
August 12, 2006 at 4:00 am
hey jeremy,
two databases will be in the same server and two will be in different server. i could have used replication but the problem is the schema is different in different databases. so when transferring data, i have to manipulate the data using trigger. so i am not sure whether i could achieve that using replication. there is option to filter data in replication but just filtering data won't work in my case. what do u think? i appreciate your opinion.
August 14, 2006 at 3:12 am
You could trigger locally to a table with a schema the same as the table that you want to publish to, then replicate from that table. A bit convoluted but performance would be better, and you would have more control.
Regards
Carolyn
August 14, 2006 at 3:29 am
I think you will be able to achieve what you want with replication. You can use the replication stored procedures to transform the data using code similar to what you have in your triggers. Or you can use DTS packages to do the same thing - you can configure it all in the replication properties.
John
August 14, 2006 at 5:21 pm
So trigger should be avoided if possible?
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply