ORACLE之触发器实战（高手勿进）

世界末日过了，我们必须承认，我们都还活着，活着就要学习。今年是个特殊的年份，现在的人想象力太好了，2013，你懂的，希望自己能在这个特殊的年份……你也懂^_^！

人活着，学习就不能停止，华罗庚曾说过：“聪明出于勤奋，天才在于积累”。所以今天的每一步积累都是我们明天成为天才的基础。很多东西都需要亲自动手去实践才能真正掌握，看似简单的知识动起手来就可能会遇到各种问题。经过实际动手，才能真正掌握一个知识点。

一、什么是触发器

触发器是在特定事件出现的时候，自动执行的代码块。类似于存储过程，与存储过程不同的是：存储过程是有用户或应用程序显示调用的，而触发器是不能被直接调用的。触发器是在事件发生时隐式运行的，并且不能接收参数。运行级的触发器的方式叫做激发（Firing）触发器，触发事件可以是对数据库表的DML（INSERT、UPDATE、DELETE）操作或某种视图的操作，也可以是某些系统事件，如数据库的启动和关闭以及某种DDL操作等。

二、触发器的作用

触发器是一种特殊的存储过程，它在插入、删除或修改特定表中的数据时触发执行，它比数据库本身标准的功能有更精细和复杂的数据控制能力。作用如下：

1.安全性

--可以基于数据库的值使用户具有操作数据库的某种权利；

--可以基于时间限制用户的操作，例如，不允许非上班时间修改数据库数据；

--可以基于数据库中的数据限制用户的操作，例如，不允许删除特定表或表中的特定记录。

2.审计

--可以跟踪用户对数据库的操作，例如，用户登录、增删改表等等；

--审计用户操作数据库的语句；

--把用户对数据库的更新写入审计表。

3.实现复杂的数据完整性规则

实现非标准的数据完整性检查和约束。触发器可产生比规则更为复杂的限制。与规则不同，触发器可引用列或数据库对象。例如，触发器可回滚任何企图吃进超过自己保证金的期货，提供可变的默认值。

4.实现复杂的非标准的数据库相关完整性规则

--在修改或删除时级联修改或删除其他表中与之匹配的行；

--在修改或删除时把其他表中与之匹配的行设成NULL值；

--在修改或删除时把其他表中与之匹配的行级联设成默认值。

5.自动计算数据值

若数据的值达到了一定的要求，则进行特定的处理。如，消息表的数据超过以千万条记录，则立即给指定用户发出提示消息。

三、触发器种类

1.DML触发器

ORACLE可以在DML语句进行触发，可以在DML操作前或操作后进行触发，并且可以对每个行或语句操作上进行触发。 ? ? ?

DML是指数据操作语言，如insert|update|delete。

2.替代触发器由于ORACLE在同一时间内不能同事对一个表以上进行操作，所以有一种触发器是建立在视图之上对视图进行INSERT、UPDATE、DELETE操作的，叫替代触发器。与DML触发器不同，DML触发器是在DML操作之外运行的，而替代触发器则代替激发它的DML语句运行，替代触发器是行级的，而且不能指定AFTER和BEFOR。

?

3.系统触发器系统触发器也叫事件触发器，是基于ORACLE系统事件或者客户事件所建立的触发器。事件触发器可细分为DDL事件触发器和数据库事件触发器：

1）DDL事件触发器

--CREATE：创建新对象时触发

--ALTER：修改数据库或数据库对象时触发

--DROP：删除对象时触发

2）数据库事件触发器，包括服务器启动/关闭、用户登录/注销和服务器错误等

--STARTUP：数据库打开时触发

--SHUTDOWN：在使用NORMAL或IMMEDIATE选项关闭数据库时触发

--LOGON：当用户连接到数据库并建立会话时触发

--LOGOFF：当一个会话从数据库中断开时触发

--SERVERERROR：发生服务器错误时触发

?

四、实例演示

以下是结合以上理论通过实际例子来演示一下三种触发器的实现代码。

1.数据结构

表说明：subject_info，科目表；class_info，班级表；student_info，学生信息表，有姓名、性别、年龄、学号、身份证号字段；history_student，学生信息历史表，作用是当学生退学或转学时等需要删除学生信息的，则将该学生的信息移到历史表；score_info，成绩表，有科目、分数、班级、学生字段；history_score，历史分数表，当需要删除学生分数时，需要将学生分数信息备份到该表。测试数据如下图：

?

?

2.关键词解析

