Oracle19C入门到熟练023-触发器

学习要求

有一定关系型数据的操作功底，会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;