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.