triggers on a group of records : DIFFICULT

Hi Oracle-fans

I am having a really difficult problem.

We have the following situation :
we should do a control on a certain group of records in a certain table.
Let us simplify the example and say that we have a table A, with two columns : X en Y. For the sake of database consistency the sum of column
Y for records which have the same X must always be zero.

I would like to implement this with database triggers, instead of in every program that manipulates data in this table. The problem with database triggers is in two ways.
First : within the triggers you can't select the rows in A with the same X, because of an Oracle Error 4091 (Mutating table thing). Second : when working with a package stack you still have the problem that the user inserts a record (the triggers pushes it onto the stack),
then the user quits the application, the records stays there.

I turned this problem round and round but I don't find a satisfying solution. All ideas are welcome in the group or else privatly.

Thx.

Peter.
Belgium.

PS : the reason why I really would like to work with the triggers is that I don't need to rewrite all my form-applications. I could well work with an abstract datatype and a well-defined package around it, but it would mean that I can't use form-applications (in a more or less normal way). Received on Thu Jan 09 1997 - 00:00:00 CST