Oracle APEX before insert trigger issue-CodePudding

I am trying to create a before insert trigger in Oracle Application Express to fill out the timetaken field by calculating enddate - startdate that the user will enter in the web application. The table looks like this:

Column Name Data Type
ID NUMBER
STARTDATE TIMESTAMP(6)
ENDDATE TIMESTAMP(6)
TIMETAKEN TIMESTAMP(6)

What I am trying to do is this:

create or replace trigger "TESTS_T1"
before
insert or update on "TESTS"
for each row
BEGIN
INSERT INTO TESTS VALUES (id, :new.startdate, :new.enddate, new:timetaken:= :new.enddate - :new.startdate);
END;

But I am getting this error when I try to enter a row:

error ORA-04098: trigger 'MAIN.TESTS_T1' is invalid and failed re-validation

Do you have any idea what I am doing wrong here? Thanks in advance.

CodePudding user response：

Hm, not exactly like that. When you subtract two timestamps, you don't get yet another timestamp as result (which is what timetaken's datatype suggests), but interval day to second.

Apart from that, trigger should contain only the calculation - all the other columns are inserted (or updated) in "main" transaction.

SQL> CREATE TABLE tests
  2  (
  3     id          NUMBER,
  4     startdate   TIMESTAMP (6),
  5     enddate     TIMESTAMP (6),
  6     timetaken   INTERVAL DAY TO SECOND    --> this
  7  );

Table created.

SQL> CREATE OR REPLACE TRIGGER trg_biu_tests
  2     BEFORE INSERT OR UPDATE
  3     ON tests
  4     FOR EACH ROW
  5  BEGIN
  6     :new.timetaken := :new.enddate - :new.startdate;
  7  END;
  8  /

Trigger created.

Testing:

SQL> INSERT INTO tests (id, startdate, enddate)
  2       VALUES (1, SYSTIMESTAMP, SYSTIMESTAMP - 2);

1 row created.

Result:

SQL> SELECT * FROM tests;

        ID STARTDATE                      ENDDATE                        TIMETAKEN
---------- ------------------------------ ------------------------------ ------------------------------
         1 06.07.22 12:24:48,665450       04.07.22 12:24:48,000000       -02 00:00:00.665450

Or:

SQL> SELECT id,
  2         EXTRACT (DAY FROM timetaken) days,
  3         EXTRACT (HOUR FROM timetaken) hours,
  4         EXTRACT (MINUTE FROM timetaken) minutes,
  5         EXTRACT (SECOND FROM timetaken) seconds
  6    FROM tests;

        ID       DAYS      HOURS    MINUTES    SECONDS
---------- ---------- ---------- ---------- ----------
         1         -2          0          0    -,66545

SQL>