If you can do it with declarative constraints
avoid the use of triggers because it is more
efficient to let the kernel makes the check.
Brian Peasland a écrit dans le message
<37C2CD1D.D72C167A_at_edcmail.cr.usgs.gov>...
>Why don't you use a trigger to check this? When a record is inserted,
>have the trigger check for your special character or the existence in
>another table. If the check passes, do nothing. If the check fails, drop
>the record from the table.
>
>HTH,
>Brian
>
>Michel Cadot wrote:
>>
>> You cannot do that: there is an integrity constraint or there is not.
>> But column link null verifies this constraint. So you can use
>>
>> create table bar
>> (
>> id number primary key,
>> special char(1) check (special is null or special='X'),
>> link number references foo,
>> constraint chk check ((special = 'X' and link is null)
>> or (special is null and link is not null))
>> );
>>
>> SQL>insert into foo values (1);
>>
>> 1 row created.
>>
>> SQL>insert into bar values (1,null,1);
>>
>> 1 row created. ==> Correct not special and references the row in foo
>>
>> SQL>insert into bar values (2,'X',null);
>>
>> 1 row created. ==> Correct special and no reference
>>
>> SQL>insert into bar values (3,'X',1);
>> ==> Wrong special and references
>> insert into bar values (3,'X',1)
>> *
>> ERROR at line 1:
>> ORA-02290: check constraint (.CHK) violated
>>
>> SQL>insert into bar values (4, null, 2);
>> ==> Wrong not special and references an inexistent row
>> insert into bar values (4, null, 2)
>> *
>> ERROR at line 1:
>> ORA-02291: integrity constraint (MMA$EP34087.SYS_C004499) violated - parent
key
>> not found
>>
>> ...
>>
>> Roy Smith a écrit dans le message ...
>> >I've got a table something like this:
>> >
>> >create table foo
>> >(
>> > id number primary key;
>> >);
>> >
>> >create table bar
>> >(
>> > id number primary key,
>> > link references foo
>> >);
>> >
>> >The problem is, there will be exceptional cases where I want to put a
>> >value in bar.link which doesn't exist in foo.id. What I'd like is
>> >something like this:
>> >
>> >create table bar
>> >(
>> > id number primary key,
>> > special char(1),
>> > link constraint check (special = 'X' or references foo)
>> >);
>> >
>> >Obviously, that's made-up syntax, but that's the idea I want. Any way to
>> >do something like this?
>> >
>> >The alternative would be to just add dummy "place-holder" entries in foo
>> >to handle these exceptional cases, but I'd rather not do that if I can
>> >avoid it.
>> >
>> >--
>> >Roy Smith <roy_at_popmail.med.nyu.edu>
>> >New York University School of Medicine
>> >
Received on Tue Aug 24 1999 - 15:06:16 CDT
