使用PL/SQL编写触发器
一、PL/SQL的事务控制
Oracle的事务是隐式的
事务的开始位置是从前一个事务结束以后执行的第一
条SQL语句，或者在连接到该数据库以后所执行的第
一条SQL语句。
事务的结束使用COMMIT或ROLLBACK语句显式标识。
通过设置保存点，可以仅仅撤销部分事务。设置保存
点的语法为：
SAVEPOINT name
；
二、PL/SQL的异常处理
当发生错误时，称为一个异常被抛出，程序无条件转
到异常处理部分。
允许声明其他异常条件类型以扩展异常处理，这种扩
展使PL/SQL的异常处理非常灵活。
异常分为系统预定义异常和用户自定义异常两种。
预定义异常不需声明，在运行时能自动引发异常处理
程序
1.用户自定义异常
用户自定义异常必须在定义部分进行声明。
当用户自定义异常发生时，系统不能自动触发，需要
用户使用RAISE语句显式引发。
声明自定义异常语法如下：
exception_name EXCEPTION;
2.抛出异常
有三种方式抛出异常：
通过PL/SQL运行时引擎
使用RAISE语句
调用RAISE_APPLICATION_ERROR存储过程
RAISE语句的语法格式为：
RAISE exception_name;
3.处理异常
PL/SQL程序块的异常部分包含程序处理错误的代码。
一旦程序进入异常部分就不能再回到同一块的执行部
分。
异常处理部分的一般语法：
EXCEPTION
WHEN exception_name THEN
Code for handing exception_name
[WHEN another_exception THEN
Code for handing another_exception]
[WHEN others THEN
code for handing any other exception]
例子：
每一个班级注册的人数不能超过一个上限
(classes.max_students)。写一段程序，检查历史系
开设的101课程目前已注册的人数是否已超过这个最
大人数的限制。如果超出，则抛出一个异常，并在日
志表(log_table)中进行记录。
DECLARE
e_TooManyStudents EXCEPTION;
v_CurrentStudents NUMBER(3);
v_MaxStudents NUMBER(3);
BEGIN
SELECT current_students, max_students
INTO v_CurrentStudents, v_MaxStudents
FROM classes
WHERE department = 'HIS' AND course = 101;
IF v_CurrentStudents > v_MaxStudents THEN
/* 如果已注册学生超出了上限，则抛出异常 */
RAISE e_TooManyStudents;
END IF;
EXCEPTION
WHEN e_TooManyStudents THEN
/*检测到e_TooManyStudents 时，将异常信息插入到日志表中*/
INSERT INTO log_table (info) VALUES ('History 101 has ' || v_CurrentStudents ||
'students: max allowed is ' || v_MaxStudents);
WHEN OTHERS THEN
/* 检测到其他异常时，将异常信息插入到日志表中*/
INSERT INTO log_table (info) VALUES ('Another error occurred');
END;4.内置异常
RAISE_APPLICATION_ERROR
也可以使用内置过程RAISE_APPLICATION_ERROR创建
用户自己的错误消息。
语法如下：
RAISE_APPLICATION_ERROR(error_number,error_message,[keep_errors]);
error_number是从-20000到-20999之间的整数，
error_message是与此错误相关的正文。
例子：
在每个新学生注册之前，要检查要注册的班级是否已
达到人数的上限。如果达到了，则不予注册，并返回
错误消息。创建一个存储过程解决上述问题，过程中
以学生学号、系和课程编号作为参数带入。
CREATE OR REPLACE PROCEDURE Register (
p_StudentID IN students.id%TYPE,
p_Department IN classes.department%TYPE,
p_Course IN classes.course%TYPE) AS
v_CurrentStudents NUMBER;
v_MaxStudents NUMBER;
BEGIN
SELECT current_students, max_students
INTO v_CurrentStudents, v_MaxStudents
FROM classes
WHERE course = p_Course
AND department = p_Department;
IF v_CurrentStudents + 1 > v_MaxStudents THEN
RAISE_APPLICATION_ERROR(-20000, 'Can‘t add more students to ' ||
p_Department || ' ' || p_Course);
END IF;
ClassPackage.AddStudent(p_StudentID, p_Department, p_Course);
EXCEPTION
WHEN NO_DATA_FOUND THEN
/*用户要查询的班级信息不存在，则返回下述错误信息*/
RAISE_APPLICATION_ERROR(-20001, p_Department || ' ' || p_Course ||
' doesn''t exist!');
END Register; 14三、触发器
1.创建触发器
触发器事件是在数据库表上执行的更新(INSERT、
UPDATE、DELETE)操作。
触发器的作用主要有以下几点：
维护不可能在表创建时刻通过声明性约束进行的复杂的完整 性约束限制。
通过记录所进行的修改以及谁进行了修改来审计（audit）表中的信息。
当表被修改的时候，自动给其他需要执行操作的程序发送信号。
语法
create [or replace] trigger tri_name
[before | after | instead of] tri_event
on table_name | view_name | user_name | db_name
[for each row] [when tri_condition]
begin
plsql_sentences;
end tri_name;例子：
数据库中有一张major_stats表，包含了每个专业学
生的总人数和总学分。要求跟踪最新的major_stats
状态，在每次students表被修改的时候都会更新
major_stats表。
CREATE OR REPLACE TRIGGER UpdateMajorStats
AFTER INSERT OR DELETE OR UPDATE ON students
DECLARE
CURSOR c_Statistics IS
SELECT major, COUNT(*) total_students,
SUM(current_credits) total_credits
FROM students
GROUP BY major;
BEGIN
FOR v_StatsRecord in c_Statistics LOOP
UPDATE major_stats
SET total_credits = v_StatsRecord.total_credits,
total_students = v_StatsRecord.total_students
WHERE major = v_StatsRecord.major;
/* 如果指定的专业不存在，则创建新的行 */
IF SQL%NOTFOUND THEN
INSERT INTO major_stats (major, total_credits, total_students)
VALUES (v_StatsRecord.major, v_StatsRecord.total_credits,
v_StatsRecord.total_students);
END IF;
END LOOP;
END UpdateMajorStats;2.语句级触发器
本实例要实现的主要功能是使⽤触发器在
SCOTT
模式下针对dept
表的各种操作进⾏监控，为此⾸先需要创建⼀个⽇志表dept_log，它⽤于存储对
dept
表的各种数据操作信息，⽐如操作种类（如插⼊、修改、删除操作）、操作时间等，下⾯就来创建这个⽇志信息表
create table dept_log
(
operate_tag varchar2(10), //定义字段，存
储操作种类信息
operate_time date //定义字段，存
储操作⽇期
);
创 建 ⼀ 个 触 发 器
tri_dept
， 该 触 发 器 在
insert
、update和
delete
事件下都可以被触发，并且操作的数据对象是
dept表，要求在触发器执⾏时输出对dept
表所做的具体操作，代码及运⾏
结果如下。
SQL> create or replace trigger tri_dept
2 before insert or update or delete
3 on dept //创建触发器，当dept表发⽣插⼊、修改、删除操作时引起该触发器执⾏
4 declare
5 var_tag varchar2(10); //声明⼀个变量，存储对dept表执⾏的操作类型
6 begin
7 if inserting then //当触发事件是INSERT时
8 var_tag := '插⼊'; //标识插⼊操作
9 elsif updating then //当触发事件是UPDATE时
10 var_tag := '修改'; //标识修改操作
11 elsif deleting then //当触发事件是DELETE时
12 var_tag := '删除'; //标识删除操作
13 end if;
14 insert into dept_log
15 values(var_tag,sysdate); //向⽇志表中插⼊对dept表的操作信息
16 end tri_dept;
17 /
触发器已创建条件谓词：
if inserting then //如果执⾏了插⼊操作，
即触发了insert事件
do something about insert
elsif updating then //如果执⾏了修改操作，即
触发了update事件
do something about update
elsif deleting then //如果执⾏了删除操作，即
触发了delete事件
do something about delete
end if;//判断某列
if updating(dname) then //若修改了dept表中的dname列
do something about update dname
end if;3.行级触发器
不⾔⽽喻，⾏级触发器会针对
DML
操作所影响的每⼀⾏数据都执⾏⼀次触发器。创建这种触发器时，必须在语法中使⽤for each
row
。使⽤⾏级触发器的⼀个典型应⽤就是给数据表⽣成主键值，下⾯就来讲解这个典型应⽤的实现过程。
为了使⽤⾏级触发器⽣成数据表中的主键值，⾸先需要创建⼀个带有主键列的数据表，来看下⾯的例⼦。
SQL> create table goods
2 (
3 id int primary key,
4 good_name varchar2(50)
5 );
表已创建
SQL> create sequence seq_id;
序列已创建
SQL> create or replace trigger tri_insert_good
2 before insert
3 on goods //关于goods数据表，在向其插⼊新记录之前，引起该触发器的运⾏
4 for each row //创建⾏级触发器
5 begin
6 select seq_id.nextval
7 into :new.id
8 from dual; //从序列中⽣成⼀个新的数值，赋值给当前插⼊⾏的id列
9 end;
10 /
触发器已创建4.替换触发器
替换触发器
——instead of
触发器，它的
“
触发时机
”
关键字是 instead of，⽽不是
before
或
after
。与其他类型触发器不同是，替换触发器定义在视图（⼀种数据库对象，在后⾯章节中会讲解到）上
的，⽽不是定义在表上。由于视图是由多个基表连接组成的逻辑结构，所以⼀般不允许⽤户进⾏DML
操作（如
INSERT
、
UPDATE
、DELETE等操作），这样当⽤户为视图编写
“
替换触发器
”
后，⽤户对视图的DML
操作实际上就变成了执⾏触发器中的
PL/SQL
语句块，这样就可以通过在“
替换触发器
”
中编写适当的代码对构成视图的各个基表进⾏操作。下⾯就通过⼀系列连续的例⼦来看⼀下创建和引发⼀个替换触发器的实现过程
SQL> create view view_emp_dept
2 as select empno,ename,dept.deptno,dname,job,hiredate
3 from emp,dept
4 where emp.deptno = dept.deptno;
视图已创建
创建⼀个关于view_emp_dept视图的替换触发器，
在该触发器的主体中实现向emp表和dept表中插⼊两⾏相互关联的数
据，代码及运⾏结果如下。
SQL> create or replace trigger tri_insert_view
2 instead of insert
3 on view_emp_dept //创建⼀个关于view_emp_dept视图的替换触发器
4 for each row //是⾏级视图
5 declare
6 row_dept dept%rowtype;
7 begin
8 select * into row_dept from dept where deptno =:new.deptno; //检索指定部门编号的记录⾏
9 if sql%notfound then //未检索到该部门编号的记录
10 insert into dept(deptno,dname)
11 values(:new.deptno,:new.dname); //向dept表中插⼊数据
12 end if;
13 insert into emp(empno,ename,deptno,job,hiredate)
14 values(:new.empno,:new.ename,:new.deptno,:new.job,:new.hiredate); //向emp表中插⼊数据
15 end tri_insert_view;
16 /
触发器已创建CREATE TRIGGER room_summary_delete
INSTEAD OF DELETE ON room_summary
FOR EACH ROW
BEGIN
DELETE FROM rooms
WHERE building = :old.building;
END room_summary_delete;5.删除和建筑触发器
删除触发器的语法是：
DROP TRIGGER trigger_name;
触发器可以被禁止激发。禁止激发触发器的语法是：
ALTER TRIGGER trigger_name [DISABLE | ENABLE];
禁止表上的所有触发器，例如：
ALTER TABLE students DISABLE ALL TRIGGERS
例子：
向表students中添加一条新记录时，学号ID由序列
student_sequence 自动生成。在表students上创建
一个触发器，使用序列student_sequence产生的新值
填写students的ID字段。
任务求解：
利用伪记录:new对字段赋值。
CREATE OR REPLACE TRIGGER GenerateStudentID
BEFORE INSERT ON students
FOR EACH ROW
BEGIN
SELECT student_sequence.nextval
INTO :new.ID
FROM dual;
END GenerateStudentID;when子句
下面定义的触发器CheckCredits仅当学生的学分多于
20的时候才被执行：
CREATE OR REPLACE TRIGGER CheckCredits
BEFORE INSERT OR UPDATE OF current-credits ON students
FOR EACH ROW
WHEN (new.current_credits>20)
BEGIN
/*Trigger body*/
END;
任务描述：
registered_students表中保存了学生选修某门课程
的成绩信息，对该表的修改非常重要。所有对该表的
更动情况都要记录到表RS_audit中。
任务求解：
在registered_students表上创建触发器，在对表进
行任何更新动作时，将更新前后的信息写入到表
RS_audit中。
CREATE OR REPLACE TRIGGER LogRSChanges
BEFORE INSERT OR DELETE OR UPDATE ON registered_students
FOR EACH ROW
DECLARE
v_ChangeType CHAR(1);
BEGIN
/* 'I' 代表 INSERT, 'D'代表DELETE, 'U'代表UPDATE。*/
IF INSERTING THEN
v_ChangeType := 'I';
ELSIF UPDATING THEN
v_ChangeType := 'U';
ELSE
v_ChangeType := 'D';
END IF;
INSERT INTO RS_audit
(change_type, changed_by, timestamp,
old_student_id, old_department, old_course, old_grade,
new_student_id, new_department, new_course, new_grade)
VALUES
(v_ChangeType, USER, SYSDATE,
:old.student_id, :old.department, :old.course, :old.grade,
:new.student_id, :new.department, :new.course, :new.grade);
END LogRSChanges; 四、序列
参考：
654
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
