Re: trigger help

A copy of this was sent to Kenny <karosenb_at_erols.com> (if that email address didn't require changing) On Mon, 15 Nov 1999 10:30:23 -0500, you wrote:

>I have a table called 'SHOW' that will soon be changed to allow an end user
>to associate a 'SHOW_ID' with a 'channel_ID' or an episode_ID (but not
>both). This means that show will contain nullable FKs to channel and
>network. Can I write a mutex trigger to require that one is set but not
>both?
>
>if so can somone provide an example?
>thanks
>-Kenny
>
>
>
>
>
>

use a check constraint, not a trigger for this:

tkyte_at_8.0> create table show( channel_id int, episode_id int ); Table created.

tkyte_at_8.0> alter table show add constraint   2 "Channel / episode must be null"
  3 check ( channel_id is NULL or episode_id is NULL ); Table altered.

tkyte_at_8.0> insert into show values ( null, null ); 1 row created.

tkyte_at_8.0> insert into show values ( 1, null ); 1 row created.

tkyte_at_8.0> insert into show values ( 1, 1 ); insert into show values ( 1, 1 )

            *
ERROR at line 1:
ORA-02290: check constraint (TKYTE.Channel / episode must be null) violated

tkyte_at_8.0> insert into show values ( null, 1 ); 1 row created.

It'll make it so that one and/or the other must be null.

--
See http://osi.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Mon Nov 15 1999 - 10:26:43 CST