Oracle触发器查询统计本表

Oracle触发器查询统计本表

这次由于项目需要，实际动手演练了一把，达到了需要的效果，大致是插入数据后，统计表内和当前插入数据时间相同的个数。

采用行级触发器+语句级触发器。

--包

  CREATE OR REPLACE PACKAGE UPDATECASE AS
  TYPE T_DATE IS TABLE OF date INDEX BY BINARY_INTEGER;
  V_UPDATETIME T_DATE;
  V_ROW  BINARY_INTEGER := 0;
  end;

--行级触发器

  create or replace trigger Tri_student_row
  after insert or update or delete
  on student
  FOR EACH ROW
BEGIN
  updatecase.V_ROW  :=  updatecase.V_ROW + 1;
  if DELETING  then
    updatecase.V_UPDATETIME(updatecase.V_ROW) := :OLD.CREATETIME;
  else
  updatecase.V_UPDATETIME(updatecase.V_ROW) := :NEW.CREATETIME;
  end if;
END;

--语句级触发器

create or replace trigger Tri_student
  after insert or update or delete on student
declare
  studentcount number(38,5);
BEGIN
  FOR V_LOOP IN 1 .. UPDATECASE.V_ROW LOOP
    select count(*)
      into studentcount from student t
    where t.createtime = UPDATECASE.V_UPDATETIME(V_LOOP);
    insert into personcount(comedate,count)values(UPDATECASE.V_UPDATETIME(V_LOOP),studentcount);
  end loop;
  UPDATECASE.V_ROW := 0;
END;