Oracle monitors a table change trigger example (monitor increase, delete, change) _oracle

Use an Oracle trigger to monitor an incremental deletion of a table and record it in another table.

Code:

Copy Code code as follows:

Create or Replace Trigger Test_trigger
Before insert or update or delete on test_table
For each row
Declare
v_id VARCHAR2 (30);
V_BDLB varchar2 (1);
V_JGDM VARCHAR2 (12);
V_JGMC VARCHAR2 (60);
V_GAJGMC VARCHAR2 (60);
V_GAJGWSMC VARCHAR2 (30);
V_JZ VARCHAR2 (30);
V_KSDWSDWMC VARCHAR2 (30);
Begin
/*
When inserted, the new inserted data is stored in the history table.
The modified data is stored in the history table at the time of modification.
When deleted, the previous data is stored in the history table.
*/
Select Org_id_s.nextval into v_id from dual; --using SEQ to generate primary keys
V_JGDM: =: new.row_id;
V_JGMC: =: new.dept_name;
V_GAJGMC: =: new.dept_name;
V_GAJGWSMC: =: NEW.BMJC;
V_JZ: = substr (V_JGDM, 7, 2);
If ' 2 ' =: New.depttype Then
V_KSDWSDWMC: = ' shiju ';
Else
If ' = V_jz Then
V_KSDWSDWMC: = ' zhi ';
elsif ' ' = V_JZ Then
V_KSDWSDWMC: = ' xing ';
elsif ' Wuyi ' = V_JZ Then
V_KSDWSDWMC: = ' she ';
Else
V_KSDWSDWMC: = ' Qita ';
End If;
End If;

If inserting then
V_BDLB: = ' 1 ';
INSERT INTO Test_table_h
(IDs, BDLB, JGDM, JGMC, GAJGMC, GAJGWSMC, JZ, KSDWSDWMC)
Values
(V_ID,
V_BDLB,
V_JGDM,
V_JGMC,
V_GAJGMC,
V_GAJGWSMC,
V_JZ,
V_KSDWSDWMC);
elsif updating Then
V_BDLB: = ' 2 ';
INSERT INTO Test_table_h
(IDs, BDLB, JGDM, JGMC, GAJGMC, GAJGWSMC, JZ, KSDWSDWMC)
Values
(V_ID,
V_BDLB,
V_JGDM,
V_JGMC,
V_GAJGMC,
V_GAJGWSMC,
V_JZ,
V_KSDWSDWMC);
Else
V_BDLB: = ' 3 ';
V_JGDM: =: old.row_id;
V_JGMC: =: old.dept_name;
V_GAJGMC: =: old.dept_name;
V_GAJGWSMC: =: OLD.BMJC;
V_JZ: = substr (V_JGDM, 7, 2);
If ' 2 ' =: Old.depttype Then
V_KSDWSDWMC: = ' shiju ';
Else
If ' = V_jz Then
V_KSDWSDWMC: = ' zhi ';
elsif ' ' = V_JZ Then
V_KSDWSDWMC: = ' xing ';
elsif ' Wuyi ' = V_JZ Then
V_KSDWSDWMC: = ' she ';
Else
V_KSDWSDWMC: = ' Qita ';
End If;
End If;
INSERT INTO Test_table_h
(IDs, BDLB, JGDM, JGMC, GAJGMC, GAJGWSMC, JZ, KSDWSDWMC)
Values
(V_ID,
V_BDLB,
V_JGDM,
V_JGMC,
V_GAJGMC,
V_GAJGWSMC,
V_JZ,
V_KSDWSDWMC);
End If;
End