CODEWITHSUNDEEP
sqloracle-databasetriggers
Phaki
Phaki

Reputation: 315

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');

results table

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;

Upvotes: 1

Views: 567

Answers (1)

Littlefoot
Littlefoot

Reputation: 143103

This is what I meant: sample table first:

SQL> create table results (id number, question_id varchar2(20),
  2  answer_id varchar2(20), student_id varchar2(20));

Table created.

Trigger with no code, it just commits:

SQL> create or replace
  2  trigger results_insert
  3     after INSERT
  4     ON results
  5     FOR EACH ROW DECLARE
  6  BEGIN
  7     commit;
  8  end;
  9  /

Trigger created.

SQL> insert into results(id,question_id, answer_id, student_id) values (1,'qst1', 'answ2', 'stud3');
insert into results(id,question_id, answer_id, student_id) values (1,'qst1', 'answ2', 'stud3')
            *
ERROR at line 1:
ORA-04092: cannot COMMIT in a trigger
ORA-06512: at "SCOTT.RESULTS_INSERT", line 3
ORA-04088: error during execution of trigger 'SCOTT.RESULTS_INSERT'

Your trigger ("as is", I didn't modify anything):

SQL> create or replace
  2  trigger results_insert
  3     after INSERT
  4     ON results
  5     FOR EACH ROW DECLARE
  6  BEGIN
  7     IF INSERTING
  8     THEN
  9          MERGE INTO results rs
 10          USING (select :new.id id, :new.question_id qid, :new.answer_id aid, :new.student_id stid
 11          from dual) t1
 12            ON (t1.qid = rs.question_id AND t1.stid = rs.student_id)
 13          WHEN MATCHED THEN
 14            UPDATE SET rs.answer_id = t1.aid
 15          WHEN NOT MATCHED THEN
 16            INSERT (id, question_id, answer_id, student_id)
 17            VALUES (t1.id, t1.qid, t1.aid, t1.stid);
 18     END IF;
 19     COMMIT;
 20  END;
 21  /

Trigger created.

SQL> insert into results(id,question_id, answer_id, student_id) values (1,'qst1', 'answ2', 'stud3');
insert into results(id,question_id, answer_id, student_id) values (1,'qst1', 'answ2', 'stud3')
            *
ERROR at line 1:
ORA-04091: table SCOTT.RESULTS is mutating, trigger/function may not see it
ORA-06512: at "SCOTT.RESULTS_INSERT", line 5
ORA-04088: error during execution of trigger 'SCOTT.RESULTS_INSERT'

Mutating table, not invalid number error.

What you should do is to use MERGE in the first place. Abandon trigger, it brings nothing but pain. Just DROP it. Then:

Inserting a new row:

SQL> merge into results rs
  2    using (select 1 id, 'qst1' qid, 'answ2' aid, 'stud3' stid from dual) t1
  3    on (    t1.qid = rs.question_id
  4        and t1.stid = rs.student_id
  5       )
  6    when matched then update set
  7      rs.answer_id = t1.aid
  8    when not matched then insert (id, question_id, answer_id, student_id)
  9      values (t1.id, t1.qid, t1.aid, t1.stid);

1 row merged.

SQL> select * From results;

        ID QUESTION_ID          ANSWER_ID            STUDENT_ID
---------- -------------------- -------------------- --------------------
         1 qst1                 answ2                stud3

Matching question_id and student_id (so that merge performs update; check answer_id):

SQL> merge into results rs
  2    using (select 1 id, 'qst1' qid, 'answ9' aid, 'stud3' stid from dual) t1
  3    on (    t1.qid = rs.question_id
  4        and t1.stid = rs.student_id
  5       )
  6    when matched then update set
  7      rs.answer_id = t1.aid
  8    when not matched then insert (id, question_id, answer_id, student_id)
  9      values (t1.id, t1.qid, t1.aid, t1.stid);

1 row merged.

SQL> select * From results;

        ID QUESTION_ID          ANSWER_ID            STUDENT_ID
---------- -------------------- -------------------- --------------------
         1 qst1                 answ9                stud3

SQL>

Upvotes: 2

Related Questions