aspose file tools*
The moose likes JDBC and the fly likes Trigger with Two database Big Moose Saloon
  Search | Java FAQ | Recent Topics | Flagged Topics | Hot Topics | Zero Replies
Register / Login
JavaRanch » Java Forums » Databases » JDBC
Bookmark "Trigger with Two database" Watch "Trigger with Two database" New topic
Author

Trigger with Two database

Nagu Spartan
Ranch Hand

Joined: Jan 08, 2010
Posts: 40
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.



Thanks & Regards,
Nagarajan T.
Stephan Mueller
Ranch Hand

Joined: May 05, 2010
Posts: 50
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.


1. Make it run - 2. Make it run correctly - 3. Make it pretty OR fast/small - 4. ??? - 5. Profit
Scott Selikoff
author
Saloon Keeper

Joined: Oct 23, 2005
Posts: 3716
    
    5

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.


My Blog: Down Home Country Coding with Scott Selikoff
Stephan Mueller
Ranch Hand

Joined: May 05, 2010
Posts: 50
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

Joined: Oct 23, 2005
Posts: 3716
    
    5

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

Joined: May 05, 2010
Posts: 50
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
 
I agree. Here's the link: http://aspose.com/file-tools
 
subject: Trigger with Two database