Re: Help - Funny Constraint

From: Jurij Modic <jmodic_at_src.si>
Date: Fri, 28 Aug 1998 12:46:27 GMT
Message-ID: <35e6a1a2.17634004@news.siol.net>

On Fri, 28 Aug 1998 20:41:13 +1000, Kevin Seneviratne <kevins_at_connect.com.au> wrote:

>Hi All,
>I want to enforce the following.
>
>Table C1 has two fields F1, F2
>I want to prevent the update of field F1 if the value of F2 exists
>in another table C2.
>So if
>
>C1.F1 C1.F2
>1 11
>2 12
>
>and
>
>C2.FF
>11
>
>then the value 1 in C1 shou1d not be updatable but the value 2 should be (as
>there is no value of 12 in C2).
>
>I can use a trigger but how can I stop the update from happenning based on a
>value the trigger works out ?

If the value exists in C2 then raise an unhandeled exception in a trigger to prevent the update. Here is an example (tables filled with your example data):

SQL> CREATE OR REPLACE TRIGGER trigg1
  2 BEFORE UPDATE OF f1 ON c1
  3 FOR EACH ROW
  4 DECLARE
  5 dummy NUMBER;
  6 BEGIN
  7 SELECT COUNT(1) INTO dummy FROM c2 WHERE :old.f2 = ff;   8 IF dummy != 0 THEN

  9      -- this will raise an exception NO_DATA_FOUND
 10      SELECT 1 INTO dummy FROM dual WHERE 1=2;

 11 END IF;
 12 END;
 13 /

Trigger created.

SQL> UPDATE c1 SET f1=102 WHERE f2=12;

1 row updated.

SQL> UPDATE c1 SET f1=101 WHERE f2=11;
UPDATE c1 SET f1=101 WHERE f2=11

       *
ERROR at line 1:

ORA-01403: no data found
ORA-06512: at "SCOTT.TRIGG1", line 7
ORA-04088: error during execution of trigger 'SCOTT.TRIGG1'

>Altenatively can I somehow use a CHECK_CONSTRAINT ?

No, check constraint can't help you here.

>Thanks v. much
>
>Kevin.

HTH,
--
Jurij Modic <jmodic_at_src.si>
Certified Oracle7 DBA (OCP)

---

The above opinions are mine and do not represent any official standpoints of my employer Received on Fri Aug 28 1998 - 07:46:27 CDT