Oracle 数据库事件触发器

数据库事件触发器有数据库级和模式级两种。前者定义在整个数据库上，触发事件是数据库事件，如数据库的启动、关闭，对数据库的登录或退出。后者定义在模式上，触发事件包括模式用户的登录或退出，或对数据库对象的创建和修改(DDL事件)。
数据库事件触发器的触发事件的种类和级别如表9-3所示。

Sql代码

1. 种   类     关 键 字    说     明   
2. 模式级 CREATE   在创建新对象时触发   
3.     ALTER    修改数据库或数据库对象时触发   
4.     DROP     删除对象时触发   
5. 数据库级     STARTUP 数据库打开时触发   
6.      SHUTDOWN     在使用NORMAL或IMMEDIATE选项关闭数据库时触发   
7.      SERVERERROR      发生服务器错误时触发   
8. 数据库级与模式级     LOGON    当用户连接到数据库，建立会话时触发   
9.      LOGOFF   当会话从数据库中断开时触发  

种  类 关 键 字 说    明
模式级 CREATE 在创建新对象时触发
 ALTER 修改数据库或数据库对象时触发
 DROP 删除对象时触发
数据库级 STARTUP 数据库打开时触发
 SHUTDOWN 在使用NORMAL或IMMEDIATE选项关闭数据库时触发
 SERVERERROR  发生服务器错误时触发
数据库级与模式级 LOGON  当用户连接到数据库，建立会话时触发
 LOGOFF 当会话从数据库中断开时触发

定义数据库事件和模式事件触发器
创建数据库级触发器需要ADMINISTER DATABASE TRIGGER系统权限，一般只有系统管理员拥有该权限。
对于模式级触发器，为自己的模式创建触发器需要CREATE TRIGGER权限，如果是为其他模式创建触发器，需要CREATE ANY TRIGGER权限。
数据库事件和模式事件触发器的创建语法与DML触发器的创建语法类似。数据库事件或模式事件触发器的创建语法如下：
CREATE [OR REPLACE] TRIGGER 触发器名
{BEFORE|AFTER }
{DDL事件1 [DDL事件2...]| 数据库事件1 [数据库事件2...]}
ON {DATABASE| [模式名.]SCHEMA }
[WHEN (条件)]
DECLARE
声明部分
BEGIN
主体部分
END;
其中：DATABASE表示创建数据库级触发器，数据库级要给出数据库事件；SCHEMA表示创建模式级触发器，模式级要给出模式事件或DDL事件。
在数据库事件触发器中，可以使用如表9-4所示的一些事件属性。不同类型的触发器可以使用的事件属性有所不同。

Sql代码

1. 属   性     适用触发器类型 说     明   
2. Sys.sysevent     所有类型     返回触发器触发事件字符串   
3. Sys.instance_num     所有类型     返回Oracle实例号   
4. Sys.database_name    所有类型     返回数据库名字   
5. Sys.server_error(stack_position)     SERVERERROR 从错误堆栈指定位置返回错误号，参数为1表示最近的错误   
6. Is_servererror(error_number)     SERVERERROR 判断堆栈中是否有参数指定的错误号   
7. Sys.login_user   所有类型     返回导致触发器触发的用户名   
8. Sys.dictionary_obj_type CREATE、ALTER、DROP    返回DDL触发器触发时涉及的对象类型   
9. Sys. dictionary_obj_name    CREATE、ALTER、DROP    返回DDL触发器触发时涉及的对象名称   
10. Sys.des_encrypted_password  CREATE、ALTER、DROP    创建或修改用户时，返回加密后的用户密码  

属  性 适用触发器类型 说    明
Sys.sysevent 所有类型 返回触发器触发事件字符串
Sys.instance_num 所有类型 返回Oracle实例号
Sys.database_name 所有类型 返回数据库名字
Sys.server_error(stack_position) SERVERERROR 从错误堆栈指定位置返回错误号，参数为1表示最近的错误
Is_servererror(error_number) SERVERERROR 判断堆栈中是否有参数指定的错误号
Sys.login_user 所有类型 返回导致触发器触发的用户名
Sys.dictionary_obj_type CREATE、ALTER、DROP 返回DDL触发器触发时涉及的对象类型
Sys. dictionary_obj_name CREATE、ALTER、DROP 返回DDL触发器触发时涉及的对象名称
Sys.des_encrypted_password CREATE、ALTER、DROP 创建或修改用户时，返回加密后的用户密码

数据库事件触发器
下面是一个综合的数据库事件触发器练习。先为STUDENT账户授予创建数据库事件触发器的权限，ADMINISTER DATABASE TRIGGER，然后创建有关的表和触发器，最后予以验证。
【训练1】 创建触发器，对本次数据库启动以来的用户登录时间进行记录，每次数据库启动后，先清空该表。
步骤1：创建登录事件记录表：

Sql代码

1. CREATE TABLE userlog (   
2. USERNAME VARCHAR2(20),   
3. LOGON_TIME DATE);  

CREATE TABLE userlog (
USERNAME VARCHAR2(20),
LOGON_TIME DATE);

执行结果：

Sql代码

1. 表已创建。  

表已创建。

步骤2：创建数据库STARTUP事件触发器：

Sql代码

1. CREATE OR REPLACE TRIGGER INIT_LOGON   
2. AFTER  
3. STARTUP   
4. ON DATABASE  
5. BEGIN  
6. DELETE FROM userlog;   
7. END;  

CREATE OR REPLACE TRIGGER INIT_LOGON
AFTER
STARTUP 
ON DATABASE
BEGIN
 DELETE FROM userlog;
END;

执行结果：

Sql代码

1. 触发器已创建。  

触发器已创建。

步骤3：创建数据库LOGON事件触发器：

Sql代码

1. CREATE OR REPLACE TRIGGER DATABASE_LOGON   
2. AFTER  
3. LOGON   
4. ON DATABASE  
5. BEGIN  
6. INSERT INTO userlog   
7. VALUES(sys.login_user,sysdate);   
8. END;  

CREATE OR REPLACE TRIGGER DATABASE_LOGON
AFTER
LOGON 
ON DATABASE
BEGIN
 INSERT INTO userlog 
 VALUES(sys.login_user,sysdate);
END;

执行结果：

Sql代码

1. 触发器已创建。  

触发器已创建。

步骤4：验证DATABASE_LOGON触发器：

Sql代码

1. CONNECT SCOTT/TIGER@MYDB;   
2.         CONNECT STUDENT/STUDENT@MYDB;  

CONNECT SCOTT/TIGER@MYDB;
  CONNECT STUDENT/STUDENT@MYDB;

执行结果：

Sql代码

1. 已连接。   
2. 已连接。  

已连接。
已连接。

执行查询：

Sql代码

1. SELECT username,TO_CHAR(logon_time,'YYYY/MM/DD HH24:MI:SS') FROM userlog;  

SELECT username,TO_CHAR(logon_time,'YYYY/MM/DD HH24:MI:SS') FROM userlog;

执行结果：

Sql代码

1. USERNAME              TO_CHAR(LOGON_TIME,   
2. ----------------------------- -----------------------------------------   
3. SCOTT                    2004/03/29 22:42:20   
4.          STUDENT                  2004/03/29 22:42:20  

USERNAME             TO_CHAR(LOGON_TIME,
----------------------------- -----------------------------------------
SCOTT                 2004/03/29 22:42:20
  STUDENT               2004/03/29 22:42:20

步骤5：验证INIT_LOGON触发器。
重新启动数据库，登录STUDENT账户：

Sql代码

1. SELECT username,TO_CHAR(logon_time,'YYYY/MM/DD HH24:MI:SS') FROM userlog;  

SELECT username,TO_CHAR(logon_time,'YYYY/MM/DD HH24:MI:SS') FROM userlog;

执行结果：

Sql代码

1. USERNAME              TO_CHAR(LOGON_TIME,   
2. -------------------------------- ---------------------------------------   
3.          STUDENT               2004/03/29 22:43:59   
4.          已选择 1 行  

USERNAME             TO_CHAR(LOGON_TIME,
-------------------------------- ---------------------------------------
  STUDENT              2004/03/29 22:43:59
  已选择 1 行

说明：本例中共创建了两个数据库级事件触发器。DATABASE_LOGON在用户登录时触发，向表userlog中增加一条记录，记录登录用户名和登录时间。INIT_LOGON在数据库启动时触发，清除userlog表中记录的数据。所以当数据库重新启动后，重新登录STUDENT账户，此时userlog表中只有一条记录。
【训练2】 创建STUDENT_LOGON模式级触发器，专门记录STUDENT账户的登录时间：

Sql代码

1. CREATE OR REPLACE TRIGGER STUDENT_LOGON   
2. AFTER  
3. LOGON ON STUDENT.SCHEMA   
4. BEGIN     
5. INSERT INTO userlog   
6. VALUES(sys.login_user,sysdate);   
7. END;  

CREATE OR REPLACE TRIGGER STUDENT_LOGON 
AFTER
LOGON ON STUDENT.SCHEMA 
BEGIN  
 INSERT INTO userlog
 VALUES(sys.login_user,sysdate);
END;

执行结果：

Sql代码

1. 触发器已创建。  

触发器已创建。

说明：为当前模式创建触发器，可以省略SCHEMA前面的模式名。
【练习1】修改DATABASE_LOGON触发器和userlog表，增加对退出时间的记录。
DDL事件触发器
【训练1】 通过触发器阻止对emp表的删除。
步骤1：创建DDL触发器：

Sql代码

1. CREATE OR REPLACE TRIGGER NODROP_EMP   
2.           BEFORE   
3.         DROP ON SCHEMA   
4.         BEGIN  
5.          IF Sys.Dictionary_obj_name='EMP' THEN  
6. RAISE_APPLICATION_ERROR(-20005,'错误信息：不能删除emp表！');   
7.          END IF;   
8.         END;  

CREATE OR REPLACE TRIGGER NODROP_EMP
   BEFORE
   DROP ON SCHEMA 
  BEGIN
   IF Sys.Dictionary_obj_name='EMP' THEN
RAISE_APPLICATION_ERROR(-20005,'错误信息：不能删除emp表！');
   END IF; 
  END;

执行结果：

Sql代码

1. 触发器已创建。  

触发器已创建。

步骤2：通过删除emp表验证触发器：

Sql代码

1. DROP TABLE emp;  

DROP TABLE emp;

执行结果：

Sql代码

1. DROP TABLE emp   
2.          *   
3. ERROR 位于第 1 行:   
4.          ORA-00604: 递归 SQL 层 1 出现错误   
5.          ORA-20005: 错误信息：不能删除emp表！   
6.          ORA-06512: 在line 3