This used to work, so I'm not sure what I did to screw it up (might have
something to do with my SET EXCLUSIVE setting), but here's what's
happening:
I have two tables: USERS and USERPROMOS. The primary key of USERS,
userid, is also a foreign key in USERPROMOS. The relational integrity
procedures are set to stop inserts into UP without a corresponding userid
in U.
When I get a batch of records to import in, I get the record from my
input file using a SCATTER NAME, INSERT the record into U, then go to UP
and insert a record there, too, referencing Users->Userid as the userid
for the new record in UP. Like so:
SELECT _INCOMING
BEGIN TRANSACTION
ON ERROR DO "Err_CannotAddRec"
DO WHILE !EOF()
SCATTER NAME oRec
WITH oRec
* Add record to USERS
INSERT INTO Users (...a bunch of fields...);
VALUES (...a bunch of values...)
* Add record to UserPromos
INSERT INTO UserPromos (userid, initcontact) ;
VALUES (Users->UserID, DATE())
ENDWITH
oRec = NULL
SKIP
ENDDO
END TRANSACTION
The problem I'm having right now is that the second INSERT is failing in
the INSERT trigger. It goes and opens up the USERS table in a new
workarea and SEEKs the userid to verify it's there. For some reason,
though, it's NOT there. If I go over and look at the USERS workarea, it
IS there, but it doesn't come into the area where the RI procedure opened
it up using USE USERS AGAIN.
I'm thinking it's my transaction. If I go and do an END TRANSACTION when
the program stops at the second insert, then retry that insert, it works
because the new instance of USERS actually does find the new userid.
Problem is, I'm not willing to dispense with the transaction in my code.
I can't risk something screwing up during the import because a partial
import would be real problematic.
Could I solve this with some kind of buffering change, or anything like
that? Another idea I had was to separate the two inserts into two
separate loops, with a TRANS surrounding each of the loops. That's a
pain, though, thanks to having to locate the new userid's.
TIA for any help.
--
Michael Kellogg
CC3 West DP Programming
Running VFP 6-SP4