oracle 更新后触发器（after update）

    oracle 触发器，我不太喜欢，因为以前的一个项目用的太多了，几乎每一个业务表都有触发器，后来维护这个项目时很费劲。

 

   提醒一下：oracle 触发器更新表之后，这张表，就不能在你的触发器中随意使用，比如查询之类。

 

--创建包 ，封装所需数据

create or replace package pkg_mail_param
as

type attendancenotcardinfo is record
(
 attendance_fid t_hr_attendancemanager.fid%type,
 leavebillentry_ftypeid t_hr_leavebillentry.ftypeid%type,
 timeattendance_unittype t_hr_timeattendance.funittype%type
);

type cur_attendancenotcard is ref cursor return attendancenotcardinfo;
end pkg_mail_param;
/

   create or replace trigger tri_after_update_leavebill
  after update of fstate
  on t_hr_leavebill
  for each row 
declare
         cur_notcardinfo pkg_mail_param.cur_attendancenotcard;
         v_attmanager_rowtype cur_notcardinfo%rowtype;
         v_billid varchar2(50);
  begin
        v_billid := :new.fid;
       if :new.fstate = 3 and :new.fisnotcard = 1 then
          -- 查找出与考勤表有关联的记录
          open cur_notcardinfo for
               select
                      attendance.fid,
                      info.ftypeid,
                      info.funittype
               from t_hr_attendancemanager attendance
               inner join
                (select
                       billentry.fpersonid,
                       billentry.fbegintime,
                       billentry.fendtime,
                       billentry.ftypeid,
                       timeattendance.funittype
                 from t_hr_leavebillentry billentry
                 left join t_hr_timeattendance timeattendance on timeattendance.fid = billentry.ftypeid
                 where billentry.fbillid =  '' || v_billid || '') info
               on attendance.fpersonid = info.fpersonid and attendance.fstartdate = info.fbegintime and attendance.fenddate = info.fendtime;

               loop
                   fetch cur_notcardinfo into v_attmanager_rowtype;
                   exit when cur_notcardinfo%notfound;
                   update
                          t_hr_attendancemanager tabAM
                   set
                          tabAM.Ftotalvalue = 1,
                          tabAM.Fattendanceid = v_attmanager_rowtype.leavebillentry_ftypeid
                   where tabAM.Fid = v_attmanager_rowtype.attendance_fid;
               end loop;
          close cur_notcardinfo;
       end if;
       exception
                when others then
                DBMS_OUTPUT.put_line(DBMS_UTILITY.format_error_stack);
                DBMS_OUTPUT.put_line(DBMS_UTILITY.format_call_stack);
  end tri_after_update_leavebill;