第八章理论课触发器和内置程序包
- 格式：pdf
- 大小：667.16 KB
- 文档页数：17
《Oracle数据库应用 》理论课
触发器和内置程序包
本章技能目标
理解和应用触发器
了解内置程序包
1. 触发器
触发器是特定事件出现的时候，自动执行的代码块。类似于存储过程，触发器与存储过程的区
别在于:存储过程是由用户或应用程序显式调用的,而触发器是不能被直接调用的。
触发器可以用于加载Oracle的默认功能，提供高度可定制的数据库，触发器能够执行的功
能有：
1、 允许/限制对表的修改
2、 自动生成派生列，比如自增字段
3、 强制数据一致性
4、 提供审计和日志记录
5、 防止无效的事务处理
6、 启用复杂的业务逻辑
触发器与特定的表或视图相关联，用于检查对表或视图所做的数据修改。无论正在执行操作的
用户身份如何，触发器都能够在它们执行操作时生效。当INSERT，DELETE，UPADTE等事件
发生在表或视图中时，就会激活触发器的代码。 用于创建触发器的语法为：
CREATE [OR REPLACE] TRIGGER trigger_name AFTER | BEFORE | INSTEAD OF [INSERT] [[OR] UPDATE [OF column_list]] [[OR] DELETE] ON table_or_view_name [REFERENCING {OLD [AS] old / NEW [AS] new}] [FOR EACH ROW] [WHEN (condition)] pl/sql_block;
其中： 第8章 触发器和内置程序包
- 2
– before和after：指在事件发生之前或之后激活触发器。
instead of：如果使用此子句，表示可以执行触发器代码来代替导致触发器调用的事件。
insert、delete和update：指定构成触发器事件的数据操纵类型，update还可以制定列的列表。
referencing：指定新行（即将更新）和旧行（更新前）的其他名称，默认为new和old。
table_or_view_name：指要创建触发器的表或视图的名称。
for each row：指定是否对受影响的每行都执行触发器，即行级触发器，如果不使用此子句，则
为语句级触发器。
when：限制执行触发器的条件，该条件可以包括新旧数据值得检查。
declare---end：是一个标准的PL/SQL块。
例1演示如何在SCOTT模式的EMP表上创建触发器。
例1： CREATE OR REPLACE TRIGGER biu_emp_deptno BEFORE INSERT OR UPDATE OF deptno ON emp FOR EACH ROW WHEN (New.deptno <> 40) BEGIN :New.comm := 0; END; /
提示：如果创建触发器时出现错误，请使用SHOW ERRORS命令查看详细的错误信息。
1.1 触发器的组成部分
触发器由三部分组成：
触发器语句（事件）
定义激活触发器的 DML 事件和 DDL 事件
触发器限制
执行触发器的条件，该条件必须为真才能激活触发器
触发器操作（主体）
包含一些 SQL 语句和代码，它们在发出了触发器语句且触发限制的值为真时运
行
触发器语句
触发器语句是那些可以导致触发器的事件，即在表或视图上执行的INSERT，DELETE，UPADTE
之类的DML语句，在模式对象上执行的DDL语句或数据库事件。
在例1触发器中，第2行至第四行，如下代码所示，构成了触发器语句。
BEFORE INSERT OR UPDATE OF deptno ON emp FOR EACH ROW
这就是说，触发器会在这些事件之前隐式执行：当对EMP表执行INSERT语句时或对EMP表
的deptno列进行UPADTE时。而且触发器将在受到影响的每一行上执行一次。
但是触发器是否真正的执行还要检查触发器的条件，只有满足限制条件才会执行。
触发器限制
触发器限制条件包含一个布尔表达式，该值必须为“真”才能激活触发器。如果该值为“假”第8章 触发器和内置程序包
- 3
– 或“未知”，将不运行触发器操作。 例1中的触发器的限制如下面的代码所示。
WHEN (New.deptno <> 40)
这就是说，如果列deptno的新值不等于40，触发器将会执行。
触发器操作
触发器操作是触发器的主体，包含一些SQL语句和代码。这些代码在执行触发器语句且触发器
限制条件的值为“真”时运行。行级触发器允许触发操作中的语句访问行的列值。
例1中的触发器操作如下面的代码所示 BEGIN :New.comm := 0; END;
以上这段代码将列comm新增设置为0。
现在，来测试此触发器产生的效果。例2演示了如何在EMP表中插入记录。
例2： INSERT INTO emp ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ( 8000, 'Jack', 'ANALYST', NULL, '31-12月-2003', 5000, 600, 20);
INSERT INTO emp ( EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
VALUES ( 8001, 'Fred', 'MANAGER', NULL, '31-12月-2002', 6000, 500, 40);
COMMIT;
上述语句在EMP表中插入了两条记录。并为每行记录指定了COMM列的值。用户在表中插入
了值，这将激活前面创建的触发器。
现在，通过下面的命令查看实际插入到表中的数据。
SELECT ename, sal, comm, deptno FROM emp WHERE empno>=8000;
上述语句的输出结果是：
ENAME SAL COMM DEPTNO
---------- --------- --------- ------
Jack 5000.00 0.00 20
Fred 6000.00 500.00 40
可以看到，插入第一行数据时指定了COMM列的值为600。但是DEPTNO的值不等于40，所
以会激活触发器，COMM列的值被设置为0。插入第二行记录时，由于DEPTNO的值等于40，
不满足触发器限制条件，所以触发器不执行，用户指定的COMM列的值插入到数据库中。
1.2 触发器类型
Oracle具有不同类型的触发器，可以实现不同的任务。这些触发器类型包括：
语句触发器，行触发器，INSTEAD OF 触发器，模式触发器，数据库级触发器
分类如下图： 第8章 触发器和内置程序包
- 4
–
DDL 触发器：在模式中执行 DDL 语句时执行
数据库级触发器：在发生打开、关闭、登录和退出数据库等系统事件时执行
DML 触发器：在对表或视图执行DML语句时执行
语句级触发器：无论受影响的行数是多少，都只执行一次
行级触发器：对DML语句修改的每个行执行一次
INSTEAD OF 触发器：用于用户不能直接使用 DML 语句修改的视图
具体叙述如下：
1. 行级触发器
行级触发器对DML语句影响的每个行执行一次，例如：UPADTE语句影响多行，就会对每行
都激活一次触发器。行触发器是触发器中最常用的一种。通常用于数据库审计和实现复杂的业
务逻辑。可以在CREATE TRIGGER命令中指定FOR EACH ROW子句创建行级触发器。
由于触发器是事件驱动的，因此可以设置触发器在这些事件之前或之后执行，即在执行DML
语句之前或之后执行。在触发器中，可以引用DML语句中涉及的旧值和新值。“新”是指由
DML语句创建的数据值（如插入记录中的列）。
执行DML语句之前的值的默认名称是 :old ,之后的值是 :new。
insert 操作只有:new
delete 操作只有 :old
update 操作两者都有
如果需要通过触发器在插入行中设置一个列值，就应该使用BEFORE INSERT 触发器访问新值。
使用AFTER INSERT不允许设置插入值，因为该行已经插入表中。
在审计应用程序中经常使用AFTER行级触发器，直到行被修改才会触发它们。行的成功修改表
明此行已经通过该表定义的完整性约束。
例3演示了行级触发器的应用。
例3： CREATE TABLE TEST_TRG (ID NUMBER, NAME VARCHAR2(20)); CREATE SEQUENCE SEQ_TEST; CREATE OR REPLACE TRIGGER BI_TEST_TRG BEFORE INSERT OR UPDATE OF ID ON TEST_TRG FOR EACH ROW BEGIN IF INSERTING THEN 第8章 触发器和内置程序包
- 5 – SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL; ELSE
RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值！');
END IF; END; / INSERT INTO TEST_TRG VALUES (0, 'First row'); INSERT INTO TEST_TRG VALUES (0, 'Second row'); INSERT INTO TEST_TRG VALUES (0, 'Third row'); COMMIT;
例3先创建一个测试表TEST_TRG，接着创建一个序列SEQ_TEST，然后创建一个触发器自动生成ID列的值，并禁止UPADTE表的ID列。最后，向测试表中插入3条记录。现在，使用以
下语句查看表中的数据。
SELECT * FROM TEST_TRG;
上述语句的输出结果是：
ID NAME
---------- --------------------
1 First row
2 Second row
3 Third row
可以看出，表的ID列的值是由触发器自动产生的，而不是用户在插入命令中指定值。
提示：如果一个触发器由多种语句的触发，可以使用INSERTING，UPDATING，DELETING
条件谓词来检查，如果值为“真”，那么就是相应得语句类型激活了该触发器。条件谓词只能在
触发器主体中引用。
begin if inserting then ----- elsif updating then ----- elsif deleting then ------ end if; end; if updating(‘COL1’) or updating(‘COL2’) then ------ end if;
2. 语句级触发器
语句级触发器对每个DML语句执行一次。例如，如果一条INSERT语句在表中插入200行，
那么这个表上的INSERT语句级触发器只执行一次。语句级触发器不常用于与数据库相关的活
动，通常用于强制实施在表 上执行操作的额外安全性措施。语句级触发器是CREATE
