Re: Help - Funny Constraint

Hi,

To prevent the action recognised, maybe a RAISE_APPLICATION_ERROR(...) will work when included in the PL/SQL block of your trigger.

Jurij Modic wrote in message <35e6a1a2.17634004_at_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 Mon Aug 31 1998 - 06:58:16 CDT