How to check numerical dependencies in PL/SQL?

From: Jens M. Felderhoff <jmf_at_informatik.uni-koblenz.de>
Date: 1997/08/17
Message-ID: <5t7349$lpn$3_at_newshost.uni-koblenz.de>#1/1

[Quoted] I have to check numerical dependencies (ND) in PL/SQL.

An ND is a dependency of the form

        <attribute-set-1> -[<min>,<max>]-> <attribute-set-2>

and expresses the constraint, that in tables with a given value of the columns in <attribute-set-1> there may be between <min> and <max> distinct values of the columns in <attr-set-2>.

E,g., we have a relation rel with attributes a1, a2, a3, and a4 and the ND a1 -[2,3]-> a2,a3.

[Quoted] Here is a pseudo-PL/SQL fragment from a trigger that tries to check the constraint:

[...]

 SELECT COUNT(*)
 INTO valcount
 FROM SELECT DISTINCT a2,a3

      FROM rel
      WHERE a1 = :new.a1;

 IF valcount NOT BETWEEN 2 AND 3
 THEN raise_application_error(-20001, "ND violation");  END IF;
[...]

However, it is only possible to specify a table reference in the SELECT-INTO statement instead of a subquery.

Does anybody have an idea how such a constraint check could be implemented?

N.B.: The usage of cursors or views should be avoided if possible.

Tschuess

-- 
 Jens M. Felderhoff     e-mail: jmf_at_infko.uni-koblenz.de
                                jmf_at_scary.mrnetz.rhein-zeitung.de
                          Fido: 2:2454/95.10_at_fidonet.org
                          NeST: 90:400/601.10_at_nest.ftn

Received on Sun Aug 17 1997 - 00:00:00 CEST