我写了个Trigger,是update某个栏位时,根据本身表的状态更新另一个表,代码如下:
CREATE OR REPLACE TRIGGER g_repair_cnt_trigger
before UPDATE of rp_status
--instead of UPDATE of rp_status as
ON sajet.g_sn_defect
FOR EACH ROW
DECLARE
ccnt NUMBER;
cCount NUMBER;
temp VARCHAR2 (25);
tflag BOOLEAN;
BEGIN
temp:='';
tflag:=False;
BEGIN
SELECT a.serial_number INTO temp
FROM sajet.g_sn_defect a,sajet.g_sn_repair b
WHERE a.recid=b.recid--serial_number = :new.serial_number
--AND rp_status =0
AND ROWNUM = 1;
EXCEPTION
WHEN no_data_found THEN
tflag := TRUE;
END; IF tflag THEN
ccnt := 1;
BEGIN
SELECT work_order INTO temp
FROM sajet.g_repair_cnt
WHERE work_order = :NEW.work_order
AND part_id = :NEW.model_id
AND repair_date = TO_CHAR (sysdate, 'YYYYMMDD')
AND repair_time = TO_CHAR (sysdate, 'hh24')
AND pdline_id = :NEW.pdline_id
AND process_id = :NEW.process_id
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
ccnt := 0;
END; IF ccnt = 0 THEN
INSERT INTO sajet.g_Repair_cnt (work_order,part_id,repair_date,repair_time,pdline_id,process_id,repair_qty,reRepair_qty)
VALUES (:NEW.work_order, :NEW.model_id,TO_CHAR (sysdate, 'YYYYMMDD'),TO_CHAR (sysdate, 'HH24'), :NEW.pdline_id,:NEW.process_id, 1,0);
ELSE
UPDATE sajet.g_repair_cnt
SET repair_qty = repair_qty + 1
WHERE work_order = :NEW.work_order
AND part_id = :NEW.model_id
AND repair_date = TO_CHAR (sysdate, 'YYYYMMDD')
AND repair_time = TO_CHAR (sysdate, 'hh24')
AND pdline_id = :NEW.pdline_id
AND process_id = :NEW.process_id;
END IF;
else
ccnt := 1;
BEGIN
SELECT work_order INTO temp
FROM sajet.g_repair_cnt
WHERE work_order = :NEW.work_order
AND part_id = :NEW.model_id
AND repair_date = TO_CHAR (sysdate, 'YYYYMMDD')
AND repair_time = TO_CHAR (sysdate, 'hh24')
AND pdline_id = :NEW.pdline_id
AND process_id = :NEW.process_id
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
ccnt := 0;
END;
if cCnt=0 then
INSERT INTO sajet.g_Repair_cnt(work_order,part_id,repair_date,repair_time,pdline_id,process_id,repair_qty,reRepair_qty)
VALUES (:NEW.work_order, :NEW.model_id,TO_CHAR (sysdate, 'YYYYMMDD'),TO_CHAR (sysdate, 'HH24'), :NEW.pdline_id,:NEW.process_id, 0,1);
else
UPDATE sajet.g_repair_cnt
SET rerepair_qty = rerepair_qty + 1
WHERE work_order = :NEW.work_order
AND part_id = :NEW.model_id
AND repair_date = TO_CHAR (sysdate, 'YYYYMMDD')
AND repair_time = TO_CHAR (sysdate, 'hh24')
AND pdline_id = :NEW.pdline_id
AND process_id = :NEW.process_id;
end if;
END IF;
END;编译没问题,当update 表时报 ORA-04091: 表格 SAJET.G_SN_DEFECT 正在變更中, 觸發程式?函數無法檢視它
ORA-06512: 在 "SAJET.G_REPAIR_CNT_TRIGGER", line 11
ORA-04088: 執行觸發程式 'SAJET.G_REPAIR_CNT_TRIGGER' 時發生錯誤请高人指点啊!!!
CREATE OR REPLACE TRIGGER g_repair_cnt_trigger
before UPDATE of rp_status
--instead of UPDATE of rp_status as
ON sajet.g_sn_defect
FOR EACH ROW
DECLARE
ccnt NUMBER;
cCount NUMBER;
temp VARCHAR2 (25);
tflag BOOLEAN;
BEGIN
temp:='';
tflag:=False;
BEGIN
SELECT a.serial_number INTO temp
FROM sajet.g_sn_defect a,sajet.g_sn_repair b
WHERE a.recid=b.recid--serial_number = :new.serial_number
--AND rp_status =0
AND ROWNUM = 1;
EXCEPTION
WHEN no_data_found THEN
tflag := TRUE;
END; IF tflag THEN
ccnt := 1;
BEGIN
SELECT work_order INTO temp
FROM sajet.g_repair_cnt
WHERE work_order = :NEW.work_order
AND part_id = :NEW.model_id
AND repair_date = TO_CHAR (sysdate, 'YYYYMMDD')
AND repair_time = TO_CHAR (sysdate, 'hh24')
AND pdline_id = :NEW.pdline_id
AND process_id = :NEW.process_id
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
ccnt := 0;
END; IF ccnt = 0 THEN
INSERT INTO sajet.g_Repair_cnt (work_order,part_id,repair_date,repair_time,pdline_id,process_id,repair_qty,reRepair_qty)
VALUES (:NEW.work_order, :NEW.model_id,TO_CHAR (sysdate, 'YYYYMMDD'),TO_CHAR (sysdate, 'HH24'), :NEW.pdline_id,:NEW.process_id, 1,0);
ELSE
UPDATE sajet.g_repair_cnt
SET repair_qty = repair_qty + 1
WHERE work_order = :NEW.work_order
AND part_id = :NEW.model_id
AND repair_date = TO_CHAR (sysdate, 'YYYYMMDD')
AND repair_time = TO_CHAR (sysdate, 'hh24')
AND pdline_id = :NEW.pdline_id
AND process_id = :NEW.process_id;
END IF;
else
ccnt := 1;
BEGIN
SELECT work_order INTO temp
FROM sajet.g_repair_cnt
WHERE work_order = :NEW.work_order
AND part_id = :NEW.model_id
AND repair_date = TO_CHAR (sysdate, 'YYYYMMDD')
AND repair_time = TO_CHAR (sysdate, 'hh24')
AND pdline_id = :NEW.pdline_id
AND process_id = :NEW.process_id
AND ROWNUM = 1;
EXCEPTION
WHEN OTHERS THEN
ccnt := 0;
END;
if cCnt=0 then
INSERT INTO sajet.g_Repair_cnt(work_order,part_id,repair_date,repair_time,pdline_id,process_id,repair_qty,reRepair_qty)
VALUES (:NEW.work_order, :NEW.model_id,TO_CHAR (sysdate, 'YYYYMMDD'),TO_CHAR (sysdate, 'HH24'), :NEW.pdline_id,:NEW.process_id, 0,1);
else
UPDATE sajet.g_repair_cnt
SET rerepair_qty = rerepair_qty + 1
WHERE work_order = :NEW.work_order
AND part_id = :NEW.model_id
AND repair_date = TO_CHAR (sysdate, 'YYYYMMDD')
AND repair_time = TO_CHAR (sysdate, 'hh24')
AND pdline_id = :NEW.pdline_id
AND process_id = :NEW.process_id;
end if;
END IF;
END;编译没问题,当update 表时报 ORA-04091: 表格 SAJET.G_SN_DEFECT 正在變更中, 觸發程式?函數無法檢視它
ORA-06512: 在 "SAJET.G_REPAIR_CNT_TRIGGER", line 11
ORA-04088: 執行觸發程式 'SAJET.G_REPAIR_CNT_TRIGGER' 時發生錯誤请高人指点啊!!!
BEGIN
SELECT a.serial_number INTO temp
FROM sajet.g_sn_defect a,sajet.g_sn_repair b
WHERE a.recid=b.recid--serial_number = :new.serial_number
--AND rp_status =0
AND ROWNUM = 1;
EXCEPTION
WHEN no_data_found THEN
tflag := TRUE;
END; 的问题,好像是update 是Oracle将表锁住了,无法select
FROM sajet.g_sn_defect
既然是这个表触发,就不能再做查询了。
建议用自治事务吧。pragma autonomous_transaction;
FROM sajet.g_sn_defect a,sajet.g_sn_repair b
WHERE a.recid=b.recid--serial_number = :new.serial_number
--AND rp_status =0
AND ROWNUM = 1;