Error at line 3/16: ORA-00942: table or view does not exist

Question

i get this error whenever i try to run this trigger when i try to assign a working schedule for an employee and when i assign the working schedule to this employee from the master table in the details table it should generate for me the working schedule details table based on the working schedule id in the working schedule page but i dont know how to do it and i need assistance TRIGGER:

CREATE OR REPLACE TRIGGER TRG_INSERT_EMPLOYEE_WORK_SCHEDULE
AFTER INSERT ON EMPLOYEE_WORK_SCHEDULE_testtt
FOR EACH ROW
DECLARE
    v_new_id NUMBER;
BEGIN
        
    -- Generate a new unique ID for each detail record,
    SELECT NVL(MAX(ID), 0) + 1 INTO v_new_id FROM EMPLOYEE_WORK_SCHEDULE_DETAIL_testtt;
    
    -- Inserting into detail table, modify the columns and values 
    INSERT INTO EMPLOYEE_WORK_SCHEDULE_DETAIL_testtt (ID, ...other columns...) 
    VALUES (v_new_id, ...other values...);
    
    -- You can add more logic if needed
END;
/

And This Is The DLL FOR both table

Master Table:

  CREATE TABLE "EMPLOYEE_WORKING_SCHEDULE_testtt" 
   (    "ID" NUMBER, 
    "EMPLOYEE_ID" NUMBER NOT NULL ENABLE, 
    "WORK_SCHEDULE_ID" NUMBER NOT NULL ENABLE, 
    "ASSIGNMENT_DATE" DATE NOT NULL ENABLE, 
     PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   ) ;

  ALTER TABLE "EMPLOYEE_WORKING_SCHEDULE_testtt" ADD CONSTRAINT "FK_EWS_EMPLOYEE" FOREIGN KEY ("EMPLOYEE_ID")
      REFERENCES "EMPLOYEES" ("EMPLOYEE_ID") ENABLE;
  ALTER TABLE "EMPLOYEE_WORKING_SCHEDULE_testtt" ADD CONSTRAINT "FK_EWS_WORK_SCHEDULE" FOREIGN KEY ("WORK_SCHEDULE_ID")
      REFERENCES "WORK_SCHEDULE_MASTER" ("ID") ENABLE;

  CREATE OR REPLACE EDITIONABLE TRIGGER "EMPLOYEE_WORKING_SCHEDULE_TESTTT_T" 
after
insert or update or delete on "EMPLOYEE_WORKING_SCHEDULE_testtt"
for each row
begin
    null;
end;
/
ALTER TRIGGER "EMPLOYEE_WORKING_SCHEDULE_TESTTT_T" ENABLE;

DETAILS TABLE:

  CREATE TABLE "EMPLOYEE_WORKING_SCHEDULE_DETAIL_testtt" 
   (    "ID" NUMBER, 
    "EMPLOYEE_WORKING_SCHEDULE_ID" NUMBER NOT NULL ENABLE, 
    "DAY" NUMBER NOT NULL ENABLE, 
    "DAY_MODEL" VARCHAR2(50 CHAR) NOT NULL ENABLE, 
    "SHIFT_DEFINITION_ID" NUMBER NOT NULL ENABLE, 
     PRIMARY KEY ("ID")
  USING INDEX  ENABLE
   ) ;

  ALTER TABLE "EMPLOYEE_WORKING_SCHEDULE_DETAIL_testtt" ADD CONSTRAINT "FK_EWSD_EWS" FOREIGN KEY ("EMPLOYEE_WORKING_SCHEDULE_ID")
      REFERENCES "EMPLOYEE_WORKING_SCHEDULE_testtt" ("ID") ENABLE;
  ALTER TABLE "EMPLOYEE_WORKING_SCHEDULE_DETAIL_testtt" ADD CONSTRAINT "FK_EWSD_SHIFT_DEFINITION" FOREIGN KEY ("SHIFT_DEFINITION_ID")
      REFERENCES "SHIFT_DEFINITIONS" ("ID") ENABLE;

Tried to run but it wont work

Answer 1

As you decided to enclose table and column names into double quotes, now you have to do it every time you reference those names, matching letter case.

In trigger, you didn't do it so it failed because table with that name really doesn't exist - in data dictionary, its (table's) name is stored in mixed case. By default, if you don't use double quotes, all identifiers are stored in uppercase, but Oracle lets you reference them any way you want.

I'd suggest you to

• drop tables
• remove all double quotes
• recreate tables
• create trigger

---

But, that's not all you did wrong. It seems that you really misspelled table name.

• create table suggests that table name is EMPLOYEE_WORKING_SCHEDULE_testtt, while
• trigger is created on EMPLOYEE_WORK_SCHEDULE_testtt

---

That's still not all: that's a row-level trigger and - in it - you're selecting from the same table:

AFTER INSERT ON EMPLOYEE_WORK_SCHEDULE_testtt      --> this
FOR EACH ROW
...
SELECT NVL(MAX(ID), 0) + 1 INTO v_new_id 
FROM EMPLOYEE_WORK_SCHEDULE_DETAIL_testtt;         --> this

That will lead to a mutating table error.

There's a way out (actually, two - using a package or a compound trigger), but - generally - that bad idea altogether. It might work in a single-user environment, but imagine 10, 20 or more users concurrently accessing that table, firing the trigger and all of them getting the same V_NEW_ID. That's a primary key column, you can't have duplicates.

What to do?

• if your database supports it, use an identity column
• if not, use a sequence
  • if database supports it, use it as column's default value
  • if not, create a before insert trigger which will put sequence next value into the ID column