delete and insert another table using trigger (FK key) oracle apex

Question

I have two schedules. I want to use trigger if an appointment is deleted from the (appointments) schedule and added to (archive_appointments)

CREATE TABLE "APPOINTMENTS" 
   (    "APPOINTMENTS_ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "PATIENT_FK" NUMBER, 
    "USER_FK" NUMBER, 
    "CLINIC_FK" NUMBER, 
    "DOCTOR_FK" NUMBER, 
    "APPOINTMENT_DATE" TIMESTAMP (6), 
    "APPOINTMENT_DETAILS" VARCHAR2(255 CHAR), 
    "ARCHIVE_FK" NUMBER, 
     CONSTRAINT "APPOINTMENTS_PK" PRIMARY KEY ("APPOINTMENTS_ID")
  USING INDEX  ENABLE
   ) ;

  ALTER TABLE "APPOINTMENTS" ADD CONSTRAINT "APPOINTMENTS_ARCHIVE_FK" FOREIGN KEY ("ARCHIVE_FK")
      REFERENCES "ARCHIVE_APPOINTMENTS" ("ARCHIVE_ID") ENABLE;
  ALTER TABLE "APPOINTMENTS" ADD CONSTRAINT "APPOINTMENTS_CLINIC_FK" FOREIGN KEY ("CLINIC_FK")
      REFERENCES "CLINICS" ("CLINIC_ID") ENABLE;
  ALTER TABLE "APPOINTMENTS" ADD CONSTRAINT "APPOINTMENTS_DOCTOR_FK" FOREIGN KEY ("DOCTOR_FK")
      REFERENCES "DOCTORS" ("DOCTOR_ID") ENABLE;
  ALTER TABLE "APPOINTMENTS" ADD CONSTRAINT "APPOINTMENTS_PATIENT_FK" FOREIGN KEY ("PATIENT_FK")
      REFERENCES "PATIENT" ("PATIENT_ID") ENABLE;
  ALTER TABLE "APPOINTMENTS" ADD CONSTRAINT "APPOINTMENTS_USERS_FK" FOREIGN KEY ("USER_FK")
      REFERENCES "USERS" ("USERS_ID") ENABLE;


  CREATE TABLE "ARCHIVE_APPOINTMENTS" 
   (    "ARCHIVE_ID" NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY MINVALUE 1 MAXVALUE 9999999999999999999999999999 INCREMENT BY 1 START WITH 1 CACHE 20 NOORDER  NOCYCLE  NOKEEP  NOSCALE  NOT NULL ENABLE, 
    "APPOINTMENTS_FK" NUMBER, 
    "PATIENT_FK" NUMBER, 
    "CLINIC_FK" NUMBER, 
    "DOCTOR_FK" NUMBER, 
    "ARCHIVE_DATE" DATE, 
    "ARCHIVE_STATUS" VARCHAR2(255), 
     CONSTRAINT "ARCHIVE_APPOINTMENTS_PK" PRIMARY KEY ("ARCHIVE_ID")
  USING INDEX  ENABLE
   ) ;

  ALTER TABLE "ARCHIVE_APPOINTMENTS" ADD CONSTRAINT "ARCHIVE_APPOINTMENTS_FK" FOREIGN KEY ("APPOINTMENTS_FK")
      REFERENCES "APPOINTMENTS" ("APPOINTMENTS_ID") ENABLE;

Trigger:

  CREATE OR REPLACE EDITIONABLE TRIGGER "DELETE_AND_INSERT_TRIGGER" 
AFTER DELETE ON APPOINTMENTS 
FOR EACH ROW 
BEGIN 
   -- Step 1: Delete the corresponding record from the target table. 
   DELETE FROM APPOINTMENTS 
   WHERE APPOINTMENTS_id = :OLD.APPOINTMENTS_id; 
 
   -- Step 2: Insert the deleted data into the target table. 
   INSERT INTO archive_appointments  
   (appointments_fk, patient_fk, clinic_fk, doctor_fk, archive_date ,archive_status)   
    VALUES  
   (:old.appointments_id, :old.patient_fk, :old.clinic_fk, :old.doctor_fk, SYSDATE, 'DONE'); 
END;
/

But alert error is : ORA-04098: trigger '********.APPOINTMENTS_T' is invalid and failed re-validation

i don't have any solution

Answer 1

I removed all foreign key constraints for this example:

• partially because parent tables don't exist (in my schema, and you didn't post their create table statements),
• partially because they are invalid (there's no point in creating foreign key from archive_appointments to appointments as there's nothing to reference once you delete row from appointments)

As of trigger: don't delete from table that's already being affected by that operation; Oracle will do it automatically.

So: tables first:

SQL> CREATE TABLE appointments
  2  (
  3     appointments_id       NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  4     patient_fk            NUMBER,
  5     user_fk               NUMBER,
  6     clinic_fk             NUMBER,
  7     doctor_fk             NUMBER,
  8     appointment_date      TIMESTAMP (6),
  9     appointment_details   VARCHAR2 (255 CHAR),
 10     archive_fk            NUMBER,
 11     CONSTRAINT appointments_pk PRIMARY KEY (appointments_id) USING INDEX ENABLE
 12  );

Table created.

SQL> INSERT INTO appointments (patient_fk)
  2       VALUES (100);

1 row created.

SQL> CREATE TABLE archive_appointments
  2  (
  3     archive_id        NUMBER GENERATED BY DEFAULT ON NULL AS IDENTITY,
  4     appointments_fk   NUMBER,
  5     patient_fk        NUMBER,
  6     clinic_fk         NUMBER,
  7     doctor_fk         NUMBER,
  8     archive_date      DATE,
  9     archive_status    VARCHAR2 (255),
 10     CONSTRAINT archive_appointments_pk PRIMARY KEY (archive_id) USING INDEX ENABLE
 11  );

Table created.

Trigger:

SQL> CREATE OR REPLACE TRIGGER delete_and_insert_trigger
  2     AFTER DELETE
  3     ON appointments
  4     FOR EACH ROW
  5  BEGIN
  6     INSERT INTO archive_appointments (appointments_fk,
  7                                       patient_fk,
  8                                       clinic_fk,
  9                                       doctor_fk,
 10                                       archive_date,
 11                                       archive_status)
 12          VALUES (:old.appointments_id,
 13                  :old.patient_fk,
 14                  :old.clinic_fk,
 15                  :old.doctor_fk,
 16                  SYSDATE,
 17                  'DONE');
 18  END;
 19  /

Trigger created.

Testing:

SQL> SELECT appointments_id, patient_fk FROM appointments;

APPOINTMENTS_ID PATIENT_FK
--------------- ----------
              1        100

SQL> SELECT archive_id, appointments_fk, patient_fk, archive_date, archive_status FROM archive_appointments;

no rows selected

Delete row from appointments:

SQL> DELETE FROM appointments WHERE patient_fk = 100;

1 row deleted.

New tables' contents:

SQL> SELECT appointments_id, patient_fk FROM appointments;

no rows selected

SQL> SELECT archive_id, appointments_fk, patient_fk, archive_date, archive_status FROM archive_appointments;

ARCHIVE_ID APPOINTMENTS_FK PATIENT_FK ARCHIVE_DATE        ARCHIVE_STATUS
---------- --------------- ---------- ------------------- --------------------
         1               1        100 25.10.2023 14:30:42 DONE

SQL>