TRIGGER問題

[girl5201314]

CREATE OR REPLACE TRIGGER ws_worksheet_checked_t
After UPDATE ON ws_worksheet
REFERENCING NEW AS NEW OLD AS OLD
fOR EACH ROW
WHEN (NEW.checked = 'Y')
declare
shiftname varchar(50);
groupname varchar(20);
wono varchar(25);
actualtime number;
CURSOR output(wsmasterid number,orgid number) is select wo_no,actual_time from ws_worksheet_output where ws_master_id = wsmasterid and org_id=orgid order by wo_no ;
BEGIN
select shift_name into shiftname from sh_shifts where org_id=:new.org_id and shift_id=:new.shift_id;
select group_name into groupname from ef_group where org_id=:new.org_id and GROUP_ID=:new.GROUP_ID;

open cursor output(:new.ws_master_id,:new.org_id);
fetch output into wono,actualtime;
while output%found
loop
INSERT INTO ws_work_hours_interface values(:new.work_date,shiftname,groupname,wono,actualtime,0,:new.ws_master_id,:new.shift_id,:new.GROUP_ID);
fetch output into wono,actualtime;
end loop;
close output;
END;

報錯Warning: Trigger created with compilation errors

[hyrongg]

出现：報錯Warning: Trigger created with compilation errors 错误后

show error

看一下什么错误

[oracledbalgtu]

试试下面的：
CREATE OR REPLACE TRIGGER WS_WORKSHEET_CHECKED_T
  AFTER UPDATE ON WS_WORKSHEET
  REFERENCING NEW AS NEW OLD AS OLD
  FOR EACH ROW
  WHEN (NEW.CHECKED = 'Y')
DECLARE
  SHIFTNAME  VARCHAR(50);
  GROUPNAME  VARCHAR(20);
  WONO       VARCHAR(25);
  ACTUALTIME NUMBER;
  CURSOR OUTPUT(WSMASTERID NUMBER, ORGID NUMBER) IS
    SELECT WO_NO, ACTUAL_TIME
      FROM WS_WORKSHEET_OUTPUT
     WHERE WS_MASTER_ID = WSMASTERID
       AND ORG_ID = ORGID
     ORDER BY WO_NO;
BEGIN
  SELECT SHIFT_NAME
    INTO SHIFTNAME
    FROM SH_SHIFTS
   WHERE ORG_ID = :NEW.ORG_ID
     AND SHIFT_ID = :NEW.SHIFT_ID;
  SELECT GROUP_NAME
    INTO GROUPNAME
    FROM EF_GROUP
   WHERE ORG_ID = :NEW.ORG_ID
     AND GROUP_ID = :NEW.GROUP_ID;

  OPEN CURSOR OUTPUT(:NEW.WS_MASTER_ID, :NEW.ORG_ID);
  --WHILE OUTPUT%FOUND LOOP
  LOOP
    FETCH OUTPUT
      INTO WONO, ACTUALTIME;
    EXIT WHEN OUTPUT%NOTFOUND;
  
    INSERT INTO WS_WORK_HOURS_INTERFACE
    VALUES
      (:NEW.WORK_DATE,
       SHIFTNAME,
       GROUPNAME,
       WONO,
       ACTUALTIME,
       0,
       :NEW.WS_MASTER_ID,
       :NEW.SHIFT_ID,
       :NEW.GROUP_ID);
    /*    FETCH OUTPUT
        INTO WONO, ACTUALTIME;
    */
  END LOOP;
  CLOSE OUTPUT;
END;
/

[Quote=引用楼主 girl5201314 的帖子:]
CREATE OR REPLACE TRIGGER ws_worksheet_checked_t
After UPDATE ON ws_worksheet
REFERENCING NEW AS NEW OLD AS OLD
fOR EACH ROW
WHEN (NEW.checked = 'Y')
declare
shiftname varchar(50);
groupname varchar(20);
wono varchar(25);
actualtime number;
CURSOR output(wsmasterid number,orgid number) is select wo_no,actual_time from ws_worksheet_output where ws_master_id = wsmasterid and o…
[/Quote]

[girl5201314]

樓上的還是報一樣的錯

[you_tube]

[Quote=引用 3 楼 girl5201314 的回复:]
樓上的還是報一樣的錯
[/Quote]

检查一下你的表字段是否正确

[sleepzzzzz]

Warning应该没关系吧

[girl5201314]

[Quote=引用 4 楼 you_tube 的回复:]
引用 3 楼 girl5201314 的回复:
樓上的還是報一樣的錯


检查一下你的表字段是否正确
[/Quote]

沒有問題。TRIGGER絕對沒有問題，只是compilation時發生錯誤。
我想問下
1.A表觸發TRIGGER過程，需要用到B表的數據，是不是有權限問題。
2.怎么看帳號，TABLE的關于TRIGGER方面的權限

[hyrongg]

让你用show error 你怎么不用呢？

show error会告诉你问题出在那一行，什么错误。

这么好用的命令你不用，哎

[girl5201314]

[Quote=引用 7 楼 hyrongg 的回复:]
让你用show error 你怎么不用呢？

show error会告诉你问题出在那一行，什么错误。

这么好用的命令你不用，哎
[/Quote]

TOAD 無法識別這個命令

[lws0472]

INSERT INTO WS_WORK_HOURS_INTERFACE(field1,field2................)
后面最好跟上你的字段名,你加上试一下,不加的话容易出错,也不容易调试