I have a SQL Server table as the source. My mapping will do an insertion to oracle target table T1. And then, will make an entry to another target T2 with the ID from T1 (ID is created by a "after insert" trigger on T1 using Oracle Sequence). If some failure happens then no insertion should happen to T1 as well as T2.
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations wOOdy-Soft on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Inserting into 2 Trgts -- 2nd needs PK from 1st
- Thread starter ProDev
- Start date
I can't see how you can be using an "after insert" trigger to generate the key on T1. It must surely be a "before insert".
If you're generating the key from a trigger, then I think you will have to do this in two different mappings. There is no easy way you could access the ID created by the trigger from within PowerCenter so that it could be used for the insert into the second table. The only option is probably to record which rows you've inserted by tagging them with a LOAD_ID column. If the insert to T2 fails, then delete everything in T1 with the current LOAD_ID.
If you're generating the key from a trigger, then I think you will have to do this in two different mappings. There is no easy way you could access the ID created by the trigger from within PowerCenter so that it could be used for the insert into the second table. The only option is probably to record which rows you've inserted by tagging them with a LOAD_ID column. If the insert to T2 fails, then delete everything in T1 with the current LOAD_ID.
- Status
Similar threads
- Locked
- Question
- Locked
- Question