Oracle trigger query statistics table

Oracle trigger query statistics table

This project requires a hands-on drill to achieve the desired effect. It is roughly the same number of data inserted in the statistical table and the current data insertion time.

Use row-level triggers + statement-level triggers.

-- Package

CREATE OR REPLACE PACKAGE UPDATECASE
TYPE T_DATE is table of date index by BINARY_INTEGER;
V_UPDATETIME T_DATE;
V_ROW BINARY_INTEGER: = 0;
End;

-- Row-Level Trigger

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;

-- Statement-level triggers

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;