oracle数据库触发事件,Oracle 数据库事件触发器

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

数据库事件触发器的触发事件的种类和级别如表9-3所示。

Sql代码

种   类     关 键 字    说     明

模式级CREATE在创建新对象时触发

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

DROP删除对象时触发

数据库级     STARTUP 数据库打开时触发

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

SERVERERROR      发生服务器错误时触发

数据库级与模式级     LOGON    当用户连接到数据库，建立会话时触发

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代码

属   性     适用触发器类型 说     明

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_typeCREATE、ALTER、DROP返回DDL触发器触发时涉及的对象类型

Sys. dictionary_obj_nameCREATE、ALTER、DROP返回DDL触发器触发时涉及的对象名称

Sys.des_encrypted_passwordCREATE、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代码

CREATETABLEuserlog (

USERNAME VARCHAR2(20),

LOGON_TIMEDATE);

CREATE TABLE userlog (

USERNAME VARCHAR2(20),

LOGON_TIME DATE);

执行结果：

Sql代码

表已创建。

表已创建。

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

Sql代码

CREATEORREPLACETRIGGERINIT_LOGON

AFTER

STARTUP

ONDATABASE

BEGIN

DELETEFROMuserlog;

END;

CREATE OR REPLACE TRIGGER INIT_LOGON

AFTER

STARTUP

ON DATABASE

BEGIN

DELETE FROM userlog;

END;

执行结果：

Sql代码

触发器已创建。

触发器已创建。

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

Sql代码

CREATEORREPLACETRIGGERDATABASE_LOGON

AFTER

LOGON

ONDATABASE

BEGIN

INSERTINTOuserlog

VALUES(sys.login_user,sysdate);

END;

CREATE OR REPLACE TRIGGER DATABASE_LOGON

AFTER

LOGON

ON DATABASE

BEGIN

INSERT INTO userlog

VALUES(sys.login_user,sysdate);

END;

执行结果：

Sql代码

触发器已创建。

触发器已创建。

步骤4：验证DATABASE_LOGON触发器：

Sql代码

CONNECTSCOTT/TIGER@MYDB;

CONNECTSTUDENT/STUDENT@MYDB;

CONNECT SCOTT/TIGER@MYDB;

CONNECT STUDENT/STUDENT@MYDB;

执行结果：

Sql代码

已连接。

已连接。

已连接。

已连接。

执行查询：

Sql代码

SELECTusername,TO_CHAR(logon_time,'YYYY/MM/DD HH24:MI:SS')FROMuserlog;

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

执行结果：

Sql代码

USERNAME              TO_CHAR(LOGON_TIME,

----------------------------- -----------------------------------------

SCOTT                    2004/03/29 22:42:20

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代码

SELECTusername,TO_CHAR(logon_time,'YYYY/MM/DD HH24:MI:SS')FROMuserlog;

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

执行结果：

Sql代码

USERNAME              TO_CHAR(LOGON_TIME,

-------------------------------- ---------------------------------------

STUDENT               2004/03/29 22:43:59

已选择 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代码

CREATEORREPLACETRIGGERSTUDENT_LOGON

AFTER

LOGONONSTUDENT.SCHEMA

BEGIN

INSERTINTOuserlog

VALUES(sys.login_user,sysdate);

END;

CREATE OR REPLACE TRIGGER STUDENT_LOGON

AFTER

LOGON ON STUDENT.SCHEMA

BEGIN

INSERT INTO userlog

VALUES(sys.login_user,sysdate);

END;

执行结果：

Sql代码

触发器已创建。

触发器已创建。

说明：为当前模式创建触发器，可以省略SCHEMA前面的模式名。

【练习1】修改DATABASE_LOGON触发器和userlog表，增加对退出时间的记录。

DDL事件触发器

【训练1】 通过触发器阻止对emp表的删除。

步骤1：创建DDL触发器：

Sql代码

CREATEORREPLACETRIGGERNODROP_EMP

BEFORE

DROPONSCHEMA

BEGIN

IF Sys.Dictionary_obj_name='EMP'THEN

RAISE_APPLICATION_ERROR(-20005,'错误信息：不能删除emp表！');

ENDIF;

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代码

触发器已创建。

触发器已创建。

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

Sql代码

DROPTABLEemp;

DROP TABLE emp;

执行结果：

Sql代码

DROPTABLEemp

*

ERROR 位于第 1 行:

ORA-00604: 递归 SQL 层 1 出现错误

ORA-20005: 错误信息：不能删除emp表！

ORA-06512: 在line 3