PLSQL入门与精通（第72章：LOGOFF触发器）

上一次我们介绍了登录触发器，本次我们介绍注销触发器。
我们这里的注销指的是退出回话或者退出回话的意思。
注销触发器是用户退出回话或者数据库数据库时启动的触发器。
他是会话结束前的最后一步处理。

登录触发器和注销触发器都是属于同一类别的触发器，他们有以下
2个最大的差别：

1.登录触发器在SYS用户下是不能被启动的，注销触发器在SYS用户下是可以被启动的。
2.在登录触发器中，如果发生例外，例外没有处理的话，则无法登录，
但是在注销触发器中发生未处理的例外的话，用户照样能够退出回话。

首先，关于1.的部分，登录触发器SYS用户不能启动。理由如下：
触发器发生错误的时候无法登录，如果连超级管理员SYS也无法登录数据库
的话，数据库就无法操作了。

但是，任何用户登录数据库后，都是可以退出回话的。
所以，正如上边2.的说明的一样，注销触发器中，即使存在未处理的例外，
也可以注销（退出回话），应为任何权限的用户都可以退出回话，
不会导致数据库不能被使用的情况。

另外，在注销触发器中，及时发生未处理例外的错误，数据库退出处理不会被
终止，仍让会推出数据库的。这意味着注销触发器处理里边是不能防止用户退出
数据库的处理的。

一般情况下，触发器里边的处理如果发生例外，同时该例外有没有做特殊处理的话，
触发器里的应为例外SQL语句会被回滚。

但是要注意的是，注销触发器里边如果SQL语句发生例外的话，SQL语句是会被回滚的,
但是推出数据库的处理仍然会继续执行的。

在登录数据库的时候，处于安全的考虑，可以使用登录触发器器用语言检查非法用户的登录。
但是在退出回话的情况下，就没有这样的安全性需求了。

注销触发器一般不做检查行的处理，只是纯粹地在结束该会话时进行最后的处理。

我们来看看具体例子吧：

SCOTT.EMP表里有一个字段，用行触发器更新这个字段；
同时有一个包变量，这个包变量用于记录更新表里边行的次数，也就是每更新一行，这个包变量+1；
最后退出回话的时候用注销触发器将这个包里边的变量值（次数）记录在日志表中。

注销触发器将在会话结束时最后启动的，主要用于将会话中进行的操作的摘要信息记录到表中。

例子用到了注销触发器，还使用了包和行触发器。

SQL> SHOW USER

用户是“SCOTT”。

CREATE OR REPLACE PACKAGE SCOTT.PAC1
IS
CNT_EMP_UPD NUMBER;
END ;
/

包已创建。

首先，在SCOTT用户下创建了PAC1的包。

包里有一个CNT_EMP_UPD变量，用这个变量的值去更新SCOTT.EMP表里的行数。

回话期间要一直要使用这个变量的值，所以定义了包变量。
所以在会话最后的注销触发器直接可以使用这个值，把这个值直接更新到记录表中。

我们例子用的注销触发器程序是使用于任何用户的触发器。

DDL触发器、登录触发器、注销触发器的创建，如果想要任何用户都可以启动（ON DATABASE的触发器），
需要创建者具有“ADMINISTER DATABASE TRIGGER”的系统权限，本次用的是数据库管理员SYSTEM用户创建的，
默认具有这个权限。

例子里边的触发器用到了SCOTT.PAC1这个包里边的变量，所以需要SYSTEM用户具有使用该包的权限。

因此，我们需要对SYSTEM用户赋予该包的使用权限。

使用包对象的权限是“EXECUTE”权限，即使只是引用包中的变量也是EXECUTE权限。

对SYSTEM用户赋予该权限，应为该包是SCOTT用户创建的，所以包的权限也是由SCOTT用户
赋给SYSTEM用户的。

GRANT EXECUTE ON SCOTT.PAC1 TO SYSTEM;

授权成功。

这样，SYSTEM用户就可以使用SCOTT.PAC1这个包了。稍后由SYSTEM用户创建注销触发器，
改注销触发器就可以直接使用这个包的变量了。

接下来，我们在SCOTT用户的会话中，创建另一个触发器：更新SCOTT.EMP表的列（行数）的触发器。

1 CREATE OR REPLACE TRIGGER TRIG_CNT_EMP_UPD
2 AFTER UPDATE
3 ON EMP
4 FOR EACH ROW
5 BEGIN
6 PAC1.CNT_EMP_UPD := NVL(PAC1.CNT_EMP_UPD,0) + 1;
7 END ;
/

触发器已创建。

表的DML触发器有AFTER、BEFORE的类型触发器，有SQL文触发器和行触发器，我们例子用的是AFTER的行触发器，
更新这个表的时候，每更新一次在这个包变量上+1，简单来说这个包变量就是计数器。

想要达到这个行更新次数的计数器的目的，就需要用行触发器，不能用SQL触发器。
因为SQL文触发器是SQL语句执行的时候只能启动一次，甚至在SQL更新及时是0行的情况下也会启动SQL文触发器。
所以SQL文触发器是无法记录更新的记录件数的次数的。

如果是行触发器的话，实际上每一行数据被更新就会启动一次行触发器，所以用行触发器可以用计数器
一个一个记录下更新的行数。
（第4行的FOR EACH ROW指定行触发器）另外，这里用BEFORE或AFTER都可以。
在第5行中，使用了NVL函数。NVL表示空的时候设置为0。因为包变量PAC1.CNT_EMP_UPD第一次执行的时候没有定义初始值，
这时候他得值是NULL，与NULL运算后结果海为NULL，因此用NVL函数将NULL置换为0。

