Originally posted by: ManojBansal

Hi,

I am doing a DB to DB mapping and my database is SYBASE. Though I have set the SCOPE in each card as "MAP", my map is inserting values to tables even though its failing during run time and also even if there is a foreign key relationship error, it doesn't throw any error and says "map completed successfully" and  insert the values into some random tables!

I enabled the backup setting on first 5 input and output cards and all files contains the data but when I check those tables in DB, there are no entries in table.

Any idea, is it a issue with SYBASE or my map.

Thanks in advance.
Manoj

Originally posted by: paul.brett

Further details on this issue....

The foreign key relationship is implemented as a trigger.  When data is inserted into certain tables, the trigger runs, and checks that the inserted data meets the 'foreign key' criteria, and if not, triggers an exception and a transaction roll-back.

Example:

create trigger TRIGGER1
on TABLE1
for insert
as
declare @rows int
select @rows = @@rowcount
if @rows = 0 return

if ( select count(*) from inserted
where
isnull(ref_group_cd ,null) is not null  and
isnull(producer_cd ,null) is not null)
!=
( select count(*) from inserted , TABLE2 t1
where
inserted.ref_group_cd  = t1.ref_group_cd
and inserted.producer_cd  = t1.producer_cd)
begin
rollback transaction
raiserror 999999 "Foreign key insert (TABLE1) not permitted. Primary table: TABLE2"
return
end  
go

Thank-you.

Paul.

Follow me on Twitter

Originally posted by: paul.brett

After speaking with the IBM WebSphere TX Software Developers, I can report that this functionality is currently NOT supported.  The fact that a rollback is being done within the middle of a WTX transaction will result in undefined behavior.

Thank-you.

Paul.

Follow me on Twitter