oracle触发器使用汇总

类型：数据库类大小：6.5M语言：中文评级：10.0标签:立即下载触发器是一个代码块，在特定事件发生时自动执行。类似于存储过程，但是用户不能直接调用它们。触发器是许多关系数据库系统提供的技术。在ORACLE系统中，触发器类似于过程和函数，具有用于声明、执行和异常处理过程的PL/SQL块。

1.描述

1)触发器是一种特殊的存储过程。触发器通常由事件触发，不能接受参数。内存由语句块调用。

2)触发分类：

1.DML触发器：在表上创建，由DML事件触发

2 .代替触发器：在视图上创建，只能在行级触发，用于替代insert、delete等操作(由于DML操作不能直接在oracle中两个以上表创建的视图上执行，因此给出了一种替代触发器，是视图操作的特殊处理方法)

3.DDL触发器：当事件被触发时，数据库对象的创建和修改

4.数据库事件触发器：在数据库或模式上定义，由数据库事件触发

3)组成：

1.触发事件：触发事件DML语句(插入、更新和删除语句对表或视图执行数据处理操作)、DDL语句(如CREATE、ALTER和DROP语句在数据库中创建、修改和删除架构对象)和数据库系统事件

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

2.触发时间：即触发事件发生之前触发还是之后触发。

3.触发操作：触发触发器后要完成什么

4.触发器对象：包括表、视图、模式和数据库。只有当满足触发条件的触发事件发生在这些对象上时，触发器才会执行触发操作。

5.触发条件：逻辑表达式由WHEN子句指定。只有当此表达式的值为真时，才会在遇到触发事件时自动执行触发操作。

6.触发器频率：表示触发器中定义的动作被执行的次数。也就是说，语句级触发器和行级触发器。(例如，删除多条数据时，行级触发器可能会被多次执行，而语句级触发器只会被触发一次)

2.语法

1)描述

不同类型的触发器，如DML触发器、替代触发器和系统触发器的语法格式有很大不同

2)一般语法

CREATE[ORREPLACE]TIGGER触发器名称触发器时间触发器事件ON表名/视图名[FOREACHROW]//plus FOREACHROW是行级触发器。没有超时，就是语句级触发器BEGINpl/sql语句end create[或replace]触发器[schema。] trigger _ name {before | after |而不是} { delete[或insert][或update [of column，[架构]上的n]]}。]table _ name | view _ name[foreach row[when(condition)]]SQL _ statement[，n]例如：

creatorreplacetriggertrigger _ name before | after | insteadofinsert | update | deleteONtable _ name[FOREACHROW]WHEN(条件)DECLAREBEGINEND

3)代替触发语法

语法：

CREATE[or replace]triggertriger _ name instead of { INSERT | DELETE | UPDATE[of column.]}[或{插入|删除|更新[列].]}]在view _ name[引用{ OLD[as]OLD | NEW[as]NEW | parents parent }]上//可以指定相关的名称。当前默认的相关名称有旧的和新的。应用相关名称时，需要添加：【FOREACHROW】//insteadof触发器只能在行级触发，因为不需要指定【when condition】声明ebeginend

注意：INSTEAD OF用于触发视图的DML。由于视图可以通过连接多个表来形成，因此并非所有的连接都可以更新。代替触发器可以完成相应的操作。

3.例子

创建测试表单：

CREATETABLE'HNZC . 'TRIGGERTEST '(' ID ' varchar 2(20字节)，' NAME ' varchar 2(20字节)，' SCORE ' number)；createtabletab 1 select * from trigertest；

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

触发器如下：

creatorreplaceriggertrigger 1 after sert Trigger Test//Trigger FOREACHROW//行级触发器beginserttotab1(ID，name)值(' 22 '，' 33 ')；END执行语句：

insertintotriggertest(id)值(' aabbcc ')；在语句执行结束后，一条新的数据被添加到表tab1中

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

触发器如下：

creatoreplacetriggertrigger 1 after insertontriggertestforeachrowbeginif(TO _ CHAR(SYSDATE，' DAY')IN('星期三','星期天))然后引发_ APPLICATION _ ERROR(-20001，'不是上班时间，不能修改表格triggert est’；ENDIFEND

执行语句：

insertintotriggertest(id)值(“aabbcc”)；今天周三因而输出结果为：

在行一上开始执行命令时出错：insertintotriggertest(id)值(“aabbcc”)错误报告：SQL错误：ORA-20001:不是上班时间，不能修改表格triggertestORA-06512:在HNZC .TRIGGER1 '，line3ORA-04088:触发器HNZC .TRIGGER1 '执行过程中出错通常对表的修改限制如下（即周一至周五9——18点能修改表格)

creatoreplacetrigger 1 beforeisterordeleterupdategontriggertestforeachrowsbeginif(TO _ CHAR(SYSDATE，' DAY')IN('星期六','星期天))或(TO_CHAR(SYSDATE，' hh 24: mi ')不是介于' 9:00 '和' 18:00 '之间然后' RAISE_APPLICATION_ERROR(-20001 ')不是上班时间，不能修改表格triggert est’；ENDIFEND

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

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

creatoreplacetrigger 1 beforestdeleterupdateontrologgerteforeachrowhen(OLD .'名称='月儿)begingausehenupdating(' SCORE ')theif : new .得分：分老。分数然后提高_ APPLICATION _ ERROR(-20001，)月儿的分数只能提升不能下降');ENDIFENDCASEEND

当前月儿的分数为20

当修改为10时出错

更新“HNZC”TRIGGERTEST ' setscore=' 10 '其中rowid=' aadezaapaaaah AAB ' andora _ ROWSCN=' 47685303 ' ORA-20001:月儿的分数只能提升不能下降ORA-06512:在' HNZC .' TRIGGER1 '，line4ORA-04088:触发器HNZC .TRIGGER1 '执行过程中出错当修改为30时成功

更新“HNZC”TRIGGERTEST ' setscore=' 30 '其中rowid=' aadezaapaaaah AAB ' andora _ ROWSCN=' 47685303 '提交成功4)在触发器中调用存储过程

触发器为：

creatoreplacetriggertrigger 1 beforeisterordeleterupdatontringtgerteforeachrowsbeginintestpro1()；结束存储过程为：

creatoreplaceprocuestpro1a sbeinsertinto tab 1(id，名称，分数)VALUES('AAA '，' BBB '，200)；ENDTESTPRO1执行完毕后表一中增加一条数据

5)级联更新

触发器如下(triggertest表中名字修改时同时修改表一中的名称)

creatoreplaceprocuestpro1a sbeinsertinto tab 1(id，名称，分数)VALUES('AAA '，' BBB '，200)；ENDTESTPRO1执行语句：

updatetriggertestsetname='水儿' wherename='月儿；结果：tab1中名字为月儿的也更改为水儿

6)代替触发器

表学生表格数据如下

创建视图学生视图

AVG(得分)平均得分学生分组分类；视图数据如下：

对视图学生视图执行如下操作：

deletefromsudnet _ viewhateclasid=' 111 '；执行结果：

错误报告：SQL错误：ORA-01732:此视图的数据操纵操作非法01732.00000-'数据操作操作不合法' lonthisview '解决方法：创建代替视图

creatoreplacetriggerstudent _ VIEW _ deleteinsteadof deleteeeonstudnet _ VIEW foreachrowsbegindeletefrostudent其中class id=: old .CLASSIDENDSTUDENT _ VIEW _ DELETE执行删除语句

deletefromsudnet _ viewwhateclassid=' 111 '；执行结果：删除成功

一行已删除。4.注意事项

1)在触发器的执行部分只能用现代语言博士现代语言博士语句（选择、插入、更新、删除),不能使用数据定义语言语句（创建、更改、删除)

2) 触发器中不能使用犯罪语句，触发器的操作与触发事件（插入、更新、删除)一起进行犯罪和回滚；

3) 一个表上的触发器越多，对于表的现代语言博士现代语言博士操作性能影响越大

4) 触发器最大为32K