I am using two database, Ms Sql Server 2005 & MySQL.
I want to create Trigger in MS SQL Server Database. When inserting the new record in MS SQL Database, the trigger will fired and Fetch data from MySQl database and stored in MS SQL server database.
How to connect mysql database from MsSql server trigger.
If the database would reside in the same dbms, this dbms should provide means to accomplish this - but your scenario is different.
Either your data-inserting application has to make sure the data get's also populated in the second dbms or you write an adapter
that will perform this task (rather than altering the application).
If you do not agree, I suggest calling MS support.
Actually there is a way
Admitted, my "call the support" statement was a bit too harsh but I'll keep my point.
It's bad practice to do such a thing. These things do belong in the application-logic, not into the database.
Even better, they should be considered when designing the hardware architecture.
Another approach would be to let the trigger write output-files that are catched by a daemon who in turn puts the data into whatever the user wants.
Scott Selikoff wrote:Creating an extended stored procedure is more akin to putting software inside the DB rather than having software call the DB. It's useful in situations where you want the database to function autonomously with ability to form complex tasks independent of an application server. It's not necessarily a bad practice, but it is really painful to configure to work properly.
You are right. It's no black/white decission as implied by me. I'm going to internalise this from now on