Re: DBA Prep -- Sample question

From: Jared Still <jared_at_valleynet.com>
Date: 1996/03/15
Message-ID: <4ic817$hem_at_alpine.valleynet.com>#1/1


jwingram_at_whale.st.usm.edu (Jonathan Wayne Ingram) wrote:

>: QUESTION:
>: A BEFORE INSERT trigger is created to do validation on a column created
>: as not NULL. The purpose of the trigger is to check the contains (sic) of
>: the column and display a message "Column cannot be NULL" if the column is
>: NULL. What will happen? (May have multiple answers)
 

>: A. The trigger will be fired and it will check value of the colmn and
>: return a message when the column is NULL.
>: B. The trigger will never be fired even when the column is NULL.
>: C. The message "Column cannot be NULL" will never be displayed.
>: D. The trigger is always fired whenever data is inserted into thie table.
 

>Steve,
>I am going to go out on a limb here and say that A is *incorrect*, because
>Oracle will parse the insert statement before the table is actually updated.
>B is obviously incorrect because if the statement parse is successful,
>the trigger will fire.
 

>I think that both C and D are correct. This is my best shot, since
>I am unaware of any constraints on which columns can be checked
>in a trigger.
 

>Jonathan

try this:

create table x ( a number null, b number null );

create or replace trigger xnull
before insert on x
for each row

	begin
		if :new.a is NULL
		then
			raise_application_error(-20000,'x.a may not be null');
		end if;
	end;

/

insert into x(b) values(1);

You will get an error when you do this.

Jared Still, Oracle DBA
RxNet, Division of Value Health
"All opinions are mine, not my employers" jared_at_valleynet.com Received on Fri Mar 15 1996 - 00:00:00 CET

Original text of this message