:old，:new，称为伪记录，用来访问数据变更前后的值。但需要注意，INSERT语句插入一条语句时没有:old记录，DELETE语句删除一条已经存在的记录时没有:new记录，UPDATE既有:old记录也有:new记录。使用方法为:old.列名和:new.列名。

?

3.DML触发器

需求：当某个学生退学、转学等，需要将该学生的信息从学生信息表中删除，在删除前需要备份该学生的信息以及成绩信息。

Sql代码?

1. create?or?replace?trigger?dml_trigger_test??
2. ??before?delete???????????--删除前触发??
3. on?wst.student_info???--指定表??
4. ??for?each?row????????????--行级触发器??
5. declare??
6. ??version???number;--版本??
7. ??classid???number;?--班级id??
8. ??subjectid?number;--科目id??
9. ??score?????number(4,?1);--分数??
10. ??--将要删除的学生的成绩放入游标??
11. ??cursor?cur_score?is??
12. ????select?t.class_id,?t.subject_id,?t.score??
13. ??????from?score_info?t??
14. ?????where?t.student_id?=?:old.id;??
15. begin??
16. ???--历史版本??
17. ??select?max(t_history.version)??
18. ????into?version??
19. ????from?wst.history_student?t_history??
20. ???where?t_history.student_id?=?:old.id;??
21. ??case??
22. ????when?version?is?null?then??
23. ??????version?:=?0;??
24. ????else??
25. ??????version?:=?version;??
26. ??end?case;??
27. ??--备份学生信息??
28. ??insert?into?wst.history_student??
29. ????(history_student.id,??
30. ?????history_student.student_id,??
31. ?????history_student.student_number,??
32. ?????history_student.idcard,??
33. ?????history_student.version,??
34. ?????history_student.createdate)??
35. ??values??
36. ????(seq_history_student.nextval,??
37. ?????:old.id,??
38. ?????:old.student_number,??
39. ?????:old.idcard,??
40. ?????version?+?1,??
41. ?????sysdate);??
42. ??--备份成绩信息?????
43. ??for?cs?in?cur_score?loop??
44. ????classid???:=?cs.class_id;??
45. ????subjectid?:=?cs.subject_id;??
46. ????score?????:=?cs.score;??
47. ????insert?into?wst.history_score??
48. ??????(history_score.id,??
49. ???????history_score.class_id,??
50. ???????history_score.subject_id,??
51. ???????history_score.student_id,??
52. ???????history_score.version,??
53. ???????history_score.score,??
54. ???????history_score.createdate)??
55. ????values??
56. ??????(seq_history_score.nextval,??
57. ???????classid,??
58. ???????subjectid,??
59. ???????:old.id,??
60. ???????version?+?1,??
61. ???????score,??
62. ???????sysdate);delete?from?student_info?t?where?t.id=1;??
63. ???
64. ??end?loop;??
65. ??--删除分数信息??
66. ??delete?from?score_info?t?where?t.student_id?=?:old.id;??
67. end;??

测试：delete from student_info t where t.id=1;

?

4.替代触发器（INSTEAD OF）

需求：在学生成绩统计视图里删除数据（通常是不会在视图里直接删除数据的，这里只是为了说明触发器的使用）。

分析：由于不能对复杂视图进行直接DML操作，所以通过替代触发器间接删除基表进行操作，达到从视图删除数据的目的。因为替代触发器是基于视图的，所以要先创建好视图。

创建视图代码：

create or replace view view_test as?

Sql代码?

