Wrong update on Trigger on Oracle

Question

I have the following problem at Trigger,

When there is an INSERT in table TB_DJE_PREPUBLICACAO, it has to be checked if the column DT_DISPONIBILIZACAO is null, if it is and if the column DT_PREPUBLICACAO is with the current date, the DT_DISPONIBILIZACAO has to be filled with the current date and set with the time: 20:00.

Trigger:

CREATE OR REPLACE TRIGGER TRG_ATUALIZA_DTDISPONIBILIZAC
BEFORE INSERT ON TB_DJE_PREPUBLICACAO
    FOR EACH ROW
    WHEN (NEW.DT_DISPONIBILIZACAO IS NULL)
BEGIN
    IF INSERTING THEN
        :NEW.DT_DISPONIBILIZACAO := TO_DATE(sysdate || ' 20:00','DD/MM/RR HH24:MI');
    END IF;
END;
/

Follow the undesirable effect in line 7:

ID_PREPUBLICACAO DT_PREPUBLICACAO DT_DISPONIBILIZACAO

1 14/09/2017 10:00 14/09/2017 10:00
2 14/09/2017 11:00 14/09/2017 11:00
3 14/09/2017 12:00 14/09/2017 14:00
4 14/09/2017 18:00 14/09/2017 20:00
5 14/09/2017 09:28 14/09/2017 21:03
6 14/09/2017 08:51 14/09/2017 20:00
7 15/09/2017 08:13 14/09/2017 20:00

In row 7, the column DT_DISPONIBILIZACAO was left with the current date, and nothing should have been done, since it is the application that is responsible for filling this column, if the column DT_PREPUBLICACAO is later than the current date.

So the problem always occurs when the DT_PREPUBLICACAO date is later than the current date. How can I resolve this?

Answer 1

The solution would be something like this:

CREATE OR REPLACE TRIGGER TRG_ATUALIZA_DTDISPONIBILIZAC
BEFORE INSERT ON TB_DJE_PREPUBLICACAO
    FOR EACH ROW
    WHEN (NEW.DT_DISPONIBILIZACAO IS NULL)
BEGIN
    IF INSERTING THEN
        :NEW.DT_DISPONIBILIZACAO := (trunc(sysdate) + (20/24));
    END IF;
END;