oracle 使用触发器(FK键)删除和插入另一个表

wnrlj8wa 于 2023-11-17 发布在 Oracle

关注(0)|答案(1)|浏览(130)

我有两个计划。如果从（appointments）计划中删除了约会并将其添加到（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;

字符串
触发器：

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;
/

型
但警报错误为：ORA-04098：触发器“*.APPOINTMENTS_T”无效，重新验证失败
我没有办法

oracle

来源：https://stackoverflow.com/questions/77359394/delete-and-insert-another-table-using-trigger-fk-key-oracle-apex

1条答案

按热度按时间

zdwk9cvp1#

我删除了这个例子中的所有外键约束：

部分原因是父表不存在（在我的模式中，并且您没有发布它们的create table语句），
部分原因是它们无效（从archive_appointments到appointments创建外键没有意义，因为一旦从appointments删除行，就没有什么可引用的了）

从触发器开始：不要从已经受到该操作影响的表中删除; Oracle将自动执行此操作。
所以：首先是table：

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.

字符串
触发器：

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.

型
测试：

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

型
从appointments删除行：

SQL> DELETE FROM appointments WHERE patient_fk = 100;

1 row deleted.

型
新表格的内容：

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>

型

赞(0）回复(0）举报 2023-11-17

我来回答

oracle 使用触发器(FK键)删除和插入另一个表

1条答案

相关问题

热门标签

最新问答