oracle监控某表变动触发器例子(监控增,删,改)
9c8831,窃听风暴 下载,第五行动组2
使用oracle触发器 实现对某个表的增改删的监控操作,并记录到另一个表中。
代码:
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
/*
插入时往历史表中存放的是新插入的数据.
修改时往历史表中存放的是修改后的数据.
删除时往历史表中存放的是删除之前的数据.
*/
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;
如对本文有疑问,请在下面进行留言讨论,广大热心网友会与你互动!!
点击进行留言回复
相关文章:
-
-
-
快速向表插入大量数据
当需要对一个非常大的表INSERT的时候,会消耗非常多的资源,因为update表的时候,oracle需要生成 redo log和undo log;此时最...
[阅读全文]
-
-
-
Oracle入门学习五
学习视频: https://www.bilibili.com/video/BV1tJ411r7EC?p=55 数据的完整性:保证插入表格的数据必须正确。...
[阅读全文]
-
Oracle入门学习六
事务:把一组操作看做一个工作单元,要么都执行,要么都不执行。dml操作才有事务,查询没有事务。 开始事务:从上一次的事务结束之后,从第一次dml操作,就...
[阅读全文]
-
-
-
Oracle入门学习一
oracle的安装,用户授权,表格操作,数据类型,ddl表格,dml数据。 下一篇:Oracle入门学习二 学习视频:https://www.bilib...
[阅读全文]
-
Oracle入门学习二
上一篇:Oracle入门学习一 学习视频:https://www.bilibili.com/video/BV1tJ411r7EC?p=15 算术运算符:...
[阅读全文]
网友评论