幫忙看下，這個trigger什么問題

[girl5201314]

CREATE OR REPLACE TRIGGER SFCS.EF_STANDARD_WORK_T
AFTER INSERT OR UPDATE
ON SFCS.EF_STANDARD_WORK_HOURS
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
WHEN (NEW.GROUP_ID IN (18,19,23,25,57))
DECLARE
tmpVar NUMBER;
models varchar(40);
Part_no varchar(20);
counts1 number;
counts2 number;
standard_work_time number;
CURSOR C_partno ;

/******************************************************************************
NAME: EF_STANDARD_WORK_T
PURPOSE:

REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2008/8/29 1. Created this trigger.

NOTES:

Automatically available Auto Replace Keywords:
Object Name: EF_STANDARD_WORK_T
Sysdate: 2008/8/29
Date and Time: 2008/8/29, 上午 08:02:49, and 2008/8/29 上午 08:02:49
Username: (set in TOAD Options, Proc Templates)
Table Name: EF_STANDARD_WORK_HOURS (set in the "New PL/SQL Object" dialog)
Trigger Options: (set in the "New PL/SQL Object" dialog)
******************************************************************************/
BEGIN
if new.group_id in (18,19,23) then
select sum(standard_work_time) into standard_work_time
from ef_standard_work_hours
where model=:new.model
and group_id in (18,19,23);
select nvl(count(*),0) into counts2 from inv_pn where model=:new.model and SUBSTR (part_no, 1, 2)='70';
if counts2>0 then
CURSOR C_partno is select part_no from inv_pn where model=:new.model and SUBSTR (part_no, 1, 2)='70';
OPEN c_partno;

　　 LOOP
　　 FETCH c_partno INTO part_no;
　　 EXIT WHEN c_partno% NOTFOUND;
insert into ef_standard_work values (ERP_STANDARD_WORK_sequence.nextval,:new.model,part_no,standard_work_time,3,sysdate,sysdate,0);
end loop;
close c_partno;
end if;
else
begin
select sum(standard_work_time) into standard_work_time
from ef_standard_work_hours
where model=:new.model
and group_id in (25,27);

SELECT nvl(count(*),0) into counts1
FROM ef_standard_work
WHERE model = :new.model
AND SUBSTR (part_no, 1, 2)='95'
and a.model= :new.model;



select nvl(count(*),0) into counts2
from inv_pn
where model=:new.model
and SUBSTR (part_no, 1, 2)='95';

if counts2>0 then
declare
CURSOR C_partno is select part_no from inv_pn where model=:new.model and SUBSTR (part_no, 1, 2)='95';
begin
OPEN c_partno;

　　 LOOP
　　 FETCH c_partno INTO part_no;
　　 EXIT WHEN c_partno% NOTFOUND;
insert into ef_standard_work values
(ERP_STANDARD_WORK_sequence.nextval,:new.model,part_no,standard_work_time,3,sysdate,sysdate,0);
end loop;

close c_partno;
end;
end if;


end;
end if;

tmpVar := 0;
ELECT MySeq.NEXTVAL INTO tmpVar FROM dual;
:NEW.SequenceColumn := tmpVar;
:NEW.CreatedDate := SYSDATE;
:NEW.CreatedUser := USER;

EXCEPTION
WHEN OTHERS THEN
RAISE;
END EF_STANDARD_WORK_T;

[oracledbalgtu]

原因是存在双字节的空格和有个new没有给":"
修改如下：
CREATE OR REPLACE TRIGGER SFCS.EF_STANDARD_WORK_T
  AFTER INSERT OR UPDATE ON SFCS.EF_STANDARD_WORK_HOURS
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
  WHEN (NEW.GROUP_ID IN (18, 19, 23, 25, 57))
DECLARE
  TMPVAR             NUMBER;
  MODELS             VARCHAR(40);
  PART_NO            VARCHAR(20);
  COUNTS1            NUMBER;
  COUNTS2            NUMBER;
  STANDARD_WORK_TIME NUMBER;
  CURSOR C_PARTNO;