1. select?t2.name?班级,??
2. ???????t1.student_number?as?学号,??
3. ???????t1.name?as?姓名,??
4. ???????sum(case??
5. ?????????????when?t3.name?=?'语文'?then??
6. ??????????????t.score??
7. ?????????????else??
8. ??????????????0??
9. ???????????end)?as?语文,??
10. ???????sum(case??
11. ?????????????when?t3.name?=?'数学'?then??
12. ??????????????t.score??
13. ?????????????else??
14. ??????????????0??
15. ???????????end)?as?数学,??
16. ???????sum(case??
17. ?????????????when?t3.name?=?'英语'?then??
18. ??????????????t.score??
19. ?????????????else??
20. ??????????????0??
21. ???????????end)?as?英语,??
22. ???????sum(case??
23. ?????????????when?t3.name?=?'物理'?then??
24. ??????????????t.score??
25. ?????????????else??
26. ??????????????0??
27. ???????????end)?as?物理,??
28. ???????sum(case??
29. ?????????????when?t3.name?=?'化学'?then??
30. ??????????????t.score??
31. ?????????????else??
32. ??????????????0??
33. ???????????end)?as?化学,??
34. ???????sum(case??
35. ?????????????when?t3.name?=?'历史'?then??
36. ??????????????t.score??
37. ?????????????else??
38. ??????????????0??
39. ???????????end)?as?历史,??
40. ???????sum(case??
41. ?????????????when?t3.name?=?'地理'?then??
42. ??????????????t.score??
43. ?????????????else??
44. ??????????????0??
45. ???????????end)?as?地理,??
46. ???????sum(case??
47. ?????????????when?t3.name?=?'政治'?then??
48. ??????????????t.score??
49. ?????????????else??
50. ??????????????0??
51. ???????????end)?as?政治,??
52. ???????sum(case??
53. ?????????????when?t3.name?=?'生物'?then??
54. ??????????????t.score??
55. ?????????????else??
56. ??????????????0??
57. ???????????end)?as?生物,??
58. ???????round(avg(t.score),?1)?as?平均分,??
59. ???????sum(t.score)?as?总分??
60. ??from?score_info?t??
61. ?inner?join?student_info?t1?on?t.student_id?=?t1.id??
62. ??join?class_info?t2?on?t2.id?=?t.class_id??
63. ??join?subject_info?t3?on?t3.id?=?t.subject_id??
64. ?group?by?t1.student_number,?t1.name,?t2.name??
65. ?order?by?t2.name?asc??

?

?触发器代码：

Sql代码?

1. create?or?replace?trigger?update_view_trigger??
2. ??instead?of?delete?on?view_test??
3. begin??
4. ??delete?score_info?s??
5. ???where?s.student_id?=?(select?si.id??
6. ???????????????????????????from?student_info?si??
7. ??????????????????????????where?si.student_number?=?:old.学号);??
8. end;??

测试：delete from view_test v where v.学号='201301003';

?

?

5.系统触发器

需求：用户登录成功后，记录用户名以及登录时间。

分析：用户登录成功后，说明触发时间是AFTER，事件则是logon，还要注意的是用户需要有ADMINISTERDATABASETRIGGER的权限才能创建事件触发器，一般只有系统管理员才拥有该权限。

Sql代码?

1. create?or?replace?trigger?sys_trigger_test??
2. ??after?logon?on?database??
3. begin??
4. ??insert?into?user_log??
5. ????(user_log.username,?user_log.logintime)??
6. ??values??
7. ????(sys.login_user,?sysdate);??
8. end;??

?

五、小结

触发器的几种使用例子上面都已经例举出来了，感觉挺简单的，在实际工作中根据不同的业务就没可能这么简单了，但再复杂的SQL语句都是一句一句的拼接起来的。触发器可以用来加强比正常的引用完整性约束条件更复杂的数据约束。ORACLE的触发器除了能够定义在表或视图上的DML操作之外，还可以根据数据库事件和DDL事件进行定义。

呼应文章开头触发器的功能，如下：

--允许/限制对表的修改；

--自动生成派生列，如自增字段；

--强制数据一致性；

--提供审计和日志记录；

--防止无效的事务处理；

--启用复杂的业务逻辑；

合理地使用触发器将大大加强数据库的安全性和数据处理能力；

?

再者，使用触发器应注意一下几点：

+DML触发器

--CREATE TRIGGER语句文本的字符长度不能超过32KB；

--触发器体内的SELECT?语句只能为SELECT … INTO …结构，或者为定义游标所使用的SELECT?语句；

--触发器中不能使用数据库事务控制语句?COMMIT; ROLLBACK, SVAEPOINT?语句；

--由触发器所调用的过程或函数也不能使用数据库事务控制语句；

--触发器中不能使用LONG, LONG RAW?类型；

--触发器内可以参照LOB?类型列的列值，但不能通过?:NEW?修改LOB列中的数据；

+替代触发器（INSTEAD OF）

--只能被创建在视图上，并且该视图没有指定WITH CHECK OPTION选项；

--不能指定BEFORE?或?AFTER选项；

--FOR EACH ROW子可是可选的，即INSTEAD OF触发器只能在行级上触发、或只能是行级触发器，无需指定；

--没有必要在针对一个表的视图上创建INSTEAD OF触发器，只要创—ML触发器就可以了

?

六、参考资料

《ORACLE PL/SQL完全自学手册》

1 楼 502220545 1 小时前 楼主 写的很细 很好 受教了