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;