Trigger fails with ORA-01858: a non-numeric character was found where a numeric was expected-CodePudding

I am trying to create a trigger on a table (TABLE_1) which detects if a column (ENTERED_DATE) has been changed then update another table (TABLE_2) to set the column (ENTRY_DATE) to the newly changed date on TABLE_1.

Here's what I have:

create or replace trigger TRG_NAME
  after insert or update or delete on TABLE_1
  for each row 

  begin 
    if updating then 
        if nvl(:old.ENTERED_DATE, '*') != nvl(:new.ENTERED_DATE, '*') then update TABLE_2 set ENTRY_DATE = :new.ENTERED_DATE where ID = :old.ID; end if; 
    end if; 
end TRG_NAME; 
/

The trigger compiles successfully but when I update the column value which is of type date on both tables I get the following error:

ORA-01858: a non-numeric character was found where a numeric was expected

I am confused since both fields on both tables are a DATE datatype so why am I getting this error?

CodePudding user response：

The problem is in your nvls:

if nvl(:old.ENTERED_DATE, '*') != nvl(:new.ENTERED_DATE, '*') then ...

Both expressions in nvl need to retun the same datatype ; otherwise, if one value is not null and the other is, you end up comparing string '*' with a date - this operation is not allowed.

I understand that you are looking for a null-wise comparison ; in standard SQL we would use operator IS DISTINCT FROM - but Oracle does not supports it.

If you have a date that you know for sure never appears in your data, you can do:

nvl(:old.ENTERED_DATE, date '1990-01-01') != nvl(:new.ENTERED_DATE, date '1990-01-01')

Otherwise you need to enumerate the logical combinations :

 :old.ENTERED_DATE != :new.ENTERED_DATE
 or (:old.ENTERED_DATE is null and :new.ENTERED_DATE is not null)
 or (:new.ENTERED_DATE is null and :old.ENTERED_DATE is not null)