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 即为行级触发器,不加时为语句级触发器BEGINpl/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)DECLAREBEGINEND;
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表格数据如下
创建视图student_view
CREATE OR REPLACE VIEW STUDNET_VIEW AS SELECT CLASSID,AVG(SCORE) AVERAGE_SCORE FROM STUDENTGROUP BY CLASSID;
视图数据如下:
对视图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
racle触发器插入A表一条数据并把这条数据插入到B表中
racle触发器插入A表一条数据并把这条数据插入到B表中 100 oracle,就是A,B表一模一样,我要在插入A表的时候,B表也插入一下... oracle,就是A,B 表一模一样,我要在插入A表的时候,B表也插入一下 展开 我来答 1个回答 #热议# 公司那些设施可以提高员工幸福感?1346795147369 2016-06-21 知道答主...
可以在远程同义词表上建触发器吗
racle中一个数据库表通过另一个数据表的数据同步
马上就要工作三年了,我都学到什么技术?
触发器,很多都是应付系统漏洞写的;RMAN的常用备份还原操作:用的是磁带和NBR,备份策略是保留一个月,一个星期一次全备,每天一个累积增份;为保险会对一些重要的数据表和表空间用RMAN做2次性的备份,大部分RACLE方面的问题在手册中都会找到答案,再不然就是到网上搜索一下,只要有毅力,我想一般的问...
java培训课程内容是什么?针对java就业岗位java培训学什么?
2、熟练掌握JAVAscr1pt脚本编写,了解XML,XSLT,CSS的相关知识。3、熟悉开源框架Struts1\/Struts2,Spring,Hibernate,iBatis等,对Ajax有了解;熟悉Tomcat,Jboss,Webligic,WebSphere等Web应用服务器的配置和部署;熟练使用PL\/SQL语言,能够编写存储过程和触发器;4、熟悉MYSQL,SQLSERVER,0RACLE等库应用,能...
Oracle数据库如何从11.2降级至之前的版本
开始降级过程之前,在服务器上应该有或已安装要降至版本的 Oracle 二进制文件。如果已卸载了希望降至版本的 Oracle 可执行文件,请将该 Oracle 二进制文件重新安装至正确的版本\/修补程序级别,以便进行降级。在CentOS 6.4下安装Oracle 11gR2(x64) Oracle 11gR2 在VMWare虚拟机中安装步骤 Debian 下 ...
学习Java的,大学毕业一般从事些什么工作?
1、Java应用方向。目前Java在许多行业的企业信息应用方面的应用非常多,比如OA、邮箱、股票、金融、考试、物流、医疗、矿山等信息方面的系统。该方向和行业密切相关,所以,这是一个经验型的发展方向。Java开发者在这方面的需求也非常大,待遇也相当不错,工资水平与Java互联网方向的差不多,很容易在一些...
产享红亭: 1:什么叫存储过程?在oracle中,可以在数据库中定义子程序,这种程序块叫存储过程(procedure),它存放在数据字典中,可以在不同用户和应用程序中共享,并可以实现程序的优化和重用.2:存储过程的有点? 过程运行在服务器端,...
青山区13957949321: oracle中触发器有什么作用 - ?
产享红亭: 触发器是个特殊的存储过程,它的执行不是由程序调用,也不是手工启动,而是由个事件来触发,比如当对一个表进行update,delete,操作时就会激活它执行,如例子:create or replace trigger tigger_select_update before update or delete on mytable for each row declare begin if :old.name='1' then raise_application_error(-20001,'不允许删除和修改'); dbms_output.put_line('load......'); end if; end;
青山区13957949321: 数据库触发器的优缺点以及用法 - ?
产享红亭: 触发器本质上讲就是事件监控器,可以定义在数据库系统进行某一个操作的前后. 当数据库系统在进行特定的操作时,发现有触发器定义,就去执行触发器定义的功能,从而满足某些特定的需要. 例如,在更新某些数据后,再去更新特定的数据. 这就可以定义一个触发器完成这样的需要.触发器的优点:以事件方式来处理. 当数据发生变化的时候, 自动作处理. 缺点:当数据库之间 导出导入的时候, 可能会引起不必要的触发逻辑.
青山区13957949321: oracle中触发器有什么作用?
产享红亭: .触发器的定义就是说某个条件成立的时候,你触发器里面所定义的语句就会被自动的执行.因此触发器不需要人为的去调用,也不能调用. 然后,触发器的触发条件其实在你定义的时候就已经设定好的了.这里面需要说明一下,触发器可以分...
青山区13957949321: Oracle trigger 怎么用??
产享红亭: trigger就是触发器 触发器是特定事件出现的时候,自动执行的代码块.类似于存储过程,触发器与存储过程的区别在于:存储过程是由用户或应用程序显式调用的,而触发器是不能被直接调用的. 举例: 下面的触发器在更新表auths之前触发,目的是不允许在周末修改表: create triggerauth_secure before insert or update or delete //对整表更新前触发 on auths begin if(to_char(sysdate,'DY')='SUN' RAISE_APPLICATION_ERROR(-20600,'不能在周末修改表auths'); end if; end
青山区13957949321: oracle触发器该怎么用? - ?
产享红亭: 你这个是 Oracle 的 for each row 触发器 也就是 每行 触发一次的.这种触发器, 不能直接通过 UPDATE 语句, 更新当前表.应该是通过 :new 来更新当前行的数据.也就是把 update t_emp set xm = REGEXP_REPLACE(xm,'( ){1,}',''); 修改为::new.xm = REGEXP_REPLACE(xm,'( ){1,}','');
青山区13957949321: oracle触发器 - ?
产享红亭: 首先,你得先明白什么是触发器.触发器的定义就是说某个条件成立的时候,你触发器里面所定义的语句就会被自动的执行.因此触发器不需要人为的去调用,也不能调用.然后,触发器的触发条件其实在你定义的时候就已经设定好的了.这...
青山区13957949321: 如何利用ORACLE触发器对新插入的记录,在符合一定条件时立即将其删除? - ?
产享红亭: 强烈不建议使用这种方法,完全可以在触发器中判断后阻止插入的动作.例子如下:create or replace trigger tri_a before insert on a for each row when (条件) begin raise_application_error(-20999,'Can't insert on this table . xxxxxxxxx .'); end; 不过非要按照你的想法实现,也可以,不过比较麻烦.需要创建一个行级触发器以及一个表级触发器,还有一个用于存放表中主键的包变量.
青山区13957949321: oracle触发器中需要通过字段来进行判断 - ?
产享红亭: 触发器的语法 CREATE [OR REPLACE] TRIGGER trigger_name{BEFORE | AFTER }{INSERT | DELETE | UPDATE [OF column [, column …]]}[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]ON [schema.]table_name | [schema....
青山区13957949321: Oracle中触发器有几种,用法与SQL Server一样吗?谢谢 - ?
产享红亭: ORACLE触发器有以下两类: 1> 语句级(Statement-level)触发器,在CREATE TRIGGER语句中不包含FOR EACH ROW子句.语句级触发器对于触发事件只能触发一次, 而且不能访问受触发器影响的每一行的列值.一般用语句级触发器处理...