触发器以定义者的权限（如果是上面的触发器，则是SCOTT用户的权限）启动的，和表是一个用户。
因此，无论用哪个用户更新SCOTT.EMP表，上述行触发器都不会发生权限问题。

最后用SYSTEM用户创建注销触发器，用这个包SCOTT.PAC1变量插入到日志表里。
在此之前我们还需要创建一个日志表。

此表包含用户名、注销日期和上述计数器。

SQL> CONNECT SYSTEM/oracle

已连接。

SQL> SHOW USER

用户是“SYSTEM”。

CREATE TABLE LOG_TABLE
（USER_NAME VARCHAR2（100），–用户名
LOG_OFF_TIME DATE，–注销时间
CNT_EMP_UPD NUMBER) – 计数器：用户记录更新了SCOTT.EMP表的件数
/

表创建成功。

那么最后由SYSTEM用户创建注销触发器。

1 CREATE OR REPLACE TRIGGER SYSTEM.LOGOOFF_EMP_UPD_SAMARY
2 BEFORE LOGOFF
3 ON DATABASE
4 BEGIN
5 IF SCOTT.PAC1.CNT_EMP_UPD IS NOT NULL THEN
6 INSERT INTO SYSTEM.LOG_TABLE(USER_NAME, LOG_OFF_TIME, CNT_EMP_UPD)
7 VALUES ( USER ,SYSDATE,SCOTT.PAC1.CNT_EMP_UPD);
8 END IF;
9 END ;
/

触发器已创建。

首先，第二行，注销触发器的指定是的“BEFORE LOGOFF”。
如果是登录触发器的话，是“AFTER LOGON”，但是这次是注销触发器，所以是“BEFORE LOGOFF”。
第3行是“ON DATABASE”，无论哪个用户注销都会启动。
顺便说一下，如果将第3行设为“ON SCHEMA”，则该触发器只会在创建改触发器的用户，也就是
SYSTEM用户退出回话时才会被启动。

第4行开始的「BEGIN」以下是记述触发器处理的部分，称为触发器主体。
触发器本体为无名PL/SQL块，如果需要变量声明等，需要“DECLARE”块请，
这里没有变量，所以直接用的“BEGIN”。

在触发器主体中，如果包变量（PAC1.CNT_EMP_UPD）不是NULL，
则在LOG_TABLE表中INSERT用户名（USER）、注销时的日期时间（SYSDATE）以及该变量（UPDATE的行数）的值。

如果包变量是NULL，则表明在该会话中SCOTT.EMP表没有被UPDATE，所以什么都不需要处理。

我们马上验证一下吧。

首先，切换到SCOTT用户下，试着更新EMP表。(数据需要提前造好）

CONNECT SCOTT/tiger
已连接。

SCOTT用户已连接。

UPDATE EMP SET SAL = SAL + 1 WHERE DEPTNO = 10;
更新了三行。

SCOTT.EMP表更新了三行。

UPDATE EMP SET SAL = SAL * 1.1 WHERE EMPNO = 7934;
一行已更新。

又更新了一行。

至此，SCOTT用户SCOTT.EMP表总共更新了4行数据。

COMMIT ;
提交完成。

EXIT；–退出回话，注销触发器会被启动

因为EXIT发生了退出回话动作，会触发注销触发器，LOG_TABLE表应该记录了一条数据，
记录了SCOTT用户更新了4行数据。

接下来用HR用户测试一下。我们需要把SCOTT.EMP的对象权限赋给HR用户。

启动SQL*Plus，以SCOTT用户登录，赋予HR用户对应的权限。

SQL>CONNCT SCOTT/tiger SCOTT用户已连接。
GRANT UPDATE ON SCOTT.EMP TO HR;
授权成功。

这时HR用户可以对SCOTT.EMP表进行更新了。

我们接着用HR用户连接数据库，对SCOTT.EMP表进行UPDATE，再退出回话。

CONNECT HR/hr
已连接。

UPDATE SCOTT.EMP SET ENAME = LOWER (ENAME) WHERE DEPTNO = 20;
更新了5行。（数据提前造好的）

COMMIT ;
提交完成。

EXIT；–退出回话，注销触发器被启动

这样一来，HR用户更新了SCOTT.EMP表的5行数据，最后EXIT处理的注销触发器将其更新件数记录在LOG_TABLE表中。

我们用SYSTEM用户登录，产看一下LOG_TABLE表的数据。

SQL>CONNECT SYSTEM/oracle
已连接。

ALTER SESSION SET NLS_DATE_FORMAT= ‘YY/MM/DD HH24:MI:SS’ ;
会话已更改。

SELECT * FROM LOG_TABLE;

USER_NAME LOG_OFF_TIME CNT_EMP_UPD

---

SCOTT 21/03/28 21:47:56 4
HR 21/03/28 21:48:35 5

正如您所看到的，增加了2条数据，分别是SCOTT用户和HR用户退出回话时增加的，
退出回话的日期和更新的EMP表的行数（4行和5行）也分别被记录下来了。

这里需要注意的，假如SCOTT.EMP的UPDATE语句出错会被ROLLBACK，包变量的值也不会恢复原样，
所以如果有ROLLBACK，LOG_TABLE表中记录的行数的值就不正确了。
如果保证正确的值，这种实现方式就不对了。

这次就到此为止。