How to discover in a trigger whether the NULL value was passed to a nullable column?

How can one figure out in a BEFORE INSERT Trigger, whether the user has passed the NULL - value for an null-able column explicit, or whether the user hasn't pass any value at all for that column.

In other words, I will be able to do following:

create table t (id, NUMBER, str VARCHAR开发者_如何学JAVA2(30) );

create Trigger trg 
befor INSERT on t
FOR earch row
DECLARE
BEGIN
IF :NEW.str is NULL
THEN
  IF <NULL-Value was explicit passed> THEN
    dbms_output.put_line( 'user has passed NULL');
  ELSE 
    dbms_output.put_line( 'user has passed nothing');
  END IF;
END IF;
END;
/

and then after

INSERT INTO t (id, str) VALUES (1, NULL);

I will see

> user has passed NULL

and after

INSERT INTO t (id) VALUES (1);

I will see

> user has passed nothing

There is no way in Oracle to programmatically distinguish a NULL deliberately passed from a defaulted NULL.

Apart from anything else, it would ascribe meaning to NULL when NULL defiantly remains the absence of meaning. Why would you want to do that?

@OneDayWhen sez:

"Codd himself proposed a second type of NULL to mean 'inapplicable'"

Yes, a "This page intentionally left blank" for databases. Apparently towards the end of his life Codd had identified four different types of NULL. 8-)

Other flavours of RDBMS distinguish between empty string and NULL, and perhaps some places have a convention that empty string means "inapplicable" and NULL means nothing. However, Oracle treats empty string as NULL and nobody has implemented Codd's NULL2. So I think it is dangerous to ascribe meaning to NULL in Oracle.

There are some database theorists who think NULL was one of Codd's big mistakes, and that goes double for different types of NULL. For an interesting alternative take on the topic try reading Chris Date's How To Handle Missing Information Without Using NULL

Why would you want to use a trigger for this, rather than a default value on the table column? Based on your comments to APC's answer, isn't this what you're trying to achieve?

create table t(id number, str varchar2(32) default 'default value');
create view v as select * from t;

insert into t(id) values (1);
insert into t(id, str) values (2,null);

insert into v(id) values (3);
insert into v(id, str) values (4,null);

select * from t;

ID                     STR                              
---------------------- -------------------------------- 
1                      default value                    
2                                                       
3                      default value                    
4