insert trigger help

[Beantree]

Here's my insert trigger:

CREATE OR REPLACE TRIGGER TG_MONEY_BAL_ARC_INS
BEFORE INSERT ON T_MONEY_BAL_ARC
FOR EACH ROW
DECLARE
tmpVar DATE;
tmpVar2 number;

BEGIN
tmpVar := NULL;

if NVLOLD.RECORD_DATE,SYSDATE) > TRUNC(SYSDATE) THEN
Select HEADER_DATE into tmpVar FROM T_LOAD_DATE_CTRL;
:NEW.RECORD_DATE := tmpVar;
END IF;

Select S_MONEY_BAL_ARC_ID.NEXTVAL into tmpVar2 FROM dual;
:NEW.ID := tmpVar2;


EXCEPTION
WHEN OTHERS THEN
Null;
END ;

I am having problems with the if then statement.

The idea is that if the RECORD_DATE field is null, to lookup the value in T_LOAD_DATE_CTRL table, which only has one record in it. Otherwise to use the RECORD_DATE field being inserted. RECORD_DATE will always be the previous day of SYSDATE.

However, no matter if the insert has a RECORD_DATE value, the if statement still evaluates to true, and looks up the value from T_LOAD_DATE_CTRL.

I cannot figure out why.

Please help.

Thanks

 

[carp]

When inserting, all :OLD.* values are null. Consequently,
NVLOLD.RECORD_DATE,SYSDATE) > TRUNC(SYSDATE) will evaluate to TRUE any time after midnight.
Try changing ld to :new.

 

[Beantree]

of course.

Thanks