• Post Reply Bookmark Topic Watch Topic
  • New Topic
programming forums Java Mobile Certification Databases Caching Books Engineering Micro Controllers OS Languages Paradigms IDEs Build Tools Frameworks Application Servers Open Source This Site Careers Other Pie Elite all forums
this forum made possible by our volunteer staff, including ...
Marshals:
  • Campbell Ritchie
  • Jeanne Boyarsky
  • Ron McLeod
  • Paul Clapham
  • Liutauras Vilda
Sheriffs:
  • paul wheaton
  • Rob Spoor
  • Devaka Cooray
Saloon Keepers:
  • Stephan van Hulst
  • Tim Holloway
  • Carey Brown
  • Frits Walraven
  • Tim Moores
Bartenders:
  • Mikalai Zaikin

Trigger with Two database

 
Ranch Hand
Posts: 40
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.


 
Ranch Hand
Posts: 50
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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.
 
author
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
Posts: 4335
39
jQuery Eclipse IDE Java
  • Mark post as helpful
  • send pies
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
    Number of slices to send:
    Optional 'thank-you' note:
  • 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
reply
    Bookmark Topic Watch Topic
  • New Topic