Ann W. Harrison wrote:

>Andrea Sansottera wrote:
>
>
>
>>There could be any reason which causes the engine to confuse
>>"Ingredient" with "IngredientView", only when updating 2 or more rows?
>>Inserting into "IngredientView" obviously would couse an infinite
>>recursion in the trigger chain.
>>
>>
>
>Ah. Excellent guess. If the new row you store into Ingredient is
>visible in the view, you'd get the behavior you're seeing. For example,
>this statement stores an infinite number of records in Firebird,
>assuming there's a record in table A:
>
> insert into a (f1, f2, f3)
> select (f1 + 1, f2 + 1, f3 + 1) from a
>
>
>It's a bug of long standing - since the first published SQL standard.
>Before there was a SQL standard, it was a quirk. The behavior has been
>part of InterBase/Firebird since 1985.
>

I was born in 1985! :)

The only thing that I don't understand is why updating a single view row
(where "Code" = 'ing01') does not cause that infinite recursion.

>There's probably some way to
>cheat by adding a field, visible through the view, that you can test and
>change so the trigger can distinguish between view changes and native
>table changes.
>
>

Maybe something like this (not real PL/SQL):

BEFORE INSERT OR UPDATE, POSITION 0:
IF (new."FakeInsert" is not null) THEN EXIT;
"DO USEFUL STUFF"
INSERT INTO "Ingredient" (..., "FakeInsert") VALUES (..., 1)
"DO OTHER USEFUL STUFF"

BEFORE INSERT OR UPDATE OR DELETE, POSITION 1:
new."FakeInsert" = null;

What do you think about this workaround?

Thank you Ann, you have been kind to me.

--
Andrea Sansottera
UGIdotNET [Italian] http://www.ugidotnet.org
My weblog [Italian] http://blogs.ugidotnet.org/andrew/