Replication Error

I found a problem in replication which we have 3 tables, namely tab A, tab B and tab C

replication will apply on tab A and tab B

and there is a trigger on tab A and tab B, whenever insert/update on tab A it fires trigger to update tab b

whenever insert/update on tab B it fires trigger to update tab C

And I found the replication error from tab A on updating tab C. If I manually insert the row, it works fine.....

Any advise? Thanks in advance!

what's the error?|||

Error Code 515:

Cannot insert the value NULL into column 'amount_id', table 'dev.dbo.tab_c'; column does not allow nulls. INSERT fails.

Thanks

|||

Can you also insert value NULL successfully into column 'amount_id" of table_C manually?

-Yunjing

|||

No, actually, the column 'amount_id' is defined as 'not null'. While inserting the row into tab A, it fires the trigger and check the conditions in tab b, and then insert the row into tab c. However, I found the 'amount_id' is defined in the trigger and shouldn't be null.

I used sp_browsereplcmds to find out the command with error and manual exec the store proc.

sp_MSins_ADMTAB_A with the field values and it works. After the row inserted, the pending transactions delivered to the subscriber successfully (as I set the replication option to skip the error 2627 for duplicate primary key)

|||

Sorry, I am confused. It looks like there is no issue.

Is the trigger, when insert/update table B, table C will be inserted with rows,defined by yourself or generated by replication?

Also, table A is on publisher side, are table B and C on subscriber side? What's your replication scenario?

THanks

Yunjing

|||

the triggers are defined by myself for business purpose.

table A and table B exist in both publisher and subscriber. Our approach is whenever there is an update in table A and B in oracle publisher, the transaction replicate to table A and B in Mssql2005 subscriber and user trigger updates table C.

|||

stephanie, please be clear where and what the problem is. We understand now you're using transactional replication with Oracle publisher, correct? The triggers are user-defined at the subscriber, correct? If you drop the user triggers at the subscriber, does the insert proc executed by the distribution agent succeed? If so, the problem must be in your triggers. If the problem persists, then it looks like it has nothing to do with triggers. Have you tried running with profiler to see what values are being passed and what statements are being executed?

|||

I found the replication command with error in replication monitor and sp_browsereplcmds

I have traced though the triggers, the trigger checked the records in other tables which are also replicated from oracle.

And I found that the records are missing in other tables which required in the triggers...and cause the replication error.

Anyone can advise me how does the replication command apply to subscriber? The command with xact_seqno(24EC and command_id:27) causing error and the command with xact_seqno(24E7 and command_id:13) doesn't appear in the table...

How to determine to sequence of commands applying to subscriber? Thanks in advance!

|||also, if I manual insert the record into the table by sql or calling the replication sp sp_MSins, the trigger works fine. Guessing it may be some problem in sequence of command or commit replication transactions. Any idea? Thanks in advance!sql