PL/SQL:ORA-00917编译失败，第49行(11:56:09)与之关联的行号

Question

create or replace TRIGGER  R_TRACKER_TRI
   before insert on R_TRACKER
   for each row
   declare
   v_number varchar2(15);
   begin
      if inserting then 
         :NEW.PROJECT_ID := PROJECT_ID(1);
         :NEW.PRO_CREATED := LOCALTIMESTAMP;
         :NEW.PRO_CREATED_BY := nvl(v('APP_USER'),USER);
         IF :NEW.RECRUITMENT_TYPE= 'New' THEN
          SELECT ('NEW/'||TO_CHAR(SYSDATE,'YY') || '/' || (NVL(MAX(SUBSTRB(PROJECT_ID,8)),10000)+1)) into v_number 
          FROM R_TRACKER
          WHERE SUBSTRB(PROJECT_ID,1,3) = 'NEW';
          :NEW.PROJECT_ID := v_number;
          elsif :NEW.RECRUITMENT_TYPE= 'Replacement' THEN
          SELECT ('REP/'||TO_CHAR(SYSDATE,'YY') || '/' || (NVL(MAX(SUBSTRB(PROJECT_ID,8)),10000)+1)) into v_number 
          FROM R_TRACKER
          WHERE SUBSTRB(PROJECT_ID,1,3) = 'REP';
            :NEW.PROJECT_ID := v_number;  
         end if;
INSERT INTO R_TRACKER
            (
              PROJECT_ID,
              PRO_LOC,
              DESIGNATION,
              NO_OF_POSITION,
              VACANCY_SINCE,
              STATUS,
              REMARKS,
              UPLOAD,
          RECRUITMENT_TYPE,
          APPROVAL,
          ACTION
            )
            VALUES
            (
              :NEW.PROJECT_ID,
              :NEW.PRO_LOC,
              :NEW.DESIGNATION,
              :NEW.NO_OF_POSITION,
              :NEW.VACANCY_SINCE,
              :NEW.STATUS,
              :NEW.REMARKS 
              :NEW.UPLOAD,
          :NEW.RECRUITMENT_TYPE,
          :NEW.APPROVAL,
          :NEW.ACTION
            );         
                end if;
                end;

Answer 1

查看您的代码，您可以理解您需要了解触发器的使用。请阅读。简而言之，我想说的是trigger是table上的事件。它的创建是为了自动化该表的列值的插入/更新/删除。在您的情况下，您将在表R_TRACKER上创建触发器，然后在inserting下方再次创建触发器。这是错误的。看看如何做。

--Table
CREATE TABLE R_TRACKER
(
   PROJECT_ID         VARCHAR2 (50),
   PRO_LOC            VARCHAR2 (50),
   DESIGNATION        VARCHAR2 (50),
   NO_OF_POSITION     VARCHAR2 (50),
   VACANCY_SINCE      VARCHAR2 (50),
   STATUS             VARCHAR2 (50),
   REMARKS            VARCHAR2 (50),
   UPLOAD             VARCHAR2 (50),
   RECRUITMENT_TYPE   VARCHAR2 (50),
   APPROVAL           VARCHAR2 (50),
   ACTION             VARCHAR2 (50)
);

- 触发表

CREATE OR REPLACE TRIGGER R_TRACKER_TRI
   BEFORE INSERT  ON R_TRACKER
   FOR EACH ROW
DECLARE
   v_number   VARCHAR2 (15);
BEGIN
   IF INSERTING
   THEN
      :NEW.PROJECT_ID := '1';--PROJECT_ID(1) -- This seems to be a function in your case;
     --:NEW.PRO_CREATED := LOCALTIMESTAMP;
     -- :NEW.PRO_CREATED_BY := NVL('APP_USER', USER);
      IF :NEW.RECRUITMENT_TYPE = 'New'
      THEN
         SELECT (   'NEW/'
                 || TO_CHAR (SYSDATE, 'YY')
                 || '/'
                 || (NVL (MAX (SUBSTRB (PROJECT_ID, 8)), 10000) + 1))
           INTO v_number
           FROM R_TRACKER
          WHERE SUBSTRB (PROJECT_ID, 1, 3) = 'NEW';
         :NEW.PROJECT_ID := v_number;
      ELSIF :NEW.RECRUITMENT_TYPE = 'Replacement'
      THEN
         SELECT (   'REP/'
                 || TO_CHAR (SYSDATE, 'YY')
                 || '/'
                 || (NVL (MAX (SUBSTRB (PROJECT_ID, 8)), 10000) + 1))
           INTO v_number
           FROM R_TRACKER
          WHERE SUBSTRB (PROJECT_ID, 1, 3) = 'REP';
         :NEW.PROJECT_ID := v_number;
      END IF;
   END IF;
END;