Oracle 第8章 触发器、内置程序包

Oracle 第7章 子程序、程序包

Oracle 第8章 触发器、内置程序包

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;
/

 

注意：使用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;
/

 

注意：如果一个触发器由多种语句触发，可用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 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;
/

 

注意：使用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;
/

 

使用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 第7章 子程序、程序包