BEGIN
  IF :NEW.GROUP_ID IN (18, 19, 23) THEN
    SELECT SUM(STANDARD_WORK_TIME)
      INTO STANDARD_WORK_TIME
      FROM EF_STANDARD_WORK_HOURS
     WHERE MODEL = :NEW.MODEL
       AND GROUP_ID IN (18, 19, 23);
    SELECT NVL(COUNT(*), 0)
      INTO COUNTS2
      FROM INV_PN
     WHERE MODEL = :NEW.MODEL
       AND SUBSTR(PART_NO, 1, 2) = '70';
    IF COUNTS2 > 0 THEN
      CURSOR C_PARTNO IS
        SELECT PART_NO
          FROM INV_PN
         WHERE MODEL = :NEW.MODEL
           AND SUBSTR(PART_NO, 1, 2) = '70';
      OPEN C_PARTNO;
      LOOP
        FETCH C_PARTNO
          INTO PART_NO;
        EXIT WHEN C_PARTNO% NOTFOUND;
        INSERT INTO EF_STANDARD_WORK
        VALUES
          (ERP_STANDARD_WORK_SEQUENCE.NEXTVAL,
           :NEW.MODEL,
           PART_NO,
           STANDARD_WORK_TIME,
           3,
           SYSDATE,
           SYSDATE,
           0);
      END LOOP;
      CLOSE C_PARTNO;
    END IF;
  ELSE
    BEGIN
      SELECT SUM(STANDARD_WORK_TIME)
        INTO STANDARD_WORK_TIME
        FROM EF_STANDARD_WORK_HOURS
       WHERE MODEL = :NEW.MODEL
         AND GROUP_ID IN (25, 27);
    
      SELECT NVL(COUNT(*), 0)
        INTO COUNTS1
        FROM EF_STANDARD_WORK
       WHERE MODEL = :NEW.MODEL
         AND SUBSTR(PART_NO, 1, 2) = '95'
         AND A.MODEL = :NEW.MODEL;
    
      SELECT NVL(COUNT(*), 0)
        INTO COUNTS2
        FROM INV_PN
       WHERE MODEL = :NEW.MODEL
         AND SUBSTR(PART_NO, 1, 2) = '95';
    
      IF COUNTS2 > 0 THEN
        DECLARE
          CURSOR C_PARTNO IS
            SELECT PART_NO
              FROM INV_PN
             WHERE MODEL = :NEW.MODEL
               AND SUBSTR(PART_NO, 1, 2) = '95';
        BEGIN
          OPEN C_PARTNO;
          LOOP
            FETCH C_PARTNO
              INTO PART_NO;
            EXIT WHEN C_PARTNO% NOTFOUND;
            INSERT INTO EF_STANDARD_WORK
            VALUES
              (ERP_STANDARD_WORK_SEQUENCE.NEXTVAL,
               :NEW.MODEL,
               PART_NO,
               STANDARD_WORK_TIME,
               3,
               SYSDATE,
               SYSDATE,
               0);
          END LOOP;
          CLOSE C_PARTNO;
        END;
      END IF;
    END;
  END IF;

  TMPVAR := 0;
  ELECT MYSEQ.NEXTVAL
    INTO TMPVAR FROM DUAL;
  :NEW.SEQUENCECOLUMN := TMPVAR;
  :NEW.CREATEDDATE    := SYSDATE;
  :NEW.CREATEDUSER    := USER;

EXCEPTION
  WHEN OTHERS THEN
    RAISE;
END EF_STANDARD_WORK_T;
/

[Quote=引用楼主 girl5201314 的帖子:]
CREATE OR REPLACE TRIGGER SFCS.EF_STANDARD_WORK_T
AFTER INSERT OR UPDATE
ON SFCS.EF_STANDARD_WORK_HOURS
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW
WHEN (NEW.GROUP_ID IN (18,19,23,25,57))
DECLARE
tmpVar NUMBER;
models varchar(40);
Part_no varchar(20);
counts1 number;
counts2 number;
standard_work_time number;
CURSOR C_partno ;

