No matter what data I insert into my table, the date stays the same at 03.01.2022. How can I fix this?

Question

I created a primitive table in which I can store date, time, the id of a patient and the id of an employee.

CREATE TABLE Squedule (
id INTEGER,
datee DATE NOT NULL,
timee CHAR(5),
patientId CHAR(10),
employeeId CHAR(10),
CONSTRAINT squedule_pk PRIMARY KEY (kennzahl),
CONSTRAINT squedule_fkSprstdh FOREIGN KEY (employeeId) REFERENCES Sprechstdhilfe,
CONSTRAINT squedule_fkPatientIn FOREIGN KEY (patientId) REFERENCES PatientIn);

------------------I insert the data like so
INSERT INTO squedule(datee,timee,patientid,employeeid) VALUES('02.02.3000','16:43',8137770103,3146213220);

------------------This is the trigger for the id
CREATE TRIGGER newSquedule BEFORE INSERT ON Squedule
FOR EACH ROW
    BEGIN
        SELECT auto_increment_pk.NEXTVAL
        INTO :new.id
        FROM dual;
    END;

This worked just fine until I noticed that no matter what data I try to insert for the datee value it always ends up with '03.01.2022' in the table for every single insert.
I dropped all tables and created them again a few times, because a few system_sequences got created, I guess because of an Identity I use in another table. But they did not get deleted with dropping every single table. Maybe one of them is triggering something. What can I do to solve this problem? I am clueless...

Answer 1

I can't reproduce what you're saying.

I removed foreign keys from the table (as I didn't feel like creating tables you reference).

SQL> ALTER SESSION SET NLS_DATE_FORMAT = 'dd.mm.yyyy hh24:mi:ss';

Session altered.

SQL> CREATE SEQUENCE auto_increment_pk;

Sequence created.

SQL> CREATE TABLE squedule
  2  (
  3     id           INTEGER,
  4     datee        DATE NOT NULL,
  5     timee        CHAR (5),
  6     patientid    CHAR (10),
  7     employeeid   CHAR (10),
  8     CONSTRAINT squedule_pk PRIMARY KEY (id)
  9  );

Table created.

SQL> CREATE OR REPLACE TRIGGER newsquedule
  2     BEFORE INSERT
  3     ON squedule
  4     FOR EACH ROW
  5  BEGIN
  6     :new.id := auto_increment_pk.NEXTVAL;
  7  END;
  8  /

Trigger created.

Inserts:

SQL> INSERT INTO squedule (datee,
  2                        timee,
  3                        patientid,
  4                        employeeid)
  5       VALUES ('02.02.3000',
  6               '16:43',
  7               8137770103,
  8               3146213220);

1 row created.

But, the way you put it, I can insert anything into the timee column, including such a rubbish:

SQL> INSERT INTO squedule (datee,
  2                        timee,
  3                        patientid,
  4                        employeeid)
  5       VALUES ('25.02.3000',
  6               'xy:83',
  7               8137770103,
  8               3146213220);

1 row created.

Result:

SQL> SELECT * FROM squedule;

        ID DATEE               TIMEE PATIENTID  EMPLOYEEID
---------- ------------------- ----- ---------- ----------
         1 02.02.3000 00:00:00 16:43 8137770103 3146213220 --> 02.02.3000, not 03.01.2022
         2 25.02.3000 00:00:00 xy:83 8137770103 3146213220 --> invalid time value
                               
SQL>

---

I suggest you abandon what you're doing and use only one column whose datatype is DATE as - in Oracle - it contains both date and time component. Something like this:

SQL> DROP TABLE squedule;

Table dropped.

SQL> CREATE TABLE squedule
  2  (
  3     id           INTEGER,
  4     datee        DATE NOT NULL,
  5     patientid    CHAR (10),
  6     employeeid   CHAR (10),
  7     CONSTRAINT squedule_pk PRIMARY KEY (id)
  8  );

Table created.

SQL> CREATE OR REPLACE TRIGGER newsquedule
  2     BEFORE INSERT
  3     ON squedule
  4     FOR EACH ROW
  5  BEGIN
  6     :new.id := auto_increment_pk.NEXTVAL;
  7  END;
  8  /

Trigger created.

Insert:

SQL> INSERT INTO squedule (datee, patientid, employeeid)
  2          VALUES (TO_DATE ('02.02.3000 14:43', 'dd.mm.yyyy hh24:mi'),
  3                  8137770103,
  4                  3146213220);

1 row created.

SQL> SELECT * FROM squedule;

        ID DATEE               PATIENTID  EMPLOYEEID
---------- ------------------- ---------- ----------
         3 02.02.3000 14:43:00 8137770103 3146213220

SQL>