Wrong trigger update in Oracle

Question

Wrong trigger update in Oracle

Navigation

#1 by (0 votes)

1

I have the following problem in the trigger,

When there is an INSERT in the 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_DISPONIBILIZAOO has to be filled with the current date and set with the hour : 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;
/

The undesired effect on line 7 follows:

PR_PREPUBLICATION DT_PREPUBLICATION DT_DISPONIBILIZATION

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 9/15/2017 08:13 9/14/2017 8:00 PM

In column 7, the column DT_DISPONIBILIZACAO remained with the current date, and nothing should have been done, since it is the application that is responsible for filling that 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?

oracle trigger

asked by anonymous 14.09.2017 / 20:19

1 answer

How to use $ .on in pure JavaScript: "$ (...) .on (event, selector, function)"? I can not login with google in my Release app

score 0 · Answer 1

The solution would look 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;