『PLSQL』PLSQL高级编程：触发器

 1. 触发器的创建

    CREATE TRIGGER [schema.]trigger_name
   {BEFORE|AFTER} {UPDATE|INSERT|DELETE} ON [schema.]table_name
    [ [REFERENCING correlation_names] FOR EACH ROW [WHEN (condition)] ]

2 触发器的管理

1) 查看触发器：
    SQL> desc ALL_TRIGGERS;

名称 空? 类型
-------------------- -------- ----------
PL/SQL 高级编程
第 8 页 共 8 页
owner VARCHAR2(30)
trigger_name VARCHAR2(30)
trigger_type VARCHAR2(16)
triggering_event VARCHAR2(216)
table_owner VARCHAR2(30)
base_object_type VARCHAR2(16)
table_name VARCHAR2(30)
column_name VARCHAR2(4000)
referencing_names VARCHAR2(128)
when_clause VARCHAR2(4000)
status VARCHAR2(8)
description VARCHAR2(4000)
action_type VARCHAR2(11)
trigger_body LONG

2) 查看触发器的代码：
    触发器的源代码被存贮在 ALL_TRIGGERS 的 trigger_body 字段中。抽取触发器定义的命令：

SET ECHO OFF
SET MAXDATA 20000
SET LONG 20000
SET LONGCHUNKSIZE 1000
SET PAGESIZE 0
SET HEADING OFF
SET TRIMSPOOL ON
SET TRIMOUT ON
SET RECSEP OFF
ACCEPT trigger_name CHAR PROMPT 'please input the trigger to lookup:'
ACCEPT trigger_owner CHAR PROMPT 'please input the trigger owner:'
ACCEPT file_name CHAR PROMPT 'please enter the output file path:'
SET TERMOUT OFF
SET FEEDBACK OFF
-- 设置when_clause字段格式并使之折行
COLUMN when_clause FORMAT A60 WORD_WRAPPED
SPOOL &file_name

SELECT 'CREATE OR REPLACE TRIGGER ' || description FROM all_triggers
WHERE trigger_name = UPPER('&trigger_name')
AND owner = UPPER('&trigger_owner');
SELECT 'WHEN (' || when_clause || ')' when_caluse FROM all_triggers
WHERE trigger_name = UPPER('&trigger_name')
AND owner = UPPER('&trigger_owner')
AND when_clause IS NOT NULL;
SELECT trigger_body FROM all_triggers
WHERE trigger_name = UPPER('&trigger_name')
AND owner = UPPER('&trigger_owner');
SELECT '/' FROM DUAL;

SPOOL OFF
SET TERMOUT ON
SET FEEDBACK ON

SET VERIFY ON
SET HEADING ON
SET PAGESIZE 24

 

3) 打开和关闭触发器：

  -- 打开触发器
  ALTER TRIGGER trigger_name DISABLE;
  -- 关闭触发器
  ALTER TRIGGER trigger_name ENABLE;

2.3 触发器的新功能

创建作用于特定数据库与数据定义事件的触发器。事件包括：
1） 关闭数据库(startup)；
2） 启动数据库(shutdown)；
3）登陆数据库(logon)；
4） 退出数据库(logoff)；
5） 发生服务器错误(server error)；
6） 创建对象(create命令)；
7） 修改对象(alter命令)；
8） 删除对象(delete命令)；

1) 数据库事件触发器：

数据库事件触发器可以定义为数据库级和模式级。
要创建数据库级的触发器，必须有administrator database trigger 的系统权限，该权限提供
给DBA角色，通常只有数据库管理员有此权限。
事 件 触发器关键词 模 式 级 数据库级
启动数据库 STARTUP 否 是
关闭数据库 SHUTDOWN 否 是
服务器错误 SERVERERROR 否 是
登陆数据库 LOGON 是 是
退出数据库 LOGOFF 是 是
创建对象 CREATE 是 否
修改对象 ALTER 是 否
删除对象 DELETE 是 否

2) 事件属性：
编写触发器时，可以象变量一样使用这些属性。
事件属性 描 述 适应的触发器类型
sys.sysevent 返回一个20个字符的字符串，描述导致触发器
激发的事件 所有类型
sys.intance_num 返回Oracle 实例编号 所有类型
sys.database_name 返回数据库品牌，通常为“Oracle” 所有类型
sys.server_error
(stack_position)
从错误堆栈的指定位置返回错误号，使用
sys.server_error(1) 查找最近的错误 SERVERERROR
sys.login_user 返回导致触发器激发的用户名 所有类型
sys.dictionary_obj_
type
返回当DDL触发器激发时涉及到的对象类型 CREATE、ALTER、DROP
sys.dictionary_obj_
name
返回当DDL触发器激发时涉及到的对象名称 CREATE、ALTER、DROP

sys.des_encrypted_p
assword
创建或修改用户时，返回加密后的该用户的密
码 CREATE、ALTER、DROP

3) 创建数据库事件触发器实例：

CREATE TABLE uptime_log(
   database_name VARCHAR2(30),
   event_name VARCHAR2(30),
   event_time DATE,
   triggered_by_user VARCHAR2(30)

);

-- 创建启动数据库时的事件触发器。

 

 

CREATE OR REPLACE TRIGGER Log_Startup

  AFTER Startup ON DATABASE

BEGIN

  INSERT INTO Uptime_Log

  VALUES

    (Sys.Database_Name, Sys.Sysevent, SYSDATE, Sys.Login_User);

END;

/

 

-- 创建关闭数据库时的事件触发器。

 

 

CREATE OR REPLACE TRIGGER Log_Shutdown

  AFTER Shutdown ON DATABASE

BEGIN

  INSERT INTO Uptime_Log

  VALUES

    (Sys.Database_Name, Sys.Sysevent, SYSDATE, Sys.Login_User);

END;

/

 

4) 创建DDL事件触发器实例：

 

CREATE TABLE alter_audit_trail(
    object_owner VARCHAR2(30),
    object_name VARCHAR2(30),
    object_type VARCHAR2(30),
    alter_by_user VARCHAR2(30),
    sys_event VARCHAR2(30),
    alteration_time DATE

);

-- 当SCOTT用户创建、修改或删除时的模式触发器。

 

CREATE OR REPLACE TRIGGER Tri_Alter_Audit_Trail

  AFTER ALTER OR CREATE OR Drop ON Scott.SCHEMA

BEGIN

  IF Sys.Sysevent = 'ALTER' THEN

    INSERT INTO Alter_Audit_Trail

    VALUES

      (Sys.Dictionary_Obj_Owner, Sys.Dictionary_Obj_Name,

       Sys.Dictionary_Obj_Type, Sys.Login_User, 'my_alter', SYSDATE);

  ELSIF Sys.Sysevent = 'CREATE' THEN

    NULL;

  ELSIF Sys.Sysevent = 'DROP' THEN

    NULL;

  END IF;

END;

/

 

4 替代触发器
语法同建立表触发器，使用关键词INTEAD_OF代替BEFORE或AFTER。
替代触发器解决了不能修改视图的问题，它只能在视图上创建，执行一个PL/SQL代码块，而不是一条DML语句
(如INSERT、UPDATE)。
编写替代触发器时，主要是编写更新视图底层的表的代码。同时也可以针对视图中的某个字段或所有字段。语
法：
CREATE OR REPLACE TRIGGER tri_emp_view
INSTEAD OF UPDATE ON emp_view
BEGIN
PL/SQL code;
END;

5 触发器的局限性

1. 触发器无法完成的工作：
1) 查询或修改变化的表；
2) 执行数据定义语言(CREATE、DROP、ALTER)的语句；
3) 执行COMMIT、ROLLBACK、SAVEPOINT 语句；
注：变化的表是指执行触发器时正在被修改的表。
触发器中不能执行COMMIT、ROLLBACK、SAVEPOINT 语句，以确保对事务控制的有效性及数据的完整性。

2. 如果使用触发器来实现商务规则或引用完整性规则，不会影响在触发器创建之前的记录。创建声明性约束实
际上是指出数据必须为真，如果出现了违反了约束的数据，则不能建立约束。

3. 不能对特定模式对象(例如：表)来定义DDL触发器。