/*****************…
[/Quote]

[girl5201314]

IF :NEW.GROUP_ID IN (18, 19, 23) THEN
這個new，要冒號？
還有，你說的雙字節問題在哪

[oracledbalgtu]

　xx LOOP
xxx FETCH c_partno INTO part_no;
　xx EXIT WHEN c_partno% NOTFOUND;

[Quote=引用 2 楼 girl5201314 的回复:]
IF :NEW.GROUP_ID IN (18, 19, 23) THEN
這個new，要冒號？
還有，你說的雙字節問題在哪
[/Quote]

[girl5201314]

能不能說具體點，順便幫忙修改正確

[oracledbalgtu]

FOR EACH ROW 行级触发器无法参照触发表EF_STANDARD_WORK_T,只有表级才可以。所以你上面的trigger没有语法错误也无法执行。

如果你一定要参照触发表自己，需要使用自治事务的trigger，但是业务逻辑就不能保证了，因为tirgger不会随着触发表自动rollback的。

自治事务触发器：
CREATE OR REPLACE TRIGGER SFCS.EF_STANDARD_WORK_T
  AFTER INSERT OR UPDATE ON SFCS.EF_STANDARD_WORK_HOURS
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
  WHEN (NEW.GROUP_ID IN (18, 19, 23, 25, 57))
DECLARE
  PRAGMA AUTONOMOUS_TRANSACTION;
  TMPVAR             NUMBER;
  MODELS             VARCHAR(40);
  PART_NO            VARCHAR(20);
  COUNTS1            NUMBER;
  COUNTS2            NUMBER;
  STANDARD_WORK_TIME NUMBER;
  CURSOR C_PARTNO;
BEGIN
  IF :NEW.GROUP_ID IN (18, 19, 23) THEN
    SELECT SUM(STANDARD_WORK_TIME)
      INTO STANDARD_WORK_TIME
      FROM EF_STANDARD_WORK_HOURS
     WHERE MODEL = :NEW.MODEL
       AND GROUP_ID IN (18, 19, 23);
    SELECT NVL(COUNT(*), 0)
      INTO COUNTS2
      FROM INV_PN
     WHERE MODEL = :NEW.MODEL
       AND SUBSTR(PART_NO, 1, 2) = '70';
    IF COUNTS2 > 0 THEN
      CURSOR C_PARTNO IS
        SELECT PART_NO
          FROM INV_PN
         WHERE MODEL = :NEW.MODEL
           AND SUBSTR(PART_NO, 1, 2) = '70';
      OPEN C_PARTNO;
      LOOP
        FETCH C_PARTNO
          INTO PART_NO;
        EXIT WHEN C_PARTNO% NOTFOUND;
        INSERT INTO EF_STANDARD_WORK
        VALUES
          (ERP_STANDARD_WORK_SEQUENCE.NEXTVAL,
           :NEW.MODEL,
           PART_NO,
           STANDARD_WORK_TIME,
           3,
           SYSDATE,
           SYSDATE,
           0);
      END LOOP;
      CLOSE C_PARTNO;
    END IF;
  ELSE
    BEGIN
      SELECT SUM(STANDARD_WORK_TIME)
        INTO STANDARD_WORK_TIME
        FROM EF_STANDARD_WORK_HOURS
       WHERE MODEL = :NEW.MODEL
         AND GROUP_ID IN (25, 27);
    
      SELECT NVL(COUNT(*), 0)
        INTO COUNTS1
        FROM EF_STANDARD_WORK
       WHERE MODEL = :NEW.MODEL
         AND SUBSTR(PART_NO, 1, 2) = '95'
         AND A.MODEL = :NEW.MODEL;
    
      SELECT NVL(COUNT(*), 0)
        INTO COUNTS2
        FROM INV_PN
       WHERE MODEL = :NEW.MODEL
         AND SUBSTR(PART_NO, 1, 2) = '95';
    
      IF COUNTS2 > 0 THEN
        DECLARE
          CURSOR C_PARTNO IS
            SELECT PART_NO
              FROM INV_PN
             WHERE MODEL = :NEW.MODEL
               AND SUBSTR(PART_NO, 1, 2) = '95';
        BEGIN
          OPEN C_PARTNO;
          LOOP
            FETCH C_PARTNO
              INTO PART_NO;
            EXIT WHEN C_PARTNO% NOTFOUND;
            INSERT INTO EF_STANDARD_WORK
            VALUES
              (ERP_STANDARD_WORK_SEQUENCE.NEXTVAL,
               :NEW.MODEL,
               PART_NO,
               STANDARD_WORK_TIME,
               3,
               SYSDATE,
               SYSDATE,
               0);
          END LOOP;
          CLOSE C_PARTNO;
        END;
      END IF;
    END;
  END IF;

  TMPVAR := 0;
  ELECT MYSEQ.NEXTVAL
    INTO TMPVAR FROM DUAL;
  :NEW.SEQUENCECOLUMN := TMPVAR;
  :NEW.CREATEDDATE    := SYSDATE;
  :NEW.CREATEDUSER    := USER;
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END EF_STANDARD_WORK_T;
/

[Quote=引用 4 楼 girl5201314 的回复:]
能不能說具體點，順便幫忙修改正確
[/Quote]