ORA-01722: invalid number when insert a row

I have an oracle trigger that gives me invalid number error when i want to insert a row. I think something wrong with one of the comparison operator. I tried to edit them but did not work. It could be easy for someone who spends more time with databases than me. My column types are: id, question_id, answer_id, student_id - number, varchar2, varchar2,varchar2.

create or replace
trigger results_insert
after INSERT
ON results
FOR EACH ROW DECLARE
BEGIN
IF INSERTING
THEN
MERGE INTO results rs
USING (select :new.id id, :new.question_id qid, :new.answer_id aid, :new.student_id stid
from dual) t1
ON (t1.qid = rs.question_id AND t1.stid = rs.student_id)
WHEN MATCHED THEN
UPDATE SET rs.answer_id = t1.aid
WHEN NOT MATCHED THEN
INSERT (id, question_id, answer_id, student_id)
VALUES (t1.id, t1.qid, t1.aid, t1.stid);
END IF;
COMMIT;
END;


My insert statement:

insert into results(id,question_id, answer_id, student_id) values (1,'qst1', 'answ2', 'stud3');




DDL:

--------------------------------------------------------
-- File created - Monday-April-19-2021
--------------------------------------------------------
--------------------------------------------------------
-- DDL for Table RESULTS
--------------------------------------------------------

CREATE TABLE "BIGFIVE"."RESULTS"
( "ID" NUMBER(10,0),
"ANSWER_ID" VARCHAR2(40 CHAR),
"QUESTION_ID" VARCHAR2(5 CHAR),
"STUDENT_ID" VARCHAR2(9 CHAR)
) SEGMENT CREATION IMMEDIATE
PCTFREE 10 PCTUSED 40 INITRANS 1 MAXTRANS 255 NOCOMPRESS LOGGING
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- DDL for Index SYS_C007820
--------------------------------------------------------

CREATE UNIQUE INDEX "BIGFIVE"."SYS_C007820" ON "BIGFIVE"."RESULTS" ("ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- DDL for Index UQ_RESULT
--------------------------------------------------------

CREATE UNIQUE INDEX "BIGFIVE"."UQ_RESULT" ON "BIGFIVE"."RESULTS" ("QUESTION_ID", "STUDENT_ID")
PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ;
--------------------------------------------------------
-- Constraints for Table RESULTS
--------------------------------------------------------

ALTER TABLE "BIGFIVE"."RESULTS" ADD CONSTRAINT "UQ_RESULT" UNIQUE ("QUESTION_ID", "STUDENT_ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;
ALTER TABLE "BIGFIVE"."RESULTS" MODIFY ("STUDENT_ID" NOT NULL ENABLE);
ALTER TABLE "BIGFIVE"."RESULTS" MODIFY ("QUESTION_ID" NOT NULL ENABLE);
ALTER TABLE "BIGFIVE"."RESULTS" MODIFY ("ANSWER_ID" NOT NULL ENABLE);
ALTER TABLE "BIGFIVE"."RESULTS" ADD PRIMARY KEY ("ID")
USING INDEX PCTFREE 10 INITRANS 2 MAXTRANS 255 COMPUTE STATISTICS
STORAGE(INITIAL 65536 NEXT 1048576 MINEXTENTS 1 MAXEXTENTS 2147483645
PCTINCREASE 0 FREELISTS 1 FREELIST GROUPS 1 BUFFER_POOL DEFAULT FLASH_CACHE DEFAULT CELL_FLASH_CACHE DEFAULT)
TABLESPACE "SYSTEM" ENABLE;
ALTER TABLE "BIGFIVE"."RESULTS" MODIFY ("ID" NOT NULL ENABLE);
--------------------------------------------------------
-- Ref Constraints for Table RESULTS
--------------------------------------------------------

ALTER TABLE "BIGFIVE"."RESULTS" ADD CONSTRAINT "RESULTS_ANSWERS_FK1" FOREIGN KEY ("ANSWER_ID")
REFERENCES "BIGFIVE"."ANSWERS" ("ANSW_ID") ENABLE;
ALTER TABLE "BIGFIVE"."RESULTS" ADD CONSTRAINT "RESULTS_QUESTIONS_FK1" FOREIGN KEY ("QUESTION_ID")
REFERENCES "BIGFIVE"."QUESTIONS" ("QST_ID") ENABLE;
ALTER TABLE "BIGFIVE"."RESULTS" ADD CONSTRAINT "RESULTS_STUDENT_DIM_FK1" FOREIGN KEY ("STUDENT_ID")
REFERENCES "BIGFIVE"."STUDENT_DIM" ("STU_ID") ENABLE;
--------------------------------------------------------
-- DDL for Trigger RESULTS_INSERT
--------------------------------------------------------

CREATE OR REPLACE TRIGGER "BIGFIVE"."RESULTS_INSERT"
after INSERT
ON results
FOR EACH ROW DECLARE
BEGIN
IF INSERTING
THEN
MERGE INTO results rs
USING (select :new.id id, :new.question_id qid, :new.answer_id aid, :new.student_id stid
from dual) t1
ON (t1.qid = rs.question_id AND t1.stid = rs.student_id)
WHEN MATCHED THEN
UPDATE SET rs.answer_id = t1.aid
WHEN NOT MATCHED THEN
INSERT (id, question_id, answer_id, student_id)
VALUES (t1.id, t1.qid, t1.aid, t1.stid);
END IF;
END;
/
ALTER TRIGGER "BIGFIVE"."RESULTS_INSERT" ENABLE;

Continue reading...