- 浏览: 529729 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (740)
- css (4)
- jquery (8)
- javascript (23)
- html (0)
- uml (0)
- 设计模式 (1)
- 开发工具 (14)
- json (4)
- struts 1.x (3)
- spring (3)
- hibernate (6)
- struts 2.x (17)
- JFreechart (0)
- j2se (48)
- jsp (9)
- flex (22)
- 找工作 (1)
- 技术杂谈 (18)
- 网络编程 (5)
- io流 (1)
- ORACLE (15)
- 报表 (3)
- extjs (11)
- jpbm (2)
- swing (5)
- jspereports (3)
- sql (1)
- linux (15)
- ps (1)
- storm (4)
- hbase (8)
- li (0)
- python (1)
- hive (3)
- 机器学习 (1)
- hdfs (1)
- elasticsearch (1)
- hadoop 2.2 (5)
- hadoop (1)
最新评论
-
Tristan_S:
这个有点意思
ASM -
starryskydog:
程序修改detail band部分的样式 如内容字体大小 ...
使用jasperReport实现动态表头 -
samwong:
Good, so usefule
使用YUI Compressor压缩CSS/JS -
gc715409742:
能够告诉我怎么在web项目中使用YUI Compressor? ...
使用YUI Compressor压缩CSS/JS -
JsonTeye:
您好! 我看你的代码,我现在也在做动态报表,实现功能由用户自己 ...
使用jasperreport动态生成pdf,excel,html
触发器、内置程序包
1、技术目标
- 应用触发器
- 使用内置程序包
2、什么是触发器
- 触发器是当特定事件出现时自动执行的存储过程
- 特定事件可以是执行更新的DML语句和DDL语句
- 触发器不能被显式调用,存储过程可以显示调用
触发器的功能 有:
- 自动生成数据
- 自定义复杂的安全权限
- 提供审计和日志记录
- 启用复杂的业务逻辑
触发器可以与特定的表或视图相关联,用于检查对表/视图所做的数据修改,
当执行insert、delete、update语句时,可激活触发器代码
3、如何创建触发器
创建触发器的语法 为:
CREATE [OR REPLACE] TRIGGER trigger_name
{AFTER | BEFORE | INSTEAD OF}
{insert | delete | update [OF column[, column] ...]}
[OR {insert | delete | update [OF column[, column] ...] }]
ON
[schema.]table_or_view_name
[REFERENCING [NEW AS new_row_name] [OLD AS old_row_name]]
[FOR EACH ROW]
[WHEN (condition)]
[DECLARE
variable_declation]
BEGIN
statements;
[EXCEPTION
exception_handlers]
END [trigger_name];
语法说明:
AFTER | BEFORE,指在事件发生之前或之后激活触发器
INSTEAD OF,表示可以执行触发器代码来代替导致触发器调用的事件
insert | delete | update,指定构成触发器事件的数据操纵类型,
update可指定列列表
REFERENCING,指定新行(即将更新)和旧行(更新前)的其他名称,
默认为NEW和OLD
table_or_view_name,指要创建触发器的表或视图的名称
FOR EACH ROW,指定是否对受影响的每行都执行触发器,即行级触发器,
如不使用此句,则为语句级触发器
WHEN,限制执行触发器的条件,该条件可包括新旧数据值的检查
DECLARE...END,一个标准的PL/SQL块
使用: 在Emp表创建触发器,
- CREATE OR REPLACE TRIGGER biu_emp_deptno
- --在添加或修改deptNo字段之前触发
- BEFORE INSERT OR UPDATE OF deptNo
- ON Emp
- --行级触发器
- FOR EACH ROW
- --列deptNo的新值不等于40
- WHEN (New.deptNo <> 40)
- BEGIN
- --将comm列设置为0
- :New.comm := 0;
- END;
- /
CREATE OR REPLACE TRIGGER biu_emp_deptno --在添加或修改deptNo字段之前触发 BEFORE INSERT OR UPDATE OF deptNo ON Emp --行级触发器 FOR EACH ROW --列deptNo的新值不等于40 WHEN (New.deptNo <> 40) BEGIN --将comm列设置为0 :New.comm := 0; END; /
注意:使用SHOW ERRORS命令可查看创建触发器时出现的错误
4、触发器的组成部分
触发器由以下3个部分 组成:
触发语句 ,定义激活触发器的 DML 事件和 DDL 事件,如:
BEFORE INSERT OR UPDATE OF deptNo
ON Emp
--行级触发器
FOR EACH ROW
这段代码表示,当对Emp表执行insert语句或对Emp表的
deptNo列执行update语句时,触发器会在受影响的每一行
上执行一次
触发限制 ,执行触发器的条件,该条件必须为真才能激活触发器,如:
--列deptNo的新值不等于40,触发器会执行
WHEN (New.deptNo <> 40)
触发操作 ,一些 SQL 语句和代码,在发出了触发器语句且触发限制的
值为真时运行,如:
BEGIN
--将comm列设置为0
:New.comm := 0;
END;
5、触发器的类型及使用
触发器有如下的类型:
每种触发器的作用:
使用1: 应用行级触发器,
- --创建表TEST_TRG
- CREATE TABLE TEST_TRG (ID NUMBER, NAME VARCHAR2(20));
- --创建序列SEQ_TEST
- CREATE SEQUENCE SEQ_TEST;
- --为TEST_TRG表创建行级触发器
- CREATE OR REPLACE TRIGGER BI_TEST_TRG
- --在insert(添加)或者update(修改)ID字段时触发
- BEFORE INSERT OR UPDATE OF ID
- ON TEST_TRG --指定TEST_TRG表
- FOR EACH ROW --设置为行级触发器
- --触发器语句部分
- BEGIN
- --判断是不是insert语句
- IF INSERTING THEN
- --如果是insert操作,将序列的值设置给ID列
- SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL;
- ELSE
- --如果不是insert操作,不能修改ID列的值
- RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值!');
- END IF;
- END;
- /
--创建表TEST_TRG CREATE TABLE TEST_TRG (ID NUMBER, NAME VARCHAR2(20)); --创建序列SEQ_TEST CREATE SEQUENCE SEQ_TEST; --为TEST_TRG表创建行级触发器 CREATE OR REPLACE TRIGGER BI_TEST_TRG --在insert(添加)或者update(修改)ID字段时触发 BEFORE INSERT OR UPDATE OF ID ON TEST_TRG --指定TEST_TRG表 FOR EACH ROW --设置为行级触发器 --触发器语句部分 BEGIN --判断是不是insert语句 IF INSERTING THEN --如果是insert操作,将序列的值设置给ID列 SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL; ELSE --如果不是insert操作,不能修改ID列的值 RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值!'); END IF; END; /
注意:如果一个触发器由多种语句触发,可用INSERTING、UPDATING、
DELETING这些关键字进行检查,对应语句类型
使用2: 应用语句级触发器,
- CREATE OR REPLACE TRIGGER trgdemo
- AFTER INSERT OR UPDATE OR DELETE
- ON order_master
- BEGIN
- --根据语句类型输出信息
- IF UPDATING THEN
- DBMS_OUTPUT.PUT_LINE('已更新ORDER_MASTER中的数据');
- ELSIF DELETING THEN
- DBMS_OUTPUT.PUT_LINE('已删除ORDER_MASTER中的数据');
- ELSIF INSERTING THEN
- DBMS_OUTPUT.PUT_LINE('已在ORDER_MASTER中插入数据');
- END IF;
- END;
- /
CREATE OR REPLACE TRIGGER trgdemo AFTER INSERT OR UPDATE OR DELETE ON order_master BEGIN --根据语句类型输出信息 IF UPDATING THEN DBMS_OUTPUT.PUT_LINE('已更新ORDER_MASTER中的数据'); ELSIF DELETING THEN DBMS_OUTPUT.PUT_LINE('已删除ORDER_MASTER中的数据'); ELSIF INSERTING THEN DBMS_OUTPUT.PUT_LINE('已在ORDER_MASTER中插入数据'); END IF; END; /
注意:语句级触发器时CREATE TRIGGER命令所创建触发器的默认类型
使用3: 应用INSTEAD OF触发器,同时向两个表中插入值,
- --创建视图
- CREATE VIEW ord_view AS
- SELECT order_master.orderno, order_master.ostatus,
- order_detail.qty_deld, order_detail.qty_ord
- FROM order_master, order_detail
- WHERE order_master.orderno = order_detail.orderno;
- --创建INSTEAD OF触发器
- CREATE OR REPLACE TRIGGER order_mast_insert
- INSTEAD OF UPDATE ON ord_view
- --为NEW关键字取别名n
- REFERENCING NEW AS n
- FOR EACH ROW
- DECLARE
- --定义游标,访问order_master表
- CURSOR ecur IS SELECT * FROM order_master
- WHERE order_master.orderno = :n.orderno;
- --定义游标,访问order_detail表
- CUSEOR dcur IS
- select * from order_detail
- WHERE order_detail.orderno = :n.orderno;
- --定义游标变量
- a ecur%ROWTYPE;
- b dcur%ROWTYPE;
- BEGIN
- --打开游标
- OPEN ecur;
- OPEN dcur;
- --读取行
- FETCH ecur into a;
- FETCH dcur into b;
- --判断是否有行
- IF dur%NOTFOUND THEN --没有
- --添加记录
- INSERT INTO order_master (orderno, ostatus)
- VALUES (:n.orderno, :n.ostatus);
- ELSE --有
- --修改记录
- UPDATE order_master SET order_master.ostatus = :n.ostatus
- WHERE order_master.orderno = :n.orderno;
- END IF;
- IF ecur%NOTFOUND THEN
- INSERT INTO order_detail (qty_ord, qty_deld, orderno)
- VALUES(:n.qty_ord, :n.qty_deld, :n.orderno);
- ELSE
- UPDATE order_detail SET
- order_detail.qty_ord = :n.qty_ord,
- order_detail.qty_deld = :n.qty_deld
- WHERE order_detail.orderno = :n.orderno;
- END IF;
- --关闭游标
- CLOSE ecur;
- CLOSE dcur;
- END;
- /
--创建视图 CREATE VIEW ord_view AS SELECT order_master.orderno, order_master.ostatus, order_detail.qty_deld, order_detail.qty_ord FROM order_master, order_detail WHERE order_master.orderno = order_detail.orderno; --创建INSTEAD OF触发器 CREATE OR REPLACE TRIGGER order_mast_insert INSTEAD OF UPDATE ON ord_view --为NEW关键字取别名n REFERENCING NEW AS n FOR EACH ROW DECLARE --定义游标,访问order_master表 CURSOR ecur IS SELECT * FROM order_master WHERE order_master.orderno = :n.orderno; --定义游标,访问order_detail表 CUSEOR dcur IS select * from order_detail WHERE order_detail.orderno = :n.orderno; --定义游标变量 a ecur%ROWTYPE; b dcur%ROWTYPE; BEGIN --打开游标 OPEN ecur; OPEN dcur; --读取行 FETCH ecur into a; FETCH dcur into b; --判断是否有行 IF dur%NOTFOUND THEN --没有 --添加记录 INSERT INTO order_master (orderno, ostatus) VALUES (:n.orderno, :n.ostatus); ELSE --有 --修改记录 UPDATE order_master SET order_master.ostatus = :n.ostatus WHERE order_master.orderno = :n.orderno; END IF; IF ecur%NOTFOUND THEN INSERT INTO order_detail (qty_ord, qty_deld, orderno) VALUES(:n.qty_ord, :n.qty_deld, :n.orderno); ELSE UPDATE order_detail SET order_detail.qty_ord = :n.qty_ord, order_detail.qty_deld = :n.qty_deld WHERE order_detail.orderno = :n.orderno; END IF; --关闭游标 CLOSE ecur; CLOSE dcur; END; /
注意:使用INSTEAD OF触发器有如下的限制,
- 只能在行级使用,不能在语句级使用
- 只能应用于视图,不能应用于表
使用4: 应用模式(DDL)触发器,对用户删除的对象进行日志记录,
创建模式触发器的语法 为:
CREATE OR REPLACE TRIGGER trigger_name
{BEFORE | AFTER} trigger_event
ON [schema.]SCHEMA
WHEN (trigger_condition)
trigger_body;
- --创建日志记录表
- CREATE TABLE dropped_obj
- (
- obj_name VARCHAR2(30),
- obj_type VARCHAR2(20),
- drop_date DATE
- );
- --创建触发器
- CREATE OR REPLACE TRIGGER log_drop_obj
- --在执行drop语句后触发
- AFTER DROP ON SCHEMA
- BEGIN
- --将被删除对象的信息添加到日志记录表中
- INSERT INTO dropped_obj
- VALUES (ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);
- END;
- /
--创建日志记录表 CREATE TABLE dropped_obj ( obj_name VARCHAR2(30), obj_type VARCHAR2(20), drop_date DATE ); --创建触发器 CREATE OR REPLACE TRIGGER log_drop_obj --在执行drop语句后触发 AFTER DROP ON SCHEMA BEGIN --将被删除对象的信息添加到日志记录表中 INSERT INTO dropped_obj VALUES (ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE); END; /
使用5: 应用数据库级触发器,在数据库启动后执行,
CREATE OR REPLACE TRIGGER system_startup
--系统启动时触发
AFTER STARTUP ON DATEBASE
BEGIN
--加入所需代码
END;
/
6、启动、禁用、删除触发器,查看触发器信息
启用和禁用触发器:
ALTER TRIGGER 触发器名 DISABLE; --禁用
ALTER TRIGGER 触发器名 ENABLE; --启用
删除触发器:
DROP TRIGGER 触发器名;
查看触发器信息,使用USER_TRIGGERS数据字典:
使用1: 查看为表EMP设置的触发器名
select TRIGGER_NAME from USER_TRIGGERS
WHERE TABLE_NAME = 'EMP';
使用2: 查看触发器BIU_EMP_DEPTNO的类型、触发事件、触发条件,
select TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSE
from USER_TRIGGERS
WHERE TRIGGER_NAME = 'BIU_EMP_DEPTNO';
7、内置程序包
Oracle提供了许多内置程序包,用于扩展数据库功能,数据库用户SYS
拥有所有程序包,程序包被定义为公有同义词,并将执行权限授予了
PUBLIC用户组,任何用户都可访问,部分内置程序包如下:
8、总结
- 触发器是当特定事件出现时自动执行的存储过程
- 触发器分为 DML 触发器、DDL 触发器和数据库级触发器三种类型
- DML触发器的三种类型为行级触发器、语句级触发器和INSTEAD OF触发器
- 了解一些常用的内置程序包
发表评论
-
高级SQL优化
2012-12-08 23:31 808高级SQL优化(一) ... -
oracle PL/SQL的基本构成,块结构和基本语法要求,数据类型,变量定义,运算符和函数
2011-10-08 01:45 1038oracle PL/SQL的基本构成,块结构和基本语法要求, ... -
oracle PL/SQL的基本构成,结构控制语句,分支结构,选择结构,loop循环结构,
2011-10-08 01:43 897oracle PL/SQL的基本构成,结构控制语句,分支结构, ... -
oracle 创建,删除存储过程,参数传递,创建,删除存储函数,存储过程和函数的查看,包,系统包
2011-10-08 01:42 1027oracle 创建,删除存储过 ... -
oracle 触发器的种类和触发事件,DML触发器,DDL事件触发器,替代触发器,查看触发器
2011-10-08 01:39 961oracle 触发器的种类和触发事件,DML触发器,DDL事 ... -
oracle 隐式游标,显示游标,游标循环,动态SELECT语句和动态游标,异常处理,自定义异常
2011-10-08 01:31 815oracle 隐式游标,显示游标,游标循环,动态SELE ... -
子程序、程序包
2011-10-07 10:18 1153子程序、程序包 1、技术目标 创建并使用子程序 ... -
游标的使用
2011-10-07 10:16 1368游标 1、技术目标 游标的使用 2、什么是游标 ... -
PL/SQL 的使用
2011-10-07 10:15 2092使用PL/SQL 1、技术目标 理解 PL/SQL ... -
同义词、序列、视图、索引
2011-10-07 10:12 1717同义词、序列、视图、 ... -
什么是Oracle归档模式
2011-10-06 23:31 839查看oracle数据库是否为归档模式 [1] 1.se ... -
什么是冷备份和热备份,有什么区别?
2011-10-06 23:19 930对于oracle数据库只有物 ... -
Oracle数据导入导出
2011-10-06 23:17 654Oracle数据导入导出imp/exp就相当于oracle ... -
ORACLE闪回技术
2011-10-06 23:17 7501 .误删了一部分数据 (delete) 想回恢复数据,ora ...
相关推荐
触发器和内置程序包
ORACLE触发器、内置程序包教学.doc )
ORACLE触发器、内置程序包教学.pdf
数据库触发器与内置程序包综述.pptx
触发器和内置程序包PPT学习教案.pptx
数据库触发器与内置程序包综述(ppt-23页).ppt
2022年数据库触发器与内置程序包综述(共22张PPT).pptx
老二牛车第八章理论课触发器和内置程序包.pdf
老二牛车教育程矢 第八章上机课触发器和内置程序包.pdf
Oracle学习第八章 触发器和内置程序包 幻灯片学习
Oracle的学习 1.Oracle 入门 2.SQL 查询和 SQL 函数 3.锁和表分区 4.数据库对象 5.使用 PL/SQL 6.游标管理 7.子程序和程序包 8.触发器和内置程序包 9.备份与恢复简介
子程序[proc,udf],自主事务处理和程序包 ①:存储过程 ②:函数 ③:自主事务处理 ④:包规范|包主体 十一.触发器|内置包 ①:触发器类型 ②:触发器原理 ③:new和old 两个行类型变量 ④:递归 ⑤:内置包 十二....
培训资料,包括PL/SQL概述、数据类型、控制类型、异常处理、游标、子程序及包、触发器、内置包、集合与数组
Oracle笔记,每行代码都经过验证,如果能把笔记里的例子都敲一遍,保证你学会Oracle,涉及系统结构、安全、查询、锁、表分区、数据库对象、PL/SQL语法、游标、子程序、程序包、触发器、内置程序包、备份与恢复、导入...
PPT目录: 第一章 Oracle入门; 第二章 查询和SQL函数; 第三章 锁和表分区; 第四章 数据库对象; 第五章 Oracle 中的 OOP 概念;...第六章 PL/SQL 简介;...第九章 数据库触发器和内置程序包; 第十章 集合和成员函数。
oracle的一些简单应用! 第一章 走进Oracle 1 第二章 SQL数据操作和查询 第三章 常用函数、事务和锁 ...第八章 游标、内置程序包 第九章 触发器、数据字典 第十章 数据库管理 附录 数据库导入导出
使用数据库创建应用程序时,过程语言是应用程序和数据库之间的主要编程接口。神通数据库中所提 供的过程语言是 plOSCAR。在使用 plOSCAR 程序时,可用两种方法存储和执行程序。可以在本地存储程 序,并创建向数据库...
这些新的特性包括如何使用内置包,优化策略,以及PL/SQL程序的调试技术等。本书从以下几个方面深入介绍了Oracle8i程序设计的最新特点: 1) Oracle8i和PL/SQL提供的高效开发和运行环境。 2) 使用包DBMS_DEBUG和 ...
这些新的特性包括如何使用内置包,优化策略,以及PL/SQL程序的调试技术等。 本书从以下几个方面深入介绍了Oracle8i程序设计的最新特点: 1) Oracle8i和PL/SQL提供的高效开发和运行环境。 2) 使用包DBMS_DEBUG和 ...