當wiporder中的com_state=1是洗掉wiporder中的這一列，使用了自治事務之后出現了死鎖的問題。
create or replace trigger trg_update_wip
after update on wiporder
for each row
declare
PRAGMA AUTONOMOUS_TRANSACTION;
v_wip_no number(11);
v_or_no number(11);
v_re_user number(11);
v_user_poin number(5);
v_task_id number(11);
v_task_poi number(5);
begin
v_or_no := :old.or_no;
v_wip_no := :old.wip_order;
select task_id into v_task_id from orders where or_no = :old.or_no;
select re_user into v_re_user from orders where or_no = :old.or_no;
select rel_point into v_task_poi from task where task_id = v_task_id;
select point into v_user_poin from point where user_id = v_re_user;
if :new.com_state = '1' then
delete from wiporder where wip_order = v_wip_no;
delete from orders where or_no = v_or_no;
delete from task where task_id = v_task_id;
update point set point = v_user_poin + v_task_poi where user_id = v_re_user;
elsif :new.com_state = '0' then
delete from wiporder where wip_order = v_wip_no;
delete from orders where or_no = v_or_no;
DBMS_OUTPUT.PUT_LINE('完成失敗');
end if;
COMMIT;
end trg_update_wip;
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/226674.html
標籤：基礎和管理