Re: newbie question on triggers

godzilla_at_seuss.cc.utexas.edu (Jim Lyons) wrote:

>1) I have a table in which some columns have the "not null" clause.
 

>2) I have written an insert trigger which is to be fired -before- insertion.
 

>3) I have code in my trigger to test the not-null columns and, if the user
>has not provided data, write a message saying specifically which required
>field was omitted (or, at least, the first detected).
 

>4) Yet, when I insert data into that table, with null values in the required
>field, I get the Oracle error message and my trigger is not fired at all.
 

>I want to keep the field defined as "not null" for documentation reasons and
>to make -real- sure data integrity is observed (just in case someone with
>the necessary authority but not the necessary knowledge drops the trigger).
>I want the trigger to work for reason 3 above.
 

>Is there any way I can do this?

You can do the following (I only use the constraint, don't use a trigger). This is faster and gets the same net result:

SQL> create table abc
  2 ( x int constraint "abc.x cannot be null" check ( x is not null ),   3 y int );  

Table created.  

SQL> insert into abc values (null,0 );
insert into abc values (null,0 )
 *
ERROR at line 1:

ORA-02290: check constraint (TKYTE.abc.x cannot be null) violated
                             ^^^^^^^^^^^^^^^^^^^^^^^^^^

You get the name of the column in your error message. By using a Quoted identifier "abc.x cannot be null" I can preserve whitespace, preserve case, and get upto 32 characters per message (each message in any given schema must be unique however, if you user TABLE.COLUMN cannnot.... it will be unique tho).  

>--
>Jim Lyons | Operating Systems Specialist
>Computation Center | 512-475-9331
>University of Texas at Austin | godzilla_at_seuss.cc.utexas.edu
>http://www.cc.utexas.edu/~godzilla/jimlyons.html

Thomas Kyte
tkyte_at_us.oracle.com
Oracle Government Received on Thu Oct 12 1995 - 00:00:00 CET