controlling activities of one db with other

[binjovi]

Hi guys

I have 2 database,db1 and db2. I wanna control the activities of one db1(main) with other db2. for ex suppose some updation or insertion is done on the db1 i need to pop up a trigger from the db2

It will be of great help..

thanks in advance..

regards
bins

 

[meker]

Bins,

You are wondering into dangerous waters when talking about doing distributed transactions. It is hard to handle/control failures on the other side (db2 in your example). Locked records will cause nightmares. If a failure occurs, how you recover and how you identify which transactions failed so you reprocess them becomes tricky.

I would recommend that you create a column in the source table on db1 that defaults to 'N', or something like that. Make sure it is indexed. Then have a separate process that attempts to process all N's against the table(s) in db2. If it succeeds, update the db1 column to a 'Y'. Build in some pre-checking, like testing to see if db2 is up and ensuring required records are not locked, some error handling, and a method for monitoring the process, and you are off and running.

There are many other ways to tackle this issue. This happens to be one I use frequently.

Mark