使用oracle觸發(fā)器 實(shí)現(xiàn)對(duì)某個(gè)表的增改刪的監(jiān)控操作,并記錄到另一個(gè)表中。
代碼：
復(fù)制代碼 代碼如下:
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
/*
插入時(shí)往歷史表中存放的是新插入的數(shù)據(jù).
修改時(shí)往歷史表中存放的是修改后的數(shù)據(jù).
刪除時(shí)往歷史表中存放的是刪除之前的數(shù)據(jù).
*/
select org_id_s.nextval into v_id from dual; -- 利用seq生成主鍵
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 '03' = v_jz then
v_ksdwsdwmc := 'zhi';
elsif '05' = v_jz then
v_ksdwsdwmc := 'xing';
elsif '51' = 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
(id, 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
(id, 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 '03' = v_jz then
v_ksdwsdwmc := 'zhi';
elsif '05' = v_jz then
v_ksdwsdwmc := 'xing';
elsif '51' = v_jz then
v_ksdwsdwmc := 'she';
else
v_ksdwsdwmc := 'qita';
end if;
end if;
insert into test_table_h
(id, 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;
您可能感興趣的文章:- Oracle中觸發(fā)器示例詳解
- 詳解oracle中通過觸發(fā)器記錄每個(gè)語句影響總行數(shù)
- Oracle觸發(fā)器trigger詳解
- Oracle觸發(fā)器用法實(shí)例詳解
- Oracle創(chuàng)建主鍵自增表(sql語句實(shí)現(xiàn))及觸發(fā)器應(yīng)用
- Oracle中游標(biāo)Cursor基本用法詳解
- Oracle存儲(chǔ)過程游標(biāo)用法分析
- Oracle顯示游標(biāo)的使用及游標(biāo)for循環(huán)
- 快速學(xué)習(xí)Oracle觸發(fā)器和游標(biāo)
