Win a copy of Re-engineering Legacy Software this week in the Refactoring forum
or Docker in Action in the Cloud/Virtualization forum!
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic

Trigger with Two database

 
Nagu Spartan
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Hi

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.


 
Stephan Mueller
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
That doesn't make sense.
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.
 
Scott Selikoff
author
Saloon Keeper
Posts: 4008
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
Actually there is a way. With MS SQL you can write an Extended Stored Procedure that does not need to be written in SQL. Then, you just define a trigger that calls the procedure. An as someone who has written Extended Stored Procedures... they are not fun.
 
Stephan Mueller
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
author
Saloon Keeper
Posts: 4008
18
Eclipse IDE Flex Google Web Toolkit
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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.
 
Stephan Mueller
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
  • Quote
  • Report post to moderator
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
 
  • Post Reply
  • Bookmark Topic Watch Topic
  • New Topic