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
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
试试下面的:
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;
/
沒有問題。TRIGGER絕對沒有問題,只是compilation時發生錯誤。
我想問下
1.A表觸發TRIGGER過程,需要用到B表的數據,是不是有權限問題。
2.怎么看帳號,TABLE的關于TRIGGER方面的權限
TOAD 無法識別這個命令
后面最好跟上你的字段名,你加上试一下,不加的话容易出错,也不容易调试