Dilemma with Set Relation to table with Trigger

[hjohnson]

We have just recently moved some free tables into a DBC to take advantage of the triggers to write data into a SQL-server table. The triggers themselves are working fine, however the issue we have come up against is that quite often we are making updates using a Set relation to.. and Scan .. replace Endscan routine.

The set relation works fine for just browsing the table but once we start updating it doesn’t seem to move the record pointer. Here is an example.

TableA= Table in DBC with Trigger to update SQL.
Custid c(10), Cust_name c(30)
Index on custid Tag Custid

‘123’, ‘Customer 1 ‘
‘124’, ‘Customer 2 ‘
‘125’, ‘Customer 3 ‘
‘126’, ‘Customer 4 ‘

TableB= free table to update names in tableA
Custid c(10), Cust_name c(30)

‘123’, ‘Jon Doe ‘
‘124’, ‘Gene Erik ‘
‘125’, ‘Fred FlintStone‘
‘126’, ‘Barney Rubble‘

Select TableB
Set relation to Custid into TableA
Scan
Replace Next 1 TableA.Cust_name with tableB.Cust_name
? Tablea.custid, TableA.Cust_name
endscan

Result:
123 Jon Doe
123 Gene Erik
123 Fred FlintStone
123 Barney Rubble

~
If I remove the trigger, the replace works fine and everything updates as it should- except of course the SQL table which was the point of the trigger in the first place. I’ve tried this with both VFP6 and 9 so it doesn’t appear to be a version issue.

Has anyone else ever come across this situation? And if so, what can we do to solve this.

Thanks

Hal

 

[Mike Lewis]

Hal,

My guess is that the trigger is changing something in the environment, such as the current work area.

If you could post the code in the trigger, it might be possible to figure it out.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[hjohnson]

I've stepped through the code with the debugger and watched the Alias() which is correctly switching from the table with trigger, to the view that we use to update the SQL table, then back again.

Here is the trigger code. I've left some of the detail- we update some of the empty() date fields with null as we don't use null date in VFP app.

Thanks-
Hal

***--------------------------------------------------
Function CustTrigger(tcTriggerType)
&& Added in case Cursor is built form array created by afields()
If justext(dbf())!='DBF'
Return .t.
Endif

Private lcCustid, oSQL
lcCustid=Custid && Needed for the Paramtized View

** make sure we have SQLCust open..
If NOT Used('SQLCust')
Use CustDBC!SQLCust in 0 nodata
Endif
Select SQLCust
Requery('SQLCust')

*SQLCust.PKIden is an identify field in SQL
If m.tcTriggerType == [D] && Delete..
If SQLCust.PKIden > 0 && delete only if there was a record in there before..
Delete IN SQLCust
Endif
Else && Insert or Update..
If SQLCust.PKIden <= 0 && likely an Insert..
Append Blank in SQLCust
Endif
Gather Name oSQL
Endif

If NOT TableUpdate(.T.,.T.,"SQLCust")
=sqlupdate('SQLCust') && Log any errors
Endif


**---------------------

 

[ramani]

Hi

The culprit is the ReQuery. The Table relationship gets lost when the requery is made. The cursor gets closed and opened and you might be loosing the SET RELATION there. You may have to check in that.



____________________________________________
ramani - (Subramanian.G)

http://winnersoft.coolfreepages.com/

 

[hjohnson]

Ramani,

The requery() is not used on any of the tables involved in the set relation. The relation ship is still intact (based on the data environment when issueing a 'set'). It's almost as if they are not long in sync. It's kinda hard to example and visualize what is happening. I've trien not using the Requery() and just making sure the view is closed then re-opened but I still get the same results.

Thanks-
Hal

 

[TamarGranor]

One problem here is that you're issuing the REPLACE in workarea B, but changing data in workarea A. Even if that's not causing your current problem, it's a really idea and likely to bite you sooner or later. The easiest way to fix it is to add "IN TableA" to your REPLACE command.

However, in this case, you could turn the relation around and do the whole thing with a single REPLACE or UPDATE command. You would have to add an index on TableB.

SELECT TableA
SET RELATION TO CustID INTO TableB
REPLACE Cust_Name WITH TableB.Cust_Name FOR NOT EOF("TableB") IN TableA

Tamar