Sql Plus Trigger Compilation Errors

I am trying to create a trigger to calculate a derived attribute on each insert command. However I am getting compilation errors, I dont know where is the problem. CREATE OR REPLA

Solution 1:

It's not the trigger, it's the data type. If you substract a date from another date, the result is an interval, not another date:

CREATETABLE dates (date1 DATE, date2 DATE, datediff DATE, numdiff NUMBER);
INSERTINTO dates (date1, date2) VALUES (sysdate, sysdate-1);

UPDATE dates SET numdiff = date1 - date2;
1rows updated

UPDATE dates SET datediff = date1 - date2;
SQL Error: ORA-00932: inconsistent datatypes: expected DATE got DATE JULIAN

So, if the trigger stores the interval in a number, it compiles:

CREATEOR REPLACE TRIGGER newtriggernum
  BEFORE INSERTON dates FOREACHROWBEGIN
  :new.numdiff := :new.date1 - :new.date2;
END;
/TRIGGER NEWTRIGGERNUM compiled

and if it stores the interval in a date, it doesn't:

CREATEOR REPLACE TRIGGER newtriggerdate
  BEFORE INSERTON dates FOREACHROWBEGIN
  :new.datediff := :new.date1 - :new.date2;
END;
/
Error(2,11): PL/SQL: ORA-00922: missing or invalid option

Solution 2:

CREATEOR REPLACE TRIGGER NewTrigger
BEFORE INSERTON Dates FOREACHROWBEGIN 
    :NEW.difference := :NEW.date1 - :NEW.date2;
End;
/