Re: Trigger issue

From: Thomas Kyte <tkyte_at_us.oracle.com>
Date: Tue, 10 Aug 1999 13:50:23 GMT
Message-ID: <37c02dc8.93087232@newshost.us.oracle.com>

A copy of this was sent to "wozi" <wozi_at_dhc.net> (if that email address didn't require changing) On Mon, 9 Aug 1999 22:06:12 -0700, you wrote:

>I want some suggestion on creating a trigger for a
>BEFORE INSERT OR UPDATE ON a table
>
>My table has 3 columns:
>col1 col2 col3
>
>Here is the business rule I am trying to enforce during insert or update:
>1. If all 3 columns are null, that's ok. No need for trigger.
>2. If all 3 columns are NOT null, that's ok. No no need for trigger.
>3. If one column is NOT null, then all 3 columns must be NOT null, then
>trigger
> must not permit insert or update.
>
>When replying, please copy tyc77_at_hotmail.com
>

While you could do this in a trigger -- the most performant option will be a check constraint. The rule of thumb is-- if you can do it declaritively, do it. declaritive integrity rules are much faster then triggers.

SQL> create table t

  2  ( x int,
  3    y int,
  4    z int,

  5 constraint MY_RULE
  6 check ( ( x is null and y is null and z is null ) OR   7 ( x is not null and y is not null and z is not null ) )   8 )
  9 /

Table created.

SQL>
SQL> insert into t values ( null, null, null );

1 row created.

SQL> insert into t values ( 1, 1, 1 );

1 row created.

SQL> insert into t values ( 1, null, 1 ); insert into t values ( 1, null, 1 )

*
ERROR at line 1:
ORA-02290: check constraint (TKYTE.MY_RULE) violated

SQL> insert into t values ( 1, null, null ); insert into t values ( 1, null, null )

*
ERROR at line 1:
ORA-02290: check constraint (TKYTE.MY_RULE) violated

SQL> insert into t values ( null, null, 1 ); insert into t values ( null, null, 1 )

*
ERROR at line 1:
ORA-02290: check constraint (TKYTE.MY_RULE) violated

--
See http://govt.us.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 Tue Aug 10 1999 - 08:50:23 CDT