oracle触发器实现对view的DML操作.docx

oracle触发器实现对view的DML操作.docx

《oracle触发器实现对view的DML操作.docx》由会员分享，可在线阅读，更多相关《oracle触发器实现对view的DML操作.docx（9页珍藏版）》请在冰豆网上搜索。

oracle触发器实现对view的DML操作

oracle触发器实现对view的DML操作

一、触发器可以看做是一种特殊的存储过程。

触发事件：

DML（insertupdatedelete）

DDL（createalterdrop）

数据库系统事件（数据库启动、退出、产生错误或者异常等）

用户事件（用户的登入登出）

二、触发器的创建

Create[orreplace]triggertri_name --触发器名

 [before|after|isteadof]tri_event --触发事件（insert、update、delete、create、alter、drop）

 Ontable_name|view_name|user_name|db_name–触发对象

 [foreachrow]

 Begin

      [whentrigger_condition]      --触发条件    （insertingupdatingdeleteing）

      Procedure（触发的操作）

End;

三、触发器的分类：

语句触发器（只触发一次）

行级触发器（foreachrow）

四、Demo

1.语句触发器

createorreplacetriggertri_emp

beforeinsertorupdateordelete

onemp

begin

      ifinsertingthen

      dbms_output.put_line（'插入数据'）;

      elsifupdatingthen

      dbms_output.put_line（'更新数据'）;

      elsifdeletingthen

      dbms_output.put_line（'删除数据'）;

      endif;

endtri_emp;

insertintoemp（empno,ename,job,sal,comm,deptno）values（'8888','小午','CEO',8888,6666,10）;

updateempsetsal=10000whereempno=8888;

deleteempwhereempno=8888;

2.行级触发器

Createtablestudent（            --创建测试table

Idintprimarykey,

Namevarchar2（30）

）

--创建序列

createsequenceseq_id

startwith1

minvalue1

nomaxvalue

incrementby1

nocache

nocycle;

createorreplacetriggertri_student

beforinsert

onstudent

foreachrow

begin

      selectseq_id.nextvalinto:

new.idfromdual;             --从序列中获取id的值插入到student

end;

insertintostudentvalues（5,'小午'）;      --插入数据后查看结果

    注：

会覆盖原来的值

在使用before触发器注入新的值时使用:

new

3.替换触发器

             好处：

实现对VIEW视图的DML

      createviewview_emp_dept--创建视图

      as

      selecta.empno,a.ename,b.deptno,b.dname,a.job,a.hiredate

      fromempa,deptb

      wherea.DEPTNO=b.DEPTNO;

      createorreplacetriggertri_insert_view             --创建触发器

insteadofinsert            --代替触发器

onview_emp_dept

foreachrow

declare

      var_numnumber（10）;

begin

      selectcount（*）intorow_deptfromdeptwheredeptno=:

new.deptno;

      ifvar_num=0then --判断部门号是否存在，不存在择插入

             insertintodept（deptno,dname）values（:

new.deptno,:

new.dname）;

      endif;

      insertintoemp（empno,ename,deptno,job,hiredate）     --emp表插入数据

      values（:

new.empno,:

new.ename,:

new.deptno,:

new.job,:

new.hiredate）;

endtri_insert_view;

或者在触发器中使用显式游标

createorreplacetriggertri_insert_view

      insteadofinsert

      onview_emp_dept

      foreachrow

declare

      cursorcur_deptis

      select*fromdeptwheredeptno=:

new.deptno;

begin

      opencur_dept;

      ifcur_dept%notfoundthen   --判断部门号是否存在，不存在择插入

             insertintodept（deptno,dname）values（:

new.deptno,:

new.dname）;

      endif;

      insertintoemp（empno,ename,deptno,job,hiredate）     --emp表插入数据

      values（:

new.empno,:

new.ename,:

new.deptno,:

new.job,:

new.hiredate）;

endtri_insert_view;

--直接在视图上进行数据的插入

insertintoview_emp_dept（empno,ename,deptno,dname,job,hiredate）

values（8888,'小午',16,'CEO','管理',to_date（'19900725','yyyymmdd'））;

注：

隐式游标会报错（像下边这样）

createorreplacetriggertri_insert_view             --创建触发器

insteadofinsert            --代替触发器

onview_emp_dept

foreachrow

declare

      var_deptnonumber（10）;

begin

      selectdeptnointovar_deptnofromdeptwheredeptno=:

new.deptno;

      ifsql%notfoundthen    --判断部门号是否存在，不存在择插入

             insertintodept（deptno,dname）values（:

new.deptno,:

new.dname）;

      endif;

      insertintoemp（empno,ename,deptno,job,hiredate）     --emp表插入数据

      values（:

new.empno,:

new.ename,:

new.deptno,:

new.job,:

new.hiredate）;

endtri_insert_view;

      注：

不知道隐式游标在这块怎么用，我想如果能用的话可能会在代码上简单点。

      替代触发器的好处：

1.      可以直接对视图进行DML操作（实际是替代触发器的作用）

2.      在复杂的业务中可以使用，程序中的多表关联处理业务的代码

3.      在提高数据一致性的效率上很好

4.用户事件触发器

常见的用户触发事件：

createalterdropcommentgrantremoverenametruncate……等等

--创建一张日志表，监视每个用户对数据的操作

Createtableddl_oper_log（

      Db_obj_namevarchar2（20）,        --数据对象名称

      Db_obj_typevarchar2（20）,          --数据对象类型

      Db_actionvarchar2（20）,                    --操作行为

      Db_uservarchar2（30）,                --数据库用户

      Db_user_namevarchar2（30）,       --操作则的用户名

Db_user_ipvarchar2（30）,                  --操作者的ip

Oper_datedate                           --操作时间

）;

--创建用户触发器（这是在测试机上，我也不知道再生产上能不能这样搞）

createorreplacetriggertri_ddl_oper_log    

aftercreateoralterordroporrename

      orgrantorrevokeortruncateorlogon

onscott.schema

begin

      insertintoscott.ddl_oper_log

      select

      ora_dict_obj_name,

      ora_dict_obj_type,

      ora_sysevent,

      ora_login_user,

      OSUSER,

      nvl（SYS_CONTEXT（'USERENV','IP_ADDRESS'）,'127.0.0.1'）,

      sysdate

      fromV$SESSIONWHEREAUDSID=SYS_CONTEXT（'USERENV','SESSIONID'）;

end;

呵呵……ok基本结束，一天就搞了这点玩意儿。

哦哦  要注意的是需要对V$session表进行授权，不然会报表不存在。

grantselectonv_$sessiontoscott;

      对于上边的操作我们还可以做最简单的实现视图

      CREATE

   ORREPLACEVIEWview_ddl_oper_logASSELECT

       ora_dict_obj_nameASDb_obj_name,

       ora_dict_obj_typeASDb_obj_type,

       ora_syseventASDb_action,

       ora_login_userASDb_user,

       OSUSERAScustomer,

       nvl（

           SYS_CONTEXT（

               'USERENV',

               'IP_ADDRESS'

           ）,

           '127.0.0.1'

       ）AScustomerip,

       sysdateASOper_date

   FROM

       V$SESSION

   WHERE

       AUDSID=SYS_CONTEXT（

           'USERENV',

           'SESSIONID'

       ）

;

不知道在实际开发的环境中应该是怎么设计更好，我觉得触发器可能会影响一定的效率吧，在这为了熟悉触发器就这样了。