Re: constraint or trigger ?

Hi Serge,

1. To cancel a transaction via a trigger, just raise the application error as you suggested in step 1. When the exception is raised, the transaction will be aborted and all data rolled back.
2. I believe that the function is causing the problem, and that you cannot use a function in the check constraint. (Oracle does not know how long the function will take to execute and therefore, they disallow the function).
3. I would not suggest the use of option 3, you would still need a before trigger to manage the extra column, and this could be expensive.

Rgds
Andrew

Serge LUCAS wrote:

> I want to attach the following kind of constraint to a table which
> contains 2 external foreign keys 'ext1ID' & 'ext2ID':
> "There can be at most TWO lines referencing these two keys"
>
> - At first, I wanted to create a BeforeInsert trigger, but I dont' know
> how to make cancel the INSERT if the condition is not checked: will an
> exception be enough, or even if an exception occurs in a trigger, is the
> corresponding request however performed ?
>
> - The other solution would be to create a function which calculates
> (select count(*)) the number of lines that already have these two keys,
> and attach a constraint to MyTable which uses this function:
> Alter table MyTable add constraint MyConstraint check (f(x,y)<2);
>
> I have the Oracle error ORA-00904, which let me think I cant call a
> function in a constraint...
>
> I would like to avoid the third solution which consists in adding the
> following column to MyTable:
> checking NUMBER, UNIQUE, NOT NULL, IN (1,2);
>
> Could somebody help me ?
>
> --
> Serge LUCAS
> STERIA ATM/Eurocontrol
> BS-012
> Tel 01 69 88 74 15
> Fax 01 69 88 73 33
> Mel lua_at_eurocontrol.fr
Received on Tue Mar 30 1999 - 02:48:51 CST