Hi Noami,

TaxRi was written and tested in VFP7. With the introduction of TRY CATCH in VFP8, it stopped working for cascading delete and update triggers. Since nobody really uses cascading updates, it in practise will mean that this is a problem for cascading deletes only when it cascades more than 1 level deep.

I did perform a quick fix to wrap certain commands into a TRY CATCH, but therefor it would not be supported by the ODBC and OLE DB driver.

I'll look into this problem into more depth later. Since the problem already exists for about 7 years, I gues a week or two does not matter much.

Walter,





>Hi Walter,
>
>Thanks for the interesting reminder. I don't remember all the details but I know what I re-wrote a bit the _RI_Handler I was using and I added a special table in our database that was keeping the relations. I'm not even sure right now which RI procedure is currently in use at this work place.
>
>>Hi Noami,
>>
>>Sorry for opening this thread again after 5 years, but I stumbled accross this problem because of a problem in TaxRi which I only noticed yesterday.
>>
>>There are indeed problems in using TRY, CATCH in combination with ON ERROR in a trigger. The problem you describe also exists in TaxRi. Also some other nasty bug in VFP is that the _triggerlevel does not seem to be reset after the "procedure canceled".
>>
>>I'm diving into it today, seeing whether I can find a solution for this problem.
>>
>>Walter,
>>
>>
>>
>>>Hi everybody,
>>>
>>>With the VFP built-in RI Builder the following behavior occurs:
>>>
>>>If you have a restricted insert and try to insert a fk which doesn't exist in the parent table, VFP gives an error "Trigger failure".
>>>If you try to insert the existing value, it works fine.
>>>If you try to insert NULL as a fk, it allows to insert it. It happens because of the bug in the builder itself. The error "data type mismatch" is generated on the seek command. This error is handled by the rierror procedure and at the end it allows to insert this value. So, it's a bug which produces a desired result at the end.
>>>
>>>=========================================================
>>>We switched to Steve Sayer's/Doug Henning's _RI_Handler. This code doesn't allow NULL values to be inserted in the FK. It does select count(*) from the parent table to see, if the passed foreing key exists in the parent. This could be easily fixed by adding a check for NULLS in the Restrict_Update and Restrict_Insert procedures (which I did yesterday).
>>>But I think that instead of select count we can use INDEXSEEK with .f. as a second parameter. If we switch to indexseek, we would not need macro. What do you think?
>>>The other problem I found is that it leaves all the tables open. I think, it should close all talbes it opened internally. This is not hard to fix as well.
>>>
>>>Finally, it currently loops through all RIs to find out the RI for the particular table. I'm thinking, this code could be changed to use ASCAN instead. In our case we have 252 relations, so I think, it's going to speed up the process. What would be your opinion here?
>>>
>>>--------------
>>>Finally, if I try to put my insert into TRY/CATCH block, I'm getting "Procedure cancelled" error in case of the failure in the trigger. I believe it happens because we have ON ERROR and RETURN .f. inside the trigger code. E.g. if we're having nested Error handling and we try to return .f. from the inner error, we would have this "Procedure cancelled" error. I think, it would happen every time we have return .f. inside the inner error statement, not necessarily for triggers. Is it a bug in VFP or it's a feature?
>>>
>>>Thanks a lot in advance.