Something for the weekend #7: Trigger Failing 4

[Olaf Doschke]

Hi everyone,

as announced, here is my "Something for the Weekend" question. This time with a little attachment.

I could do this in the lunch break now. But I'll keep the problem description very short:
Unzip the attachment, you'll find a database data.dbc with table1. Try to update records inside, no matter if via UPDATE-SQL, REPLACE or manually in a browse.
You'll always trigger the table1 Update-Trigger as defined in the data.DBC and it'll always Error with Error 111 - Cannot update the cursor TABLE1, since it is read-only.

Two part question:
1. Why the error on a table?
2. How can you overcome the problem?

As always it's easy, if you know. This time you'll get a very useful answer if you didn't know.

As always I'lll appeal to you to not spoil the fun for others with Mike's usual Note re spoilers

If you are one of the first to post a reply, consider using

Spoiler

tags to hide the answer from casual viewers. To use this tag, type [ignore]

Spoiler

[/ignore]

Spoiler

at the start of the answer, and [ignore]

[/ignore] at the end. Note that the tag is delimited by square brackets, not angle brackets.

Alternatively, you can use the spoiler icon on the toolbar - the one that looks like a birthday present wrapped in red ribbon.

To check that you have done this right, use the Preview facility before submitting your post.

Bye, Olaf.

 

[Mike Lewis]

Excellent question. It should brighten up an otherwise dull afternoon (which in any case will be a bit of an anti-climax for those of us in north west Europe who were lucky to see the eclipse this morning).

I don't know the answer for sure, but I would guess the following:

Spoiler

The trigger is trying to update the same table as the one to which it applies. I don't know if this is specifically disallowed, but I can see that it would cause a problem, as it would generate a sort of recursive update.

I tried altering the trigger so that it did something other than update the table, and it worked fine.

I'll be interested in seeing what other answers the folk here come up with.


Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[vgulielmus]

Spoiler

1) Cannot modify the same table that triggers the update trigger
2) a) delete the update trigger
b) make a small change in code

Procedure update_table1()
	IF timestamp <>Datetime()
		Replace timestamp with Datetime()
	ENDIF
Return .T.

c) make the procedure as a rule for the timestamp field

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Olaf Doschke]

To clarify: The intention of the trigger is to always automatically update the table1.timestamp field with DATETIME(), not matter what other changes are made to the "otherdata" field, which represents all other fields a table may have. And one more thing about the DBC: I also should have set the default value for the timestamp field to DATETIME(). Imagine it's set this way.

The circumvention of the problem should not just circumvent the error, but fulfill the intention to update the field.
Congrats so far for Vilhelm-Ion, you have solved it the way I intended it besides other solutions.

Bye, Olaf.

 

[Olaf Doschke]

Sorry, misunderstanding, you meant 2 a),b),c) as steps of a single solution. Anyway, it's right.

Bye, Olaf.

 

[Mike Lewis]

Agreed. Vilhelm-Ion explained it better than I did, and gave a good suggestion for how to avoid it.

Regarding the error message itself:

Cannot update the cursor "cursor", since it is read-only

This is a typical unhelpful error message. It gives no clue as to the reason for the inability to update the cursor, which in any case is not read-only, and isn't even a cursor (in the usual VFP sense of the term).

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

What I miss in your answer, Mike, is part 2. You say you changed the trigger to do something other than updating the table, and that only resolves the error, it's not a solution. It would be a partly solution, if you'd write the datetime() into some other place, table, log file.

Cursor actually is short for "current set of records", and could also be used as a term for anything open in a workarea, including a table. What's more misleading is the reasoning, the update fails because TABLE1 is read-only.

Bye, Olaf.

 

[Mike Lewis]

Olaf, my point about changing the trigger to do something else wasn't an answer to part 2. I simply did it to confirm (to myself) that my diagnosis was correct.

Mike



__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

OK, I see.

Just as a motivation: There also is a solution working without a code change.

Bye, Olaf.

 

[vgulielmus]

It's an exciting problem and I like to see as many solutions as possible.
I hope the solar eclipse wasn't too distracting ...

Respectfully,
Vilhelm-Ion Praisach
Resita, Romania

 

[Mike Lewis]

Vilhelm-Ion, I've just been looking again at the last part of your solution. You said:

make the procedure as a rule for the timestamp field

If it was a rule on the field, wouldn't it fail to fire if you issued a command that only updates a different field? I think you would need to make it a validation rule on the record, so that it fires regardless of what change you made.

Not a big point, but worth mentioning.

Mike

__________________________________
Mike Lewis (Edinburgh, Scotland)

Visual FoxPro articles, tips and downloads

 

[Olaf Doschke]

Correct, Mike.

1. The table rule is triggered with any change, not just with changes of each single field.
2. Unlike the field rule, the table rule does not trigger itself, even if a field is changed during evaluation of the rule expression. The field rule can cause ERROR 103 - Allowed DO nesting level exceeded, not so the table rule.

So moving the call of upate_table1() from the update trigger to the table rule is the solution I had in mind. But Before I close this with some final words, let's wait, if anybody else adds a different approach.

Bye, Olaf.

 

[NasibKalsi]

I do not how far from the truth I am, and late comer as well.

Spoiler

it is the same answer for both questions:
you can do 1. or 2.
1. remove the trigger
2. create a prg.

 

[Olaf Doschke]

Nasib,

have you understood the questions?

First question is why the error happens, not how to overcome it, that is the second question.
Removing the trigger only removes the error, but does not fulfill the intention.
Could you expand on how your second solution would work?

Bye, Olaf.

 

[Olaf Doschke]

I feel you have some idea, NasibKalsi, but it's simply doing the replace outside of the trigger, of course that'll do, but not automatic.


Let me round this up, doesn't mean closing the thread, of course.

Overall: Error 111 prevents endless recursion of update triggers.
The field validation rule can recurse and VFP then errors with error 103 - Allowed DO nesting level exceeded, so the fox inventors and developers didn't thought of this way. The table or record validation rule is only checked once, even if the check changes the record. I wonder what happens in which order, so I'll make a little experiment, but later.

This mechanism of using the rules also works nice, if you need the rules for normal rule checking, you just append the trigger code with AND. If the rule isn't fulfilled the trigger won't run, but then the whole update of the record in the DBF is rejected anyway. The only downside of the rule expressions is, they run with any record modification, no matter if insert (or append)), update (or replace), or delete and of course also for changes in grids or browse.

But if you need one or more computed fields like a timestamp field or any other even more complex computed value, you can make use of the rules.

What also will work is neither using trigger nor rule, but handle this in your business logic. VFP frameworks typically allow a default action in a beforesave hook method, where you also might apply the DATETIME() to some standard field in all your tables. And also the cursoradapter has such hook methods, which are triggered by TABLEUPDATE(): The BeforeCursorUpdate triggered by TABLEUPDATE() once before the TABLEUPDATE() does it's core job and BeforeUpdate before each single record is saved.

So the alternative solutions not using dbc trigger or record or field validation rules work, if you do things in "normal" code, but mainly best work with buffered data, as that introduces this single point of the commit. Another reason to turn on buffers and make use of them instead of scatter/gather memvars. Buffers also allow a central point of acting with standard code in a cursor adapter, if you inherit some base cursor adapter class, it has the advantage of being applied to all tables without defining a rule for each.

The downside of these solutions is, they only act, if you strictly go through your cursor adapter or other framework data access classes. Working on DBFs administratively you can overcome this checking and automatism with all pros and cons. If that is a reason for you to stay away from triggers, they (or the call of them) also can be removed temporarily. You may also use a rule expression like glADMIN OR (rule expression), so if glADMIN is .T. you can override any rule in that form, but glADMIN has to be present set as .F. for the normal behaviour, which makes access to DBFs hardly work via ODBC for any external programs, which again might be welcome or not.

And finally what happens in which order:

First let's change the stored proc:

Procedure update_table1()
   LParameters lcCalledBy
   
   Activate Screen
   ? lcCalledBy
   If "rule" $ lcCalledBy and Program(-1)<10
      Replace timestamp with Datetime()
   EndIf
   
   Return .T.

And then change the calls:
field validation rule for timestamp: update_table1("fieldrule")
record validation rule for table1: update_table1("tablerule")
update_trigger: update_table1("trigger")

Experiment #1:
Browsing the table, changing a timestamp and moving to another record the field rule runs 10 times, then the rule, which again triggers the field rule 10 times and finally the trigger runs. So in between the stack is emptied, before the record validation runs from stack level 0.

Experiment #2:
Changing a timestamp and tabbing within the same record, only the field rule runs 10 times.

Experiment #3:
REPLACE timestamp with DATETIME()
Interestingly enough, even though this only changes the timestamp value and doesn't move the record pointer, table validation rule and trigger also run.

Now setting MULTILOCKS ON And buffering to 5 (optimistic table buffering), rules still run this way, only the trigger is delayed until a TABLEUPDATE() commits changes to the DBF. That is quite important, you can't assume rule and trigger always run in conjunction.

And last not least: Using Program(-1) to limit the level, at which code runs is not a well solution, as the normal code might run on some program level already quite high and so the stored procedure might even not run at all.

Bye, Olaf.

 

[Mike Gagnon]

Mike
Throughout this weekend tests, are we actually debbuging one of your apps LOL?

Mike Gagnon

If you want to get the best response to a question, please check out FAQ184-2483 first.