Oracle触发器例子

-- set serveroutput on
create or replace TRIGGER check_out_jiecun_trigger 
before UPDATE -- 更新当前表的字段时,里只能 before, 不能 after; 否则会报ORA-04084：无法更改此触发器类型的新值
ON check_out
for each row
DECLARE
  -- 声明使用自治事务(触发器里引用了被触发的表,解决方案就是用自治事务.注意业务代码后的 commit.)
  -- 报错参考: ORA-04091: 发生了变化, 触发器/函数不能读它
  pragma autonomous_transaction;
  pv_res varchar2(255);
  pn_last_jiecun number; -- 上期结存
  pi_cnt_id int; -- 合同id
BEGIN
  pi_cnt_id := :old.contract_id;
  pv_res := '查询上期结存, pi_cnt_id: '||pi_cnt_id;
  dbms_output.put_line(pv_res);
  select nvl(max(p.jie_cun), 0) into pn_last_jiecun
  from (
      select c.bid_amount as jie_cun
      , row_number() over(order by c.id desc) as rn -- 按id倒序生成序号
      from check_out c
      where c.contract_id=pi_cnt_id
        and c.id<:old.id -- 当期之前的数据
        and c.status=2 
        and c.reverse_Status=1
  ) p
  where p.rn=1
  ;
  pv_res := '更新结存 '||sysdate||' pn_last_jiecun: '||pn_last_jiecun;
  dbms_output.put_line(pv_res);
  :new.bid_Amount := case when (:old.SALE_AMOUNT+pn_last_jiecun)>nvl(:old.REAL_PAY_AMOUNT,0) 
      then -mod(-(:old.SALE_AMOUNT+pn_last_jiecun) + nvl(:old.REAL_PAY_AMOUNT,0),100) 
      else :old.SALE_AMOUNT + pn_last_jiecun - nvl(:old.REAL_PAY_AMOUNT,0) 
    end;
    -- 自治事务手动提交 
    COMMIT;
end;