学习要求
有一定关系型数据的操作功底,会SQL语句
教学目标
熟练掌握Oracle数据库PL/SQL编程中触发器操作
概念
触发器是许多关系数据库系统都提供的一项技术。在 ORACLE 系统里,触发器类似过程和函数,都有 声明,执行和异常处理过程的 PL/SQL 块。
触发器与存储过程不同的是,存储过程可以直接执行也可以被其他程序(函数/存储过程)调用,而触发器是必须由一个事件触发并调用。另外触发器不能接收参数。ORACLE中的事件指的是对数据库的表进行的 INSERT、UPDATE 及 DELETE 操作或对视图进行类似的操作。甚至包括数据库的启动与关闭。
分类
DML 触发器
ORACLE 可以在 DML 语句进行触发,可以在 DML 操作前或操作后进行触发,并且可以对每个行或语句 操作上进行触发。
替代触发器
由于在 ORACLE 里,不能直接对由两个以上的表建立的视图进行操作。所以给出了替代触发器。
系统触发器
它可以在 ORACLE 数据库系统的事件中进行触发,如 ORACLE 系统的启动与关闭等。
触发器组成
触发事件:即在何种情况下触发 TRIGGER; 例如:INSERT, UPDATE, DELETE。
触发时间:即该 TRIGGER 是在触发事件发生之前(BEFORE)还是之后(AFTER)触发,也就是触发事件和 该 TRIGGER 的操作顺序。
触发器本身:即该 TRIGGER 被触发之后的目的和意图,正是触发器本身要做的事情。 例如:PL/SQL 块。
触发频率:说明触发器内定义的动作被执行的次数。即语句级(STATEMENT)触发器和行级(ROW)触发器。 语句级(STATEMENT)触发器:是指当某触发事件发生时,该触发器只执行一次; 行级(ROW)触发器:是指当某触发事件发生时,对受到该操作影响的每一行数据,触发器都单独执行一 次
DML 触发器
触发器名可以和表或过程有相同的名字,但在一个模式中触发器名不能相同。
1>CREATE TRIGGER 语句文本的字符长度不能超过 32KB;
2>触发器体内的 SELECT 语句只能为 SELECT … INTO …结构,或者为定义游标所使用的 SELECT 语句。
3>触发器中不能使用数据库事务控制语句 COMMIT; ROLLBACK, SVAEPOINT 语句;
4>由触发器所调用的过程或函数也不能使用数据库事务控制语句;
语法
CREATE [OR REPLACE] TRIGGER trigger_name
{BEFORE | AFTER }
{INSERT | DELETE | UPDATE [OF column [, column …]]}
ON [schema.] table_name
[FOR EACH ROW ]
[WHEN condition]
trigger_body;
BEFORE 和 AFTER 指出触发器的触发时序分别为前触发和后触发方式,前触发是在执行触发事件之前触 发当前所创建的触发器,后触发是在执行触发事件之后触发当前所创建的触发器。INSTEAD OF 触发器用在对视图的更新上。
FOR EACH ROW 选项说明触发器为行触发器。行触发器和语句触发器的区别表现在:行触发器要求当一 个 DML 语句操做影响数据库中的多行数据时,对于其中的每个数据行,只要它们符合触发约束条件,均激 活一次触发器;而语句触发器将整个语句操作作为触发事件,当它符合约束条件时,激活一次触发器。当 省略 FOR EACH ROW 选项时,BEFORE 和 AFTER 触发器为语句触发器,而 INSTEAD OF 触发器则为行触发 器。
WHEN 子句说明触发约束条件。Condition 为一个逻辑表达时,其中必须包含相关名称,而不能包含查 询语句,也不能调用 PL/SQL 函数。WHEN 子句指定的触发约束条件只能用在 BEFORE 和 AFTER 行触发器 中,不能用在 INSTEAD OF 行触发器和其它类型的触发器中。
当一个基表被修改( INSERT, UPDATE, DELETE)时要执行的存储过程,执行时根据其所依附的基表改动而自动触发,因此与应用程序无关,用数据库触发器可以保证数据的一致性和完整性。
触发器触发次序
首先:执行 BEFORE 语句级触发器;
其次:对与受语句影响的每一行:
1>执行 BEFORE 行级触发器
2>执行 DML 语句
3>执行 AFTER 行级触发器
最后:执行 AFTER 语句级触发器
参数获取
当触发器被触发时,要使用被插入、更新或删除的记录中的列值,有时要使用操作前、 后 列的值.
:NEW 修饰符访问操作完成后列的值
:OLD 修饰符访问操作完成前列的
表达式 | insert | update | delete |
---|---|---|---|
:NEW | 有值 | 有值 | 没值 |
:OLD | 没值 | 有值 | 有值 |
需求:使用触发器完成下列操作
1>创建一个操作日志表,记录部门操作信息
2>执行新增部门信息,使用触发器记录一笔操作日志
3>执行更新部门信息,使用触发器记录一笔操作日志
4>执行删除部门信息,删除前记录一笔提示信息, 删除后,使用触发器记录一笔操作日志
-- 创建操作日志表
CREATE TABLE "SCOTT"."DEPT_OP_RECORD"
( "ID" LONG,
"TYPE" VARCHAR2(10),
"INFO" VARCHAR2(100),
"TIME" TIMESTAMP (6)
)
-- 新增触发器
create or replace trigger add_op_tg
after insert on scott.dept for each row
begin
insert into dept_op_record(id, type, info, time)
values(:new.deptno, 'insert', '部门id为'||:new.deptno||'数据被添加了' , SYSDATE);
end;
-- 更新触发器
create or replace trigger edit_op_tg
after update on scott.dept for each row
begin
insert into dept_op_record(id, type, info, time)
values(:old.deptno, 'update', '部门id为'||:old.deptno||'数据被更新了' , SYSDATE);
end;
-- 删除触发器
create or replace trigger edit_op_tg_bf
before delete on scott.dept for each row
begin
insert into dept_op_record(id, type, info, time)
values(:old.deptno, 'tip', '部门id为'||:old.deptno||'数据就要被删除啦' , SYSDATE);
end;
create or replace trigger edit_op_tg_af
after delete on scott.dept for each row
begin
insert into dept_op_record(id, type, info, time)
values(:old.deptno, 'delete', '部门id为'||:old.deptno||'数据已经被删除' , SYSDATE);
end;
替代(INSTEAD OF)触发器
CREATE [OR REPLACE] TRIGGER trigger_name
INSTEAD OF
{INSERT | DELETE | UPDATE [OF column [, column …]]}
ON [schema.] view_name
[FOR EACH ROW ]
[WHEN condition]
trigger_body;
INSTEAD OF 选项使 ORACLE 激活触发器,而不执行触发事件。只能对视图和对象视图建立 INSTEAD OF 触发器,而不能对表、模式和数据库建立 INSTEAD OF 触发器。
INSTEAD_OF 用于对视图的 DML 触发,由于视图有可能是由多个表进行联结(join)而成,因而并非是所 有的联结都是可更新的。但可以按照所需的方式执行更新。
需求:删除某视图指定部门的信息,触发删除源表数据
-- 创建视图
create or replace view dept_view
as
select deptno, count(empno) count from emp group by deptno;
-- 直接使用视图删除
delete from dept_view where deptno = 10;
-- ORA-01732: 此视图的数据操纵操作非法
-- 创建视图触发器
create or replace trigger dept_view_tg
instead of delete
on scott.dept_view
for each row
begin
delete from dept where deptno = :old.deptno;
end;
系统事件触发器
ORACLE 提供的系统事件触发器可以在 DDL 或数据库系统上被触发。DDL 指的是数据定义语言,如 CREATE 、ALTER 及 DROP 等。而数据库系统事件包括数据库服务器的启动或关闭,用户的登录与退出、数 据库服务错误等。
CREATE OR REPLACE TRIGGER [sachema.] trigger_name
{BEFORE|AFTER}
{ddl_event_list | database_event_list}
ON { DATABASE | [schema.] SCHEMA }
[WHEN_clause]
trigger_body
ddl_event_list:一个或多个 DDL 事件,事件间用 OR 分开;
database_event_list:一个或多个数据库事件,事件间用 OR 分开;
系统事件触发器既可以建立在一个模式上,又可以建立在整个数据库上。当建立在模式(SCHEMA)之上 时,只有模式所指定用户的 DDL 操作和它们所导致的错误才激活触发器, 默认时为当前用户模式。当建立在 数据库(DATABASE)之上时,该数据库所有用户的 DDL 操作和他们所导致的错误,以及数据库的启动和关闭均 可激活触发器。要在数据库之上建立触发器时,要求用户具有 ADMINISTER DATABASE TRIGGER 权限。
触发器事件
事件名 | 类别 | 说明 |
---|---|---|
启动STARTUP | database | oracle实例启动时触发 |
关闭SHUTDOWN | database | oracle实例关闭时触发 |
服务器错误SERVERERROR | database | 只要有错误都触发 |
登录LOGON | database | 登录成功后触发 |
注销LOGOFF | database | 开始注销时触发 |
创建CREATE | DDL | 创建数据库对象时触发 |
撤销DROP | DDL | 删除数据库对象时触发 |
变更ALTER | DDL | 更新数据库对象时触发 |
触发器中可使用的属性/函数
属性/函数 | 介绍 |
---|---|
ora_client_ip_address | 用于返回客户端的IP地址 |
ora_database_name | 用于返回当前数据库名 |
ora_des_encrypted_password | 用于返回DES加密后的用户口令 |
ora_dict_obj_name | 用于返回DDL操作所对应的数据库对象名 |
ora_dict_obj_name_list(name_list_ OUT ora_name_list_t) | 用于返回字事件中被修改的对象名列表 |
ora_dict_obj_owner | 用于返回DDL操作所对应的对象的所有者名。 |
ora_dict_obj_ower_list(ower_list OUT ora_name_list_t) | 用于返回在事件中被修改对象的所有者列表 |
ora_dict_obj_type | 用于返回DDL操作所对应的数据库对象的类型。 |
ora_grantee(user_list OUT ora_name_list_t) | 用于返回授权时事件授权者。 |
ora_instance_num | 用于返回历程号。 |
ora_is_alter_column(column_name IN VARCHAR2) | 用于检测特定列是否被修改 |
ora_is_creating_nested_table | 用于检测是否正在建立嵌套表 |
ora_is_drop_column(column_name IN VARCHAR2) | 用于检测特定列是否被删除 |
ora_is_servererror(error_number) | 用于检测是否返回了特定Oracle错误。 |
ora_login_user | 用于返回登录用户名 |
ora_sysevent | 用于返回触发 触发器的系统事件名。 |
建立database触发器
需求:按要求完成下面操作
1>定义一张表,记录用户登录与退出信息
2>定义一个登录的触发器记录登录信息
3>定义一个注销触发器记录注销信息
-- 先切换用户
conn sys/admin as sysdba;
create table login_info(event varchar2(30),uname varchar2(10), time date);
-- 登录
create or replace trigger login_tr
after LOGON ON DATABASE
begin
insert into login_info(event, uname, time) values(ora_sysevent, ora_login_user, SYSDATE);
end;
-- 注销
create or replace trigger logout_tr
before LOGOFF ON DATABASE
begin
insert into login_info(event, uname, time) values(ora_sysevent, ora_login_user, SYSDATE);
end;
测试
先exit 然后再conn 再查询login_info表
DDL触发器
需求: 按要求完成下面操作
1>定义DDL触发器, 当给dept添加新字段时,打印提示信息
create or replace trigger ddl_tr
after DDL on scott.schema
begin
dbms_output.put_line('执行了ddl操作');
end;
PL/SQL 工具可能无法显示, 可以使用plus自带命令框测试
CREATE TABLE temp(
ID INTEGER PRIMARY KEY,
NAME VARCHAR2(20)
);
DROP TABLE temp;
SELECT * FROM eventddl;
PL/SQL 创建
随手
其他操作
删除触发器
DROP TRIGGER trigger_name;
禁止触发器
ALTER TRIGGER trigger_name DISABLE;
激活触发器
ALTER TRIGGER trigger_name ENABLE;