Best practices for moving data using triggers in SQL Server 2000

Question

Hello Friends, I have some troubles trying to move data from SQL Server 2000 (SP4) to Oracle10g, so the link is ready and working, now my issue is how to move detailed data, my case is the following:

Table A is Master Table B is Detail

Both relationed for work with the trigger (FOR INSERT)

So My query needs to query both for create a robust query, so when trigger get fired on first insert of Master it passed normal, in the next step the user will insert one or more details in Table B, so the trigger will be fired any time the record increment, my problem is that I need to send for example :

1 Master - 1 Detail = 2 rows (Works Normal) 1 Master - 2 Details = 4 rows (Trouble)

In the second case I work around the detail that in each select for each insert it duplicates data, I said if Detail have 2 details the normal is that it will be 2 selects with 1 row each one, but in the second select the rows get doubled (query the first detail inserted)

How can I move one row per insert using triggers on Table B?

Answer 1

Most of the time this boils down to a coding error, and I blogged about it here:

http://www.brentozar.com/archive/2009/01/triggers-need-to-handle-multiple-records/

However, I'm concerned about what's going to happen with rollbacks. If you have a program on your SQL Server that does several things in a row to different tables, and they're encapsulated in different transactions, I can envision scenarios where data will get inserted into Oracle but it won't be in SQL Server. I would advise against using triggers for cross-server data synchronization.

Instead, consider using something like DTS or SSIS to synchronize the two servers regularly.