Re: triggers on a group of records : DIFFICULT

Nedee & Co wrote:
>
> 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 think the logic to your problem has some problem if you want a database trigger
based on records of a database table:

Let's take your example:
  TABLE:    Temp_Tab {X, Y}

  Your condition is : sum of Y == 0 with same X:

then any single record DML:

          deletion with Y !=0 
  OR      insertion with Y !=0
  OR      updating  with another Y

will cause your condition to be violent. That means, you can only do:

1. Delete records with Y==0
2. Insert records with Y==0
3. Update records which keeps Y unchanged;

and thus, if table 'Temp_Tab' is empty from very beginning,   After some DML statements, Y column of all records should be ZERO.

If table 'Temp_Tab' has records from where your condition is established,
  You can write a trigger to check whether   

1. Delete records with Y==0
2. Insert records with Y==0
3. Update records which keeps Y unchanged;

Appearntly, your solution is not as above stated. I think you want to keep Y ==0
after the whole transaction is commited??!!

-- 
Hui, Kwok Miu (Xu, Guo Miao)         E-Mail: huikomix_at_dt.com.hk
Assistant MIS Manager                Tel   : (00852)2589 3369
Cho Yang (Hong Kong) Co., Ltd.       Fax   : (00852)2559 3049