欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  科技

oracle触发器使用总结

程序员文章站 2023-12-02 15:04:52
oracle触发器使用总结...

触发器 是特定事件出现的时候,自动执行的代码块。类似于存储过程,但是用户不能直接调用他们。触发器是许多关系数据库系统都提供的一项技术。在oracle系统里,触发器类似过程和函数,都有声明,执行和异常处理过程的pl/sql块。

1.说明

1)触发器是一种特殊的存储过程,触发器一般由事件触发并且不能接受参数,存储器由语句块去调用

2)触发器分类:

  1.dml触发器: 创建在表上,由dml事件引发

  2.instead of触发器: 创建在视图上并且只能在行级上触发,用于替代insert,delete等操作(由于oracle中不能直接对有两个以上的表建立的视图进行dml操作,所以给出替代触发器,它是专门为进行视图操作的一种处理方法)

  3.ddl触发器: 触发事件时数据库对象的创建和修改

  4.数据库事件触发器:定义在数据库或者模式上,由数据库事件触发

3)组成:

  1.触发事件:引发触发器被触发的事件 dml语句(insert, update, delete语句对表或视图执行数据处理操作)、ddl语句(如create、alter、drop语句在数据库中创建、修改、删除模式对象)、数据库系统事件

      (如系统启动或退出、异常错误)、用户事件(如登录或退出数据库)。

  2.触发时间:即该触发器是在触发事件发生之前(before)还是之后(after)触发

  3.触发操作:触发器触发后要完成的事情

  4.触发对象:包括表、视图、模式、数据库。只有在这些对象上发生了符合触发条件的触发事件,触发器才会执行触发操作。

  5.触发条件:由when子句指定一个逻辑表达式。只有当该表达式的值为true时,遇到触发事件才会自动执行触发操作。

  6.触发频率:说明触发器内定义的动作被执行的次数。即语句级(statement)触发器和行级(row)触发器。(比如delete多条数据时,行级触发器可能会执行多次,语句级触发器只会触发一次)       

2.语法

1)说明

不同类型的触发器例如dml触发器,instead of触发器,系统触发器语法格式区别较大

2)一般语法

  create [or replace] tigger触发器名 触发时间 触发事件  on表名/视图名  [for each row]  //加上for each row 即为行级触发器,不加时为语句级触发器  begin  pl/sql语句  end
    create [or replace] trigger [schema.]trigger_name      {before | after | instead of}      {delete [or insert][or update [of column,...n]]}      on [schema.]table_name | view_name      [for each row [when(condition)]]      sql_statement[,...n]

例如:

  create or replace trigger   trigger_name    < before | after | instead of > < insert | update | delete>  on table_name  [for each row]  when (condition)  declare  begin  end;

3)instead of 触发器语法

语法:

create [or replace] trigger trigger_name instead of{insert|delete|update [of column...]}[or {insert| delete| update [of column...]}]on view_name[refferencing{old [as] old | new [as] new| parent as parent}]   // 可以指定相关名称,当前的默认相关名称为old和new,应用相关名称时需要加:[for each row]  //instead of 触发器只能在行级上触发,因为没有必要指定[when condition]declarebeginend;

说明:instead of 用于对视图的dml触发,由于视图可能有多个表进行联结而成,因而并非所有的联结均可更新,运用 instead of 触发器可完成相应的操作。

3.实例

创建测试表格:

create table "hnzc"."triggertest"  (    "id"    varchar2(20 byte),    "name"  varchar2(20 byte),    "score" number  );create table tab1 select * from triggertest;

1)dml触发器/行级触发器

触发器如下:

create or replace trigger trigger1 after insert on triggertest    //插入后触发for each row                   //行级触发器begin   insert into tab1(id,name) values('22','33');end;

执行语句:

insert into triggertest (id) values ('aabbcc');

语句执行结束,表tab1中新增加一条数据

2)限制对表的修改(例如非工作时间不能修改某些表)

触发器如下:

create or replace trigger trigger1 after insert on triggertest for each row begin   if(to_char(sysdate,'day') in ('星期三','星期天'))   then raise_application_error(-20001,'不是上班时间,不能修改表格triggertest');   end if;end;

执行语句:

insert into triggertest (id) values ('aabbcc');

今天周三因而输出结果为:

在行 1 上开始执行命令时出错:insert into triggertest (id) values ('aabbcc')错误报告:sql 错误: ora-20001: 不是上班时间,不能修改表格triggertestora-06512: 在 "hnzc.trigger1", line 3ora-04088: 触发器 'hnzc.trigger1' 执行过程中出错

通常对表的修改限制如下(即周一至周五9——18点能修改表格)

create or replace trigger trigger1 before insert or delete or update on triggertest for each row begin   if(to_char(sysdate,'day') in ('星期六','星期天'))   or(to_char(sysdate,'hh24:mi') not between '9:00' and '18:00')   then raise_application_error(-20001,'不是上班时间,不能修改表格triggertest');   end if;end;

3)增加限制条件(如不能更改某个员工的记录)

触发器如下:(如下实现月儿的分数只能增加)

create or replace trigger trigger1 before insert or delete or update on triggertest for each row when(old.name='月儿')begin   case when updating('score') then        if:new.score<:old.score        then raise_application_error(-20001,'月儿的分数只能提升不能下降');        end if;    end case;end;

当前月儿的分数为20

当修改为10时出错

update "hnzc"."triggertest" set score = '10' where rowid = 'aaadezaapaaaah+aab' and ora_rowscn = '47685303'ora-20001: 月儿的分数只能提升不能下降ora-06512: 在 "hnzc.trigger1", line 4ora-04088: 触发器 'hnzc.trigger1' 执行过程中出错

当修改为30时成功

update "hnzc"."triggertest" set score = '30' where rowid = 'aaadezaapaaaah+aab' and ora_rowscn = '47685303'提交成功

4)在触发器中调用存储过程

触发器为:

create or replace trigger trigger1 before insert or delete or update on triggertest for each row begin  testpro1();end;

存储过程为:

create or replaceprocedure testpro1 as begin insert into tab1(id,name,score) values('aaa','bbb',200);end testpro1;

执行完毕后tab1中增加一条数据

5)级联更新

触发器如下(triggertest表中name修改时同时修改tab1中的name)

create or replaceprocedure testpro1 as begin insert into tab1(id,name,score) values('aaa','bbb',200);end testpro1;

执行语句:

update  triggertest set name= '水儿' where name='月儿';

结果:tab1中name为月儿的也更改为水儿

6)instead of触发器

table student表格数据如下

oracle触发器使用总结

创建视图student_view

create or replace view studnet_view as select classid,avg(score) average_score from studentgroup by classid;

视图数据如下:

oracle触发器使用总结

对视图student_view 执行如下操作:

delete from studnet_view where classid='111';

执行结果:

错误报告:sql 错误: ora-01732: 此视图的数据操纵操作非法01732. 00000 -  "data manipulation operation not legal on this view"

解决方法:创建instead of 视图

create or replace trigger student_view_delete instead of delete on studnet_view for each rowbegin  delete from student where classid=:old.classid;end student_view_delete;

执行删除语句

delete from studnet_view where classid='111';

执行结果:删除成功

1 行已删除。

4.注意事项

1) 在触发器的执行部分只能用dml语句(select、insert、update、delete),不能使用ddl语句(create、alter、drop)

2) 触发器中不能使用commit语句,触发器的操作与触发事件(insert,update,delete)一起进行commit和rollback;

3)  一个表上的触发器越多,对于表的dml操作性能影响越大

4) 触发器最大为32k

相关标签: oracle