Ann W. Harrison wrote:

>Andrea,
>
>What I would do is start by breaking the trigger into several pieces.
>What I've done below is create a procedure that generates version
>numbers, an insert trigger that checks the validity of the operation and
>one, an update trigger that checks validity, an insert/update trigger
>that performs the operation, and a delete trigger.
>
>

Thanks Ann, I'll try your code later.

>The procedure includes the insert into ElementVersion and the insert
>into or update of Element. As a general rule, a trigger on an operation
>that causes an update of another table is an invitation for deadlocks -
>which may be what you're seeing if your problem happens when several
>users are trying to change the view.
>
>

The problem happens even when a single user is connected to the database
server.

It happens while executing:
UPDATE "IngredientView" set "Acq" = 100 where ("Code" = 'ing01' or
"Code" = 'ing02')
(when both ingredient exists) (the same with more than two ingredients)

But does not happen while executing:
UPDATE "IngredientView" set "Acq" = 100 where "Code" = 'ing01'

>When you say "infinite loop", is the server using CPU time, or is it
>just not returning?
>
>

I t uses 100% of CPU time.

Andrea

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