CODEWITHSUNDEEP
sqloracle-databasenull
user3075981
user3075981

Reputation: 13

Comparing dates with null value in trigger in Oracle 10

I am sorry if I am asking a question which was already answered, but I cannot find any answer on it. I am writing a trigger in Oracle 10, where I compare 2 dates, as numbers, but I have a problem, if the second value is null.

I have two tables: Herci (dat_umr_her:date) and filmy (rok_vyd:integer).

The problem is that dat_umr_her is sometimes NULL and then I cannot insert any new Herec because an application error is raised inside the if statement.

CREATE OR REPLACE TRIGGER "XVIKD00"."DATUM_UMRTIA_HEREC" 
  BEFORE INSERT OR UPDATE 
  ON HERCI
  FOR EACH ROW 
DECLARE 
  l_ROK_VYDANIA filmy.ROK_VYD%TYPE;
BEGIN 
  SELECT FILMY.ROK_VYD
    INTO l_ROK_VYDANIA 
    FROM FILMY
   WHERE FILMY.ID_FILM = :new.ID_HEREC;

  IF( extract( year from :new.dat_umr_her ) < l_ROK_VYDANIA )
  THEN
    RAISE_APPLICATION_ERROR(-2009,'Dátumy nie su v správnom časovom slede'); 
  END IF;
END;

Upvotes: 1

Views: 2660

Answers (1)

Jens Schauder
Jens Schauder

Reputation: 81882

Not sure where your exact problem is, but there are two basic approaches:

Either use nvl or coalesce to replace a NULL value with some default value (in your case probably a date way in the past or in the future.

Or if you want to use it in a comparison you might use is null

This might actually be what you need, judging from the comments

IF( 
    (:new.dat_umr_her is not null) 
    AND extract( year from :new.dat_umr_her ) < l_ROK_VYDANIA 
)

Upvotes: 1

Related Questions