基于职务类别的插入触发器

Question

你好，我正在尝试创建一个触发器，它应该根据所有注册用户的工作类别(医疗/其他)将用户总数检索为两个参数，因此我开发了：

    CREATE OR REPLACE TRIGGER count_before_insert
    BEFORE INSERT
    ON REGISTRATION_FORM
    FOR EACH ROW

    DECLARE
    p_current_doctor_number varchar2(20);
    p_current_other_number varchar2(20);
    p_ann_id number;

   BEGIN

-- Count the existing trainees based on medical job category
SELECT COUNT(*) FROM REGISTRATION_FORM INTO p_current_doctor_number
WHERE JOB_CATEGORY = 'Medic'
AND ANNOUCMENT_ID = p_ann_id;

 -- Count the existing trainees based on other job category
 SELECT COUNT(*) FROM TRN_LS_REGISTRATION_FORM INTO p_current_other_number
 WHERE JOB_CATEGORY != 'Medic'
 AND ANNOUCMENT_ID = p_ann_id;

但我收到了以下错误：

 Trigger COUNT_BEFORE_INSERT compiled

 LINE/COL  ERROR
--------- -------------------------------------------------------------
9/4       PL/SQL: SQL Statement ignored
9/50      PL/SQL: ORA-00933: SQL command not properly ended
14/4      PL/SQL: SQL Statement ignored
14/50     PL/SQL: ORA-00933: SQL command not properly ended
Errors: check compiler log

Answer 1

这是SELECT - INTO - FROM，而不是SELECT - FROM - INTO。

CREATE OR REPLACE TRIGGER count_before_insert
   BEFORE INSERT
   ON REGISTRATION_FORM
   FOR EACH ROW

DECLARE
   p_current_doctor_number  VARCHAR2 (20);
   p_current_other_number   VARCHAR2 (20);
   p_ann_id                 NUMBER;
BEGIN
   -- Count the existing trainees based on medical job category
   SELECT COUNT (*)
     INTO p_current_doctor_number
     FROM REGISTRATION_FORM
    WHERE     JOB_CATEGORY = 'Medic'
          AND ANNOUCMENT_ID = p_ann_id;

   -- Count the existing trainees based on other job category
   SELECT COUNT (*)
     INTO p_current_other_number
     FROM TRN_LS_REGISTRATION_FORM
    WHERE     JOB_CATEGORY != 'Medic'
          AND ANNOUCMENT_ID = p_ann_id;
END;

现在，扳机并没有做什么聪明的事情，所以我想你会在这里做更多的事情。

注意表错误的变异(不能从正在被修改的表中选择数据)。

此外，p_ann_id是一个局部变量，当前为NULL，因此这些选择不会返回任何内容。也许你是想用

... and ANNOUCMENT_ID = :new.ANNOUCMENT_ID