ORA-04098 is invalid and failed re-validation

Question

I wrote the below trigger and it was compiled successfully.

create or replace trigger after_update_datetable
after update on date_table
for each row
begin
   if(TRUNC(:new.end_date) - TRUNC(:new.start_date) > 90) THEN
    UPDATE date_table set END_DATE = :old.END_DATE, START_DATE =  :old.START_DATE;
   END IF;
END;

However when I performed the below update statement I got this error

update date_table set end_date = sysdate, start_date = sysdate-100;

trigger failed -ORA-04098 is invalid and failed re-validation.

Any help is appreciated. Thanks

Answer 1

you are updating the same table from the trigger, that is not allowed :

if(TRUNC(:new.end_date) - TRUNC(:new.start_date) > 90) THEN
UPDATE date_table set END_DATE = :old.END_DATE, START_DATE =  :old.START_DATE;
END IF;

you better use a trigger for BEFORE instead of AFTER, and update the values of the record :NEW see this answer, it will be useful for you