sql - 触发器编译没有错误，但它不添加数据

Question

在对表“saturn.szraspr”进行更新或插入后，我正在尝试更新名为“saturn.szrunsp”的表。经过善意的建议，我创建了一个没有任何错误的触发器。然而，主要部分似乎仍然没有工作，这是在表 saturn.szraspr 更新后将数据添加到表 saturn.szrunsp 的触发器。请在下面查看表格和触发器的所有代码。更新!!

-- INSERTING table --

        CREATE TABLE SATURN.SZRUNSP
    (
      SZRUNSP_STUDENT_NO       VARCHAR2(9 CHAR)     NOT NULL,
      SZRUNSP_PIDM             NUMBER(8)            NOT NULL,
      SZRUNSP_STUDENT_NAME     VARCHAR2(60 CHAR)    NOT NULL,
      SZRUNSP_SUSPEND_ACCOUNT  VARCHAR2(1 CHAR)     NOT NULL,
      SZRUNSP_UNSUSPEND_DATE   DATE                 NOT NULL,
      SZRUNSP_ACTIVITY_DATE    DATE                 NOT NULL,
      SZRUNSP_USER_ID          VARCHAR2(30 CHAR),
      SZRUNSP_ACTION           VARCHAR2(30 CHAR),
      SZRUNSP_SURROGATE_ID     NUMBER(19)           NOT NULL,
      SZRUNSP_VERSION          NUMBER(19)           NOT NULL,
      SZRUNSP_VPDI_CODE        VARCHAR2(6 CHAR)
    )


-- TRIGGER TABLE -- 

    CREATE TABLE SATURN.SZRASPR
(
  SZRASPR_STUDENT_NO             VARCHAR2(9 CHAR) NOT NULL,
  SZRASPR_STUDENT_NAME           VARCHAR2(60 CHAR) NOT NULL,
  SZRASPR_DATE_OF_BIRTH          VARCHAR2(8 CHAR) NOT NULL,
  SZRASPR_SUSPEND_ACCOUNT        VARCHAR2(1 CHAR) NOT NULL,
  SZRASPR_EMAIL_ADDRESS          VARCHAR2(100 CHAR) NOT NULL,
  SZRASPR_STUDENT_YEAR           VARCHAR2(2 CHAR) NOT NULL,
  SZRASPR_FACULTY                VARCHAR2(60 CHAR) NOT NULL,
  SZRASPR_SCHOOL_OF_STUDY        VARCHAR2(60 CHAR) NOT NULL,
  SZRASPR_DEGREE_NAME            VARCHAR2(100 CHAR) NOT NULL,
  SZRASPR_MODE_OF_STUDY          VARCHAR2(20 CHAR) NOT NULL,
  SZRASPR_FEE_STATUS             VARCHAR2(20 CHAR) NOT NULL,
  SZRASPR_YEAR_START             VARCHAR2(4 CHAR) NOT NULL,
  SZRASPR_SEMESTER_START         VARCHAR2(5 CHAR) NOT NULL,
  SZRASPR_ENROLMENT_STATUS       VARCHAR2(2 CHAR) NOT NULL,
  SZRASPR_LEAVING_DATE           VARCHAR2(8 CHAR),
  SZRASPR_PENDING_ACTIVITY_DATE  DATE,
  SZRASPR_ACTIVE_ACTIVITY_DATE   DATE,
  SZRASPR_PENDING_STATUS         VARCHAR2(2 CHAR),
  SZRASPR_ACTIVE_STATUS          VARCHAR2(2 CHAR),
  SZRASPR_PIDM                   NUMBER(8)      NOT NULL,
  SZRASPR_TERM_CODE              VARCHAR2(6 CHAR) NOT NULL,
  SZRASPR_ASPIRE_ERROR_REPORTED  VARCHAR2(10 CHAR),
  SZRASPR_ERROR_TEXT             VARCHAR2(100 CHAR),
  SZRASPR_SEQ_NO                 NUMBER,
  SZRASPR_SEND                   INTEGER,
  SZRASPR_SURROGATE_ID           NUMBER(19),
  SZRASPR_VERSION                NUMBER(19),
  SZRASPR_USER_ID                VARCHAR2(30 CHAR),
  SZRASPR_DATA_ORIGIN            VARCHAR2(30 CHAR),
  SZRASPR_ACTIVITY_DATE          DATE,
  SZRASPR_VPDI_CODE              VARCHAR2(6 CHAR)
);



--Trigger--

    CREATE OR REPLACE TRIGGER szrunsp_suspend_trigger
after update or insert on SATURN.SZRASPR
for each row     
declare
    SZRUNSP_USER_ID varchar2(30 CHAR);
begin
    select user into SZRUNSP_USER_ID from dual;

    insert into SATURN.SZRUNSP values
        (:new.SZRASPR_STUDENT_NO,
         :new.SZRASPR_PIDM,
         :new.SZRASPR_STUDENT_NAME,
         :new.SZRASPR_SUSPEND_ACCOUNT,
         SYSDATE,
         SYSDATE,
         user,
         :new.SZRASPR_SURROGATE_ID,
         :new.SZRASPR_VERSION,
         :new.SZRASPR_VPDI_CODE);
 end;
/

Answer 1

您正在向

中插入 NULL 值

  SZRUNSP_SURROGATE_ID     NUMBER(19)           NOT NULL,
  SZRUNSP_VERSION          NUMBER(19)           NOT NULL,

而且你只能对触发表上的列使用 :new 前缀 - 而不是你要插入的表上