Re: triggers on a group of records : DIFFICULT

From: <Solomon.Yakobson_at_entex.com>
Date: 1997/01/10
Message-ID: <852922061.13225@dejanews.com>#1/1

All you need is a statement level trigger. Statement level trigger is executed before or after the whole statement, versus row level trigger which is executed before or after every row involved in the statement. Therefore statement level trigger is never causing "mutating" state.
Below is an example that might help you:

SQL> CREATE TABLE xyz(x VARCHAR2(1),y NUMBER)   2 /

Table created.

SQL> CREATE OR REPLACE TRIGGER xyz_t
  2 AFTER INSERT OR UPDATE OR DELETE
  3 ON xyz

  4     BEGIN
  5       FOR Rec IN (SELECT  x
  6                     FROM  xyz
  7                     GROUP BY x
  8                     HAVING SUM(y) <> 0
  9                  ) LOOP
 10         RAISE_APPLICATION_ERROR(-20001,
 11                    'Sum of y for x = '||rec.x||' is not 0.');
 12       END LOOP;

 13 END;
 14 /

Trigger created.

SQL> INSERT INTO xyz ( SELECT 'A',1 FROM Dual

  2                   UNION ALL
  3                    SELECT 'A',-1 FROM Dual
  4                  )

  5 /

2 rows created.

SQL> INSERT INTO xyz VALUES('B',1)
  2 /
INSERT INTO xyz VALUES('B',1)

            *
ERROR at line 1:

ORA-20001: Sum of y for x = B is not 0.
ORA-06512: at "SNY001.XYZ_T", line 3
ORA-04088: error during execution of trigger 'SNY001.XYZ_T'

This solution is not the best one in terms of performance if table xyz is a large table and/or x has high cardinality. In that case you could consider packaged PL/SQL table and 3 triggers. First trigger is statement level trigger with BEFORE option. It simply initializes packaged PL/SQL table, therefore any leftovers from previous statements issued by the user are discarded (I hope this answers your "Second" concern).
Second trigger is row level (FOR EACH ROW) trigger with AFTER option. All it does is adding distinct :old.x and :new.x to PL/SQL table.
The third trigger is statement level trigger with AFTER option. It validates SUM(y) for every x in PL/SQL table and raises application error if SUM(y) is not 0.

Solomon.

In article <5b370d$dnh$1_at_news.be.innet.net>,   nedee_at_club.innet.be (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 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).

-------------------==== Posted via Deja News ====-----------------------
      http://www.dejanews.com/     Search, Read, Post to Usenet

Received on Fri Jan 10 1997 - 00:00:00 CST