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

本软件是一款 Oracle 客户端工具 - 专用于 Oracle 数据库操作: 多线程、多连接、智能/彩色SQL编辑器、中英文双语界面并能切换、支持 PL/SQL、批量SQL运行、高效的块操作、方便的表格操作,不需要安装 Oracle 客户端,能运行于所有的主流平台包括 Windows、Linux 、Unix 及 Mac OS,界面简洁、操作简便、功能实用且全面! 主要功能如下: o 用表格显示 SELECT 语句的查询结果,可以直接对查询结果进行再操作,如 修改、插入行、删除行、提交(将表格中的数据改变写入相应的数据库表中)、多功能拷贝、导出为 INSERT 语句、单条记录操作、方便的查找及替换功能、数字统计、行列移动、列排序、选择模式切换等;能方便地查看、编辑大型的文本字段(如 CLOB、 LONG) o 智能的SQL编辑器:自动弹出提示窗口,列出关键字、函数名、列名、对象名、对象类型、参数、包内的过程和函数,提高SQL编辑效率。例如: (1)输入字串“se”,两秒钟后列出以“se”开头的所有关键字及函数; (2)输入字串“abc”+“.”,如果“abc”是一表名/视图,或其别名,则列出表/视图的所有列名及数据类型;如果“abc”是一用户名,则列出该用户的所有对象;如果“abc”是一包名,则列出包内的过程和函数; (3)输入字串“grant”+空格,列出所有权限和角色,再输入“on”+空格,列出所有对象,再输入“to”+空格,列出所有用户名; (4)输入字串“drop”+空格,列出“drop”支持的所有对象类型,例如,选择“table”项再输入一空格,则又列出当前用户的所有表名; (5)当提示窗口出现时,只需输入部分字串,窗口能自动查找全串,比如,输入子串“name”,显示“first_name”项,按Ctrl+PdDn键则向下查找“last_name”项,按Ctrl+PdUp键则向上查找“first_name”项 o 彩色的SQL编辑器,增强可读性: (1)关键字、注释块、引号内字符串、操作符、特殊字符等,用不同的彩色字体加以区别; (2)如果光标前面是一个括号,高亮度显示配对的括号 o 支持批量SQL语句的运行:用监控器监控每条语句的运行,在运行过程中可以中断/暂停/继续正在运行的语句,甚至可以更正出错误的语句 o 方便高效的块操作,以满足特殊需求: (1)“Format SQL” 按钮能将语句块中杂乱语句以缩进的格式进行重新编排,即格式美化; (2)“Explain Plan” 按钮能快速地显示语句的执行计划; (3)“Export” 按钮能将 SELECT 语句块直接导出为 INSERT 语句,方便于数据移植、备份等; (4)支持列块操作; (5)加上/去掉注释标记、加上/去掉引号、增加/减少缩进、大小写转换等 o 运行语句 “desc [user.]objectName[@dbLink]” 或 Ctrl+鼠标点击字串“[user.]objectName[@dbLink]”,如果是一表名,则能清楚地显示表的列信息(包括列名称、数据类型及长度、默认值、非空)、索引、约束条件(主键、唯一键、检查键、子表、触发器、外键、甚至外键的外键) o 运行语句 “schema [user.]objectName[@dbLink]”,如果是一表名或视图名,则显示表或视图的脚本/语句,即表或视图是如何创建的(包括索引及约束条件) o 支持 PL/SQL 语句(包括 Procedure、 Function、Trigger、 Package),能够显示 DBMS_OUPTPUT.PUT_LINE 的输出;能够给出错误信息,甚至光标能够精确定位错误所在的行列位置,方便于调试 o 多线程、多连接:可以创建多个会话/连接,多个会话可以同时运行,在运行过程中可以掌控其运行状态、可以随时中断之,在运行过程中可以继续从事编辑等工作,不必等待运行完毕才能编辑 o 提供对常用数据字典(如 表、视图、索引、同义词、约束、过程、触发器等)的常用操作。例如,输入 “%NAME” 或 “NAME%” 或 “%NAME%”,能查找包含子串 “NAME” 的所有对象名,通过弹出菜单可以对每一个对象进行操作,如修改、删除、查看等
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值