oracle行級觸發器操作 觸發表 數據 處理方法(oracle 子事務自治 pragma autonomous_transaction)

create or replace trigger iam_storage_useY_update_tr
  after update of use_y, manager_no on IAM_STORAGE
  for each row
declare
  num number;
  --子事務自治 不影響 主 事務
  pragma autonomous_transaction;
  -- local variables here
  --IAM重要物資 倉庫 DIR信息變更時 old.manager_no  when ((old.use_y = 'Y' and new.use_y = 'N') or new.manager_no != '')
  --從有效到無效修改時 或 DIR信息變更時  並判斷當前系統是否仍存在 有效 修改前的DIR信息(old.manager_no)
  -- 仍存在 不做處理; 否 其對應的 DIR對應關係 全部修改為 無效
begin
  select count(1)
    into num
    from iam_storage a
   where a.use_y = 'Y'
     and a.manager_no = :old.manager_no;
  --commit;
 /* DIR信息修改 且 原DIR信息在系統中只存在一個 */
  if num <= 1 and :old.manager_no != :new.manager_no then
    update iam_storage_window w
       set w.is_ny = 'N'
     where w.dir_no = :old.manager_no;
    --commit;
    update iam_storage_window_supervisor s
       set s.is_ny = 'N'
     where s.kid in (select w.kid
                       from iam_storage_window w
                      where w.dir_no = :old.manager_no);
    --commit;
  end if;
  /*DIR信息刪除 且 原DIR信息在系統中只存在一個*/
  if num <= 1 and :old.use_y = 'Y' and :new.use_y = 'N' then
    update iam_storage_window w
       set w.is_ny = 'N'
     where w.dir_no = :old.manager_no;
    --commit;
    update iam_storage_window_supervisor s
       set s.is_ny = 'N'
     where s.kid in (select w.kid
                       from iam_storage_window w
                      where w.dir_no = :old.manager_no);
    --commit;
  end if;
  commit;
end iam_storage_useY_update_tr;