`

触发器、内置程序包

 
阅读更多

触发器、内置程序包


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表创建触发器,

Sql代码 复制代码 收藏代码
  1. CREATE OR REPLACE TRIGGER biu_emp_deptno   
  2. --在添加或修改deptNo字段之前触发   
  3. BEFORE INSERT OR UPDATE OF deptNo   
  4. ON Emp   
  5. --行级触发器   
  6. FOR EACH ROW   
  7. --列deptNo的新值不等于40   
  8. WHEN (New.deptNo <> 40)   
  9. BEGIN  
  10.     --将comm列设置为0   
  11.     :New.comm := 0;   
  12. END;   
  13. /  
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: 应用行级触发器,

Sql代码 复制代码 收藏代码
  1. --创建表TEST_TRG   
  2. CREATE TABLE TEST_TRG (ID NUMBER, NAME VARCHAR2(20));   
  3. --创建序列SEQ_TEST   
  4. CREATE SEQUENCE SEQ_TEST;   
  5. --为TEST_TRG表创建行级触发器   
  6. CREATE OR REPLACE TRIGGER BI_TEST_TRG   
  7. --在insert(添加)或者update(修改)ID字段时触发   
  8. BEFORE INSERT OR UPDATE OF ID   
  9. ON TEST_TRG --指定TEST_TRG表   
  10. FOR EACH ROW --设置为行级触发器   
  11. --触发器语句部分   
  12. BEGIN  
  13.      --判断是不是insert语句   
  14.      IF INSERTING THEN  
  15.        --如果是insert操作,将序列的值设置给ID列   
  16.        SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL;   
  17.      ELSE  
  18.        --如果不是insert操作,不能修改ID列的值   
  19.        RAISE_APPLICATION_ERROR(-20020, '不允许更新ID值!');   
  20.      END IF;   
  21. END;   
  22. /  
--创建表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: 应用语句级触发器,

Sql代码 复制代码 收藏代码
  1. CREATE OR REPLACE TRIGGER trgdemo   
  2.    AFTER INSERT OR UPDATE OR DELETE  
  3.    ON order_master   
  4. BEGIN  
  5.    --根据语句类型输出信息   
  6.    IF UPDATING THEN  
  7.      DBMS_OUTPUT.PUT_LINE('已更新ORDER_MASTER中的数据');   
  8.    ELSIF DELETING THEN  
  9.      DBMS_OUTPUT.PUT_LINE('已删除ORDER_MASTER中的数据');   
  10.    ELSIF INSERTING THEN  
  11.      DBMS_OUTPUT.PUT_LINE('已在ORDER_MASTER中插入数据');   
  12.    END IF;   
  13. END;   
  14. /  
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触发器,同时向两个表中插入值,

Sql代码 复制代码 收藏代码
  1. --创建视图   
  2. CREATE VIEW ord_view AS  
  3. SELECT order_master.orderno, order_master.ostatus,   
  4.     order_detail.qty_deld, order_detail.qty_ord   
  5. FROM order_master, order_detail   
  6. WHERE order_master.orderno = order_detail.orderno;   
  7.   
  8. --创建INSTEAD OF触发器   
  9. CREATE OR REPLACE TRIGGER order_mast_insert   
  10.   INSTEAD OF UPDATE ON ord_view   
  11.   --为NEW关键字取别名n   
  12.   REFERENCING NEW AS n   
  13.   FOR EACH ROW   
  14.   DECLARE  
  15.     --定义游标,访问order_master表   
  16.     CURSOR ecur IS SELECT * FROM order_master   
  17.     WHERE order_master.orderno = :n.orderno;   
  18.     --定义游标,访问order_detail表   
  19.     CUSEOR dcur IS  
  20.     select * from order_detail   
  21.     WHERE order_detail.orderno = :n.orderno;   
  22.     --定义游标变量   
  23.     a ecur%ROWTYPE;   
  24.     b dcur%ROWTYPE;   
  25.   BEGIN  
  26.     --打开游标   
  27.     OPEN ecur;   
  28.     OPEN dcur;   
  29.     --读取行   
  30.     FETCH ecur into a;   
  31.     FETCH dcur into b;   
  32.     --判断是否有行   
  33.     IF dur%NOTFOUND THEN --没有   
  34.         --添加记录   
  35.         INSERT INTO order_master (orderno, ostatus)   
  36.         VALUES (:n.orderno, :n.ostatus);   
  37.     ELSE --有   
  38.         --修改记录   
  39.         UPDATE order_master SET order_master.ostatus = :n.ostatus   
  40.         WHERE order_master.orderno = :n.orderno;   
  41.     END IF;   
  42.   
  43.     IF ecur%NOTFOUND THEN  
  44.         INSERT INTO order_detail (qty_ord, qty_deld, orderno)   
  45.         VALUES(:n.qty_ord, :n.qty_deld, :n.orderno);   
  46.     ELSE  
  47.         UPDATE order_detail SET  
  48.         order_detail.qty_ord = :n.qty_ord,   
  49.         order_detail.qty_deld = :n.qty_deld   
  50.         WHERE order_detail.orderno = :n.orderno;   
  51.     END IF;   
  52.     --关闭游标   
  53.     CLOSE ecur;   
  54.     CLOSE dcur;   
  55.   END;   
  56. /  
--创建视图
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;

Sql代码 复制代码 收藏代码
  1. --创建日志记录表   
  2. CREATE TABLE dropped_obj   
  3. (   
  4.     obj_name VARCHAR2(30),   
  5.     obj_type VARCHAR2(20),   
  6.     drop_date DATE  
  7. );   
  8. --创建触发器   
  9. CREATE OR REPLACE TRIGGER log_drop_obj   
  10. --在执行drop语句后触发   
  11. AFTER DROP ON SCHEMA  
  12. BEGIN  
  13.     --将被删除对象的信息添加到日志记录表中   
  14.     INSERT INTO dropped_obj   
  15.     VALUES (ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);   
  16. END;   
  17. /  
--创建日志记录表
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触发器
  • 了解一些常用的内置程序包
分享到:
评论

相关推荐

    触发器和内置程序包

    触发器和内置程序包

    ORACLE触发器、内置程序包教学.doc )

    根据给定的文件信息,我们将深入探讨Oracle数据库中的触发器和内置程序包,重点解析触发器的概念、创建方法以及其在数据库管理中的作用。 ### 触发器:自动化执行的存储过程 触发器是一种特殊类型的存储过程,它在...

    2022年数据库触发器与内置程序包综述(共22张PPT).pptx

    数据库触发器和内置程序包是数据库管理系统中两个重要的概念,它们在2022年的数据库管理中仍然扮演着关键角色。触发器是一种特殊的数据库对象,它在特定的数据操作语言(DML)事件(如INSERT、UPDATE或DELETE)发生...

    触发器和内置程序包PPT学习教案.pptx

    【触发器和内置程序包】是数据库管理系统中的关键概念,主要应用于实现高级的数据管理和业务逻辑。触发器是一种特殊的存储过程,当特定的数据库操作(如INSERT、UPDATE或DELETE)发生时,它们会自动执行,提供了对...

    数据库触发器与内置程序包综述(ppt-23页).ppt

    这些内置程序包中的函数和过程,如DBMS_OUTPUT的Enable、Put_line等,以及DBMS_LOB的append、copy等,都是数据库管理员和开发人员在日常工作中常用的工具,可以帮助他们更高效地管理和维护数据库系统。用户必须拥有...

    数据库触发器与内置程序包综述.pptx

    【数据库触发器】 数据库触发器是Oracle数据库中一种强大的特性,它允许在特定的...这些内置程序包极大地丰富了Oracle数据库的功能,使得开发人员能够更高效地管理和操作数据库,同时提供了更高级别的控制和安全性。

    老二牛车第八章理论课触发器和内置程序包.pdf

    根据提供的文件信息,我们可以深入探讨Oracle数据库中的两个关键概念——触发器和内置程序包。 ### 触发器 #### 定义与作用 触发器是一种特殊类型的存储过程,它会在某些特定事件(如数据修改)发生时自动执行。与...

    ORACLE触发器、内置程序包教学.pdf

    Oracle内置程序包是一组已经预定义好的PL/SQL包和类型,这些包提供了大量预构建的功能,让开发者可以快速实现复杂的应用功能。 触发器的创建语法包含以下主要部分: - 触发器类型:BEFORE、AFTER 或 INSTEAD OF。...

    老二牛车第八章上机课触发器和内置程序包.pdf

    根据提供的文件信息,我们可以深入探讨Oracle数据库中的两个关键概念——触发器与内置程序包,并具体分析文件中提及的两种触发器实例。 ### 触发器概述 触发器是一种存储过程,它定义为当特定事件(如数据修改)...

    Oracle学习第八章

    在Oracle学习的第八章中,我们将聚焦于两个核心概念:触发器(Triggers)和内置程序包(Built-in Packages)。这两个概念是Oracle数据库开发中的重要组成部分,它们扩展了SQL的功能,为数据库应用提供了更高级别的...

    受限过程 内置子程序,触发器,变量 说明

    ### 受限过程 内置子程序,触发器,变量 说明 #### 一、概述 受限过程在数据库系统中通常是指一类具有特定权限限制的过程,这类过程可以在数据库环境中执行预定义的操作,如数据处理、逻辑判断等。内置子程序、...

    PL/SQL的过程、函数、程序包和数据库触发器(十一).pdf

    它允许开发者以模块化的方式封装逻辑,这包括创建过程、函数、程序包以及数据库触发器等。接下来,我们将详细探讨这些概念和相关知识点。 首先,匿名PL/SQL块是未命名的代码块,它们在应用程序运行时被发送到数据库...

    Oracle培训教程9.pptx

    在Oracle中,数据库触发器和内置程序包是两个重要的概念,它们极大地扩展了数据库的功能和灵活性。 **数据库触发器** 是PL/SQL代码块,它在对特定表执行数据操纵语言(DML)操作时自动执行。触发器分为两类:应用...

    Oracle触发器里调用Java程序

    首先,我们需要了解Oracle的PL/SQL(Procedural Language/Structured Query Language)语言,它是Oracle数据库内置的一种过程编程语言,可以创建触发器、存储过程等。在PL/SQL中调用Java代码,我们需要启用Oracle的...

    Oracle触发器实验报告

    Oracle触发器是数据库管理系统中的一种特殊程序,它与数据库表的操作紧密相关,可以在特定的数据操作事件(如INSERT、UPDATE或DELETE)发生时自动执行。在本实验报告中,主要涉及了Oracle触发器与参照完整性约束的...

    WPF MVVM Command 02 触发器

    在提供的压缩包文件`WpfApplication2`中,很可能是包含了一个简单的WPF应用程序,演示了如何使用MVVM模式和命令触发器。你可能发现一个ViewModel类,它定义了一个或多个Command,以及一个View,其中的按钮或其他控件...

    jsp 应用触发器自动插入回复记录

    在"jsp 应用触发器自动插入回复记录"这个场景中,我们可以推断这是一个JSP应用程序,它利用了数据库的触发器功能来自动化回复记录的插入过程。以下是对这个主题的详细解释: 1. **JSP基础**:JSP的核心是Servlet...

    存储过程和程序包

    综上所述,存储过程、程序包以及触发器是数据库开发中的重要组成部分,它们不仅有助于提高应用程序的效率和安全性,还能简化复杂的业务逻辑处理。掌握这些技术对于任何希望深入学习数据库管理和开发的人员来说都是至...

    Orcal 数据库触发器文件

    - 触发器应该作为最后的手段,当其他数据库约束、存储过程或应用程序逻辑无法满足需求时使用。 - 为触发器编写详尽的文档,以便其他开发人员理解其功能和可能的影响。 6. 替代方案: - 使用应用程序层的逻辑,将...

    使用java代码自动生成触发器

    触发器是数据库系统内置的一种机制,当满足特定条件(如某个表的数据发生变化)时,会自动执行一段代码。它们通常用于实施复杂的业务规则、数据验证、日志记录等任务,这些任务可能不适合通过简单的SQL查询或应用...

Global site tag (gtag.js) - Google Analytics