22 Triggers

001 This chapter discusses triggers, which are procedures stored in PL/SQL or Java that run (fire) implicitly whenever a table or view is modified or when some user actions or database system actions occur.
　		 本章讨论触发器[trigger]。触发器是由 PL/SQL 或 Java 编写的过程[procedure]，当表或视图被修改或发生用户操作[user action]或数据库系统操作[database system action]时被触发运行[fire]。
　
002 This chapter contains the following topics: 本章包含以下主题：
003

Introduction to Triggers

22.1 触发器简介
004 You can write triggers that fire whenever one of the following operations occurs:
  • DML statements (INSERT, UPDATE, DELETE) on a particular table or view, issued by any user
  • DDL statements (CREATE or ALTER primarily) issued either by a particular schema/user or by any schema/user in the database
  • Database events, such as logon/logoff, errors, or startup/shutdown, also issued either by a particular schema/user or by any schema/user in the database
 用户可以设定当以下操作发生时，触发器[trigger]被触发运行：
  • 用户提交了针对特定表或视图的 DML 语句（INSERT，UPDATE，DELETE）
  • 特定的方案[schema]/用户或数据库内任意方案/用户提交了 DDL 语句（主要指 CREATE 及 ALTER）
  • 特定的方案/用户或数据库内任意方案/用户提交的数据库事件[event]，例如登录/退出[logon/logoff]，错误[error]，或启动/关闭[startup/shutdown]
005 Triggers are similar to stored procedures. A trigger stored in the database can include SQL and PL/SQL or Java statements to run as a unit and can invoke stored procedures. However, procedures and triggers differ in the way that they are invoked. A procedure is explicitly run by a user, application, or trigger. Triggers are implicitly fired by Oracle when a triggering event occurs, no matter which user is connected or which application is being used.
　		 触发器与存储过程[stored procedure]类似。数据库触发器内可以包含 Java 语句，或 SQL 语句及 PL/SQL 结构，也可以调用其他存储过程。触发器与存储过程的区别在于她们的调用方式不同。存储过程是由用户，应用程序或触发器显式地调用的。而触发器是在触发事件[triggering event]发生时由 Oracle 隐式地触发的，触发器的运行与连接到数据库的用户及用户使用的是何种应用程序无关。
　
006 Figure 22-1 shows a database application with some SQL statements that implicitly fire several triggers stored in the database. Notice that the database stores triggers separately from their associated tables.
　		 图 22-1 展示了数据库应用程序中的 SQL 语句能够隐式地触发数据库内存储的触发器。注意，在数据库中触发器与其相关表的存储位置是不同的。
　
007 Figure 22-1 Triggers
　		 图 22-1 触发器
　
008


　


　
009
Figure 22-1 is described in the text preceding the image.
图 22-1 将在下文中说明。
010 A trigger can also call out to a C procedure, which is useful for computationally intensive operations.
　		 触发器可以调用外部的 C 语言过程，从而保证计算密集型操作[computationally intensive operation]的执行效率。
　
011 The events that fire a trigger include the following:
  • DML statements that modify data in a table (INSERT, UPDATE, or DELETE)
  • DDL statements
  • System events such as startup, shutdown, and error messages
  • User events such as logon and logoff
 能够导致触发器被调用的事件包括：
  • 修改表数据的 DML 语句（INSERT，UPDATE，或 DELETE）
  • DDL 语句
  • 启动[startup]，关闭[shutdown]或错误消息[error message]等系统事件[system event]
  • 登录[logon]及退出[logoff]等用户事件[user event]
012
Note:

Oracle Forms can define, store, and run triggers of a different sort. However, do not confuse Oracle Forms triggers with the triggers discussed in this chapter.
提示：

在 Oracle Forms 中也可以定义，存储，及运行多种触发器。但不要将 Oracle Form 触发器与本章讲述的数据库触发器相混淆。
013

See Also:

 另见：
014

How Triggers Are Used

22.1.1 如何运用触发器
015 Triggers supplement the standard capabilities of Oracle to provide a highly customized database management system. For example, a trigger can restrict DML operations against a table to those issued during regular business hours. You can also use triggers to:
  • Automatically generate derived column values
  • Prevent invalid transactions
  • Enforce complex security authorizations
  • Enforce referential integrity across nodes in a distributed database
  • Enforce complex business rules
  • Provide transparent event logging
  • Provide auditing
  • Maintain synchronous table replicates
  • Gather statistics on table access
  • Modify table data when DML statements are issued against views
  • Publish information about database events, user events, and SQL statements to subscribing applications
 触发器[trigger]是 Oracle 数据库提供的标准功能，用户可以通过触发器实现高度自定义的[highly customized]数据库管理系统。例如，用户可以使用触发器阻止在正常工作时间对指定的表执行 DML 操作。用户还可以使用触发器完成以下工作：
  • 自动地计算派生列值[derived column value]
  • 防止无效的事务[invalid transaction]破坏数据
  • 强制实现复杂的安全授权[complex security authorization]
  • 在分布式数据库[distributed database]的不同节点[node]上强制实现引用完整性约束[referential integrity]
  • 强制实现复杂的业务规则[complex business rule]
  • 实现透明事件日志[transparent event logging]功能
  • 实现审计[auditing]功能
  • 实现同步表复制[synchronous table replicate]
  • 收集数据表访问的统计信息
  • 当用户对视图执行 DML 操作时修改相关的表数据
  • 为订阅[subscribing]了事件的应用程序发布[publish]关于数据库事件[database event]，用户事件[user event]或 SQL 语句的信息。
016
See Also:

Oracle Database Application Developer's Guide - Fundamentals for examples of trigger uses
另见：

Oracle Database Application Developer's Guide - Fundamentals 查找使用触发器的示例
017

Some Cautionary Notes about Triggers

22.1.1.1 使用触发器的注意事项
018 Although triggers are useful for customizing a database, use them only when necessary. Excessive use of triggers can result in complex interdependencies, which can be difficult to maintain in a large application. For example, when a trigger fires, a SQL statement within its trigger action potentially can fire other triggers, resulting in cascading triggers. This can produce unintended effects. Figure 22-2 illustrates cascading triggers.
　		 尽管用户可以通过触发器[trigger]实现自定义的[customized]数据库管理系统，但应在必要时才使用触发器。过度的使用触发器将导致系统内存在过于复杂的依赖性[interdependency]，从而使大型应用系统变得难以维护。例如，当一个触发器被触发时，其中的 SQL 语句有可能触发其他触发器，从而引起连锁触发[cascading trigger]。这有可能造成不可预料的效果。图 22-2 是一个连锁触发的示例。
　
019 Figure 22-2 Cascading Triggers
　		 图 22-2 连锁触发
　
020


　


　
021
Figure 22-2 shows cascading triggers. One SQL statement fires the UPDATE_T1 trigger, which fires the INSERT_T2 trigger, and so on.
图 22-2 是一个连锁触发的示例。首先，SQL 语句触发了触发器 UPDATE_T1，进而导致触发器 INSERT_T2 被触发。
022

Triggers Compared with Declarative Integrity Constraints

22.1.1.2 使用触发器与声明完整性约束的比较
023 You can use both triggers and integrity constraints to define and enforce any type of integrity rule. However, Oracle strongly recommends that you use triggers to constrain data input only in the following situations:
  • To enforce referential integrity when child and parent tables are on different nodes of a distributed database
  • To enforce complex business rules not definable using integrity constraints
  • When a required referential integrity rule cannot be enforced using the following integrity constraints:

    • 　
    • NOT NULL, UNIQUE
    • PRIMARY KEY
    • FOREIGN KEY
    • CHECK
    • DELETE CASCADE
    • DELETE SET NULL
 用户可以使用触发器[trigger]或完整性约束[integrity constraint]来定义并强制实现各类完整性约束规则[integrity rule]。但是，Oracle 强烈建议用户只在以下情况使用触发器来约束数据输入：
  • 需要强制实现引用完整性[referential integrity]的子表[child table]及父表[parent table]位于分布式数据库[distributed database]的不同节点[node]上
  • 强制实现无法通过完整性约束定义的复杂业务规则[complex business rule]
  • 当一个引用完整性规则无法通过以下约束及约束操作实现时：

    • 　
    • NOT NULL，UNIQUE
    • PRIMARY KEY
    • FOREIGN KEY
    • CHECK
    • DELETE CASCADE
    • DELETE SET NULL
024
See Also:

"How Oracle Enforces Data Integrity" for more information about integrity constraints
另见：

“Oracle 如何强制实现数据完整性”了解关于完整性约束的更多信息
025

Parts of a Trigger

22.2 触发器的组成部分
026 A trigger has three basic parts:
  • A triggering event or statement
  • A trigger restriction
  • A trigger action
 一个触发器[trigger]包含 3 个基本组成部分：
  • 触发事件[triggering event]或触发语句[triggering statement]
  • 触发限制条件[trigger restriction]
  • 触发操作[trigger action]
027 Figure 22-3 represents each of these parts of a trigger and is not meant to show exact syntax. The sections that follow explain each part of a trigger in greater detail.
　		 图 22-3 展示了触发器的各个组成部分[图中语法并不完全准确]。以下各节将对各个组成部分进行详细介绍。
　
028 Figure 22-3 The REORDER Trigger
　		 图 22-3 触发器 REORDER
　
029


　


　
030
Figure 22-3 shows the parts of a REORDER trigger. In the top box is the triggering statement. Under that is the trigger restriction, and under that is the triggered action.
图 22-3 显示了触发器 REORDER 的各个组成部分。图的最上端是触发语句。其下为触发限制条件。最下为触发操作。
031

The Triggering Event or Statement

22.2.1 触发事件与触发语句
032 A triggering event or statement is the SQL statement, database event, or user event that causes a trigger to fire. A triggering event can be one or more of the following:
  • An INSERT, UPDATE, or DELETE statement on a specific table (or view, in some cases)
  • A CREATE, ALTER, or DROP statement on any schema object
  • A database startup or instance shutdown
  • A specific error message or any error message
  • A user logon or logoff
 触发语句[triggering statement]及触发事件[triggering event]指导致触发器被触发的 SQL 语句，及数据库事件[database event]或用户事件[user event]。触发事件及触发语句可以是以下情况：
  • 针对特定表（在某些情况下还可以针对视图）执行的 INSERT，UPDATE，或 DELETE 语句
  • 针对任意方案对象执行的 CREATE，ALTER，或 DROP 语句
  • 数据库实例的启动[startup]及关闭[shutdown]
  • 指定的错误消息[error message]或任意错误消息
  • 用户登录[logon]或退出[logoff]
033 For example, in Figure 22-3, the triggering statement is:
　		 例如，在 图 22-3 中触发语句为：
　
034
... UPDATE OF parts_on_hand ON inventory ...
... UPDATE OF parts_on_hand ON inventory ...
035 This statement means that when the parts_on_hand column of a row in the inventory table is updated, fire the trigger. When the triggering event is an UPDATE statement, you can include a column list to identify which columns must be updated to fire the trigger. You cannot specify a column list for INSERT and DELETE statements, because they affect entire rows of information.
　		 此语句表示，当 inventory 表内数据行的 parts_on_hand 列被更新时触发器将被触发。如果触发事件是 UPDATE 语句，用户可以在触发器定义中包含一个数据列列表，规定只有在列表中的列被更新时触发器才被触发。用户无法为 INSERT 及 DELETE 语句的触发事件设定数据列列表，因为这两种语句会对整个数据行产生影响。
　
036 A triggering event can specify multiple SQL statements:
　		 一个触发事件中可以针对多种 SQL 语句：
037
... INSERT OR UPDATE OR DELETE OF inventory ...
... INSERT OR UPDATE OR DELETE OF inventory ...
038 This part means that when an INSERT, UPDATE, or DELETE statement is issued against the inventory table, fire the trigger. When multiple types of SQL statements can fire a trigger, you can use conditional predicates to detect the type of triggering statement. In this way, you can create a single trigger that runs different code based on the type of statement that fires the trigger.
　		 此语句表示，当用户对 inventory 表执行 INSERT，UPDATE，或 DELETE 语句时，触发器将被触发。当多种类型的 SQL 语句均可以触发某个触发器时，用户可以使用条件谓词[conditional predicate]来判断触发语句的类型。因此，用户可以在一个触发器内，根据触发语句的类型执行不同的程序代码。
　
039

Trigger Restriction

22.2.2 触发限制条件
040 A trigger restriction specifies a Boolean expression that must be true for the trigger to fire. The trigger action is not run if the trigger restriction evaluates to false or unknown. In the example, the trigger restriction is:
　		 触发限制条件[rigger restriction]是一个布尔表达式[Boolean expression]，只有在表达式为 true 时触发器才被触发。如果触发限制条件的结果为 false 或 unknown 则触发操作[trigger action]不会被执行。示例中的触发限制条件为：
　
041
new.parts_on_hand < new.reorder_point
new.parts_on_hand < new.reorder_point
042 Consequently, the trigger does not fire unless the number of available parts is less than a present reorder amount.
　		 因此，只有在零件库存数量小于重下订单需求数量[reorder amount]时，触发器才被触发。
　
043

Trigger Action

22.2.3 触发操作
044 A trigger action is the procedure (PL/SQL block, Java program, or C callout) that contains the SQL statements and code to be run when the following events occur:
  • A triggering statement is issued.
  • The trigger restriction evaluates to true.
 触发操作[trigger action]是包含 SQL 语句及控制代码的过程[包括 PL/SQL 块，Java 程序，C 外部调用]，满足以下条件时触发操作会被执行：
  • 用户提交了触发语句[triggering statement]
  • 触发限制条件[trigger restriction]为 true.
045 Like stored procedures, a trigger action can:
  • Contain SQL, PL/SQL, or Java statements
  • Define PL/SQL language constructs such as variables, constants, cursors, exceptions
  • Define Java language constructs
  • Call stored procedures
 与存储过程[stored procedure]类似，触发操作具有以下特点：
  • 可以包含 SQL，PL/SQL，或 Java 语句
  • 可以定义变量[variable]，常量[constant]，游标[cursor]，异常[exception]等 PL/SQL 程序结构[language construct]
  • 可以定义 Java 程序结构
  • 可以调用存储过程
046 If the triggers are row triggers, the statements in a trigger action have access to column values of the row being processed by the trigger. Correlation names provide access to the old and new values for each column.
　		 如果触发器为行触发器[row trigger]，则触发操作能够访问正在被触发器处理的数据行的各个列值。用户可以按照 Oracle 的命名规则访问各列的旧值[old value]及新值[new value]。
　
047

Types of Triggers

22.3 触发器的类型
048 This section describes the different types of triggers: 本节讲述不同类型的触发器：
049

Row Triggers and Statement Triggers

22.3.1 行触发器与语句触发器
050 When you define a trigger, you can specify the number of times the trigger action is to be run:
  • Once for every row affected by the triggering statement, such as a trigger fired by an UPDATE statement that updates many rows
  • Once for the triggering statement, no matter how many rows it affects
 在定义触发器时[trigger]，用户可以设定触发操作[trigger action]的执行次数：
  • 触发语句[triggering statement]所影响的每个数据行都将使触发器被触发一次，例如 UPDATE 语句所更新的多个数据行将多次引发触发器
  • 触发语句执行一次触发器被触发一次，而与语句所影响的数据行数无关
051

Row Triggers

22.3.1.1 行触发器
052 A row trigger is fired each time the table is affected by the triggering statement. For example, if an UPDATE statement updates multiple rows of a table, a row trigger is fired once for each row affected by the UPDATE statement. If a triggering statement affects no rows, a row trigger is not run.
　		 行触发器[row trigger]在数据表每次被触发语句[triggering statement]修改时被触发。例如，UPDATE 语句更新了表内的多行数据，行触发器在此 UPDATE 语句更新每行数据时均被触发一次。如果触发语句没有影响任何数据行，则行触发器不会被触发。
　
053 Row triggers are useful if the code in the trigger action depends on data provided by the triggering statement or rows that are affected. For example, Figure 22-3 illustrates a row trigger that uses the values of each row affected by the triggering statement.
　		 如果触发操作[trigger action]的代码需要依据触发语句及其所修改的数据行来执行，就需要使用行触发器。例如，图 22-3 所示的行触发器，需要依据触发语句所修改的数据行来执行。
　
054

Statement Triggers

22.3.1.2 语句触发器
055 A statement trigger is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even if no rows are affected. For example, if a DELETE statement deletes several rows from a table, a statement-level DELETE trigger is fired only once.
　		 语句触发器[statement trigger]在触发语句[triggering statement]每次执行时被触发一次，无论触发语句影响了数据表内多少行数据（或没有修改任何数据行）。例如，当一条 DELETE 语句删除了数据表内的多行数据时，将触发语句级的 DELETE 触发器执行一次。
　
056 Statement triggers are useful if the code in the trigger action does not depend on the data provided by the triggering statement or the rows affected. For example, use a statement trigger to:
  • Make a complex security check on the current time or user
  • Generate a single audit record
 如果触发操作[trigger action]的代码不需要依据触发语句及其所修改的数据行来执行，就可以使用语句触发器。例如，可以使用语句触发器：
  • 进行实时的复杂安全性检查[complex security check]
  • 生成一条审计记录[audit record]
057

BEFORE and AFTER Triggers

22.3.2 BEFORE 触发器与 AFTER 触发器
058 When defining a trigger, you can specify the trigger timing—whether the trigger action is to be run before or after the triggering statement. BEFORE and AFTER apply to both statement and row triggers.
　		 在定义触发器时，用户可以指定触发时机[trigger timing]，即设定触发操作[trigger action]在触发语句[triggering statement]之前或之后执行。BEFORE 及 AFTER 可以应用于语句触发器[statement trigger]及行触发器[row trigger]。
　
059 BEFORE and AFTER triggers fired by DML statements can be defined only on tables, not on views. However, triggers on the base tables of a view are fired if an INSERT, UPDATE, or DELETE statement is issued against the view. BEFORE and AFTER triggers fired by DDL statements can be defined only on the database or a schema, not on particular tables.
　		 由 DML 语句触发的 BEFORE 及 AFTER 触发器只能定义在表上，而不能定义在视图上。但在对视图执行 INSERT，UPDATE，或 DELETE 语句时，相关基表[base table]上的触发器仍会被触发。由 DDL 语句触发的 BEFORE 及 AFTER 触发器只能定义在数据库或方案[schema]上，而不能针对特定表。
　
060

See Also:

See Also:
061

BEFORE Triggers

22.3.2.1 BEFORE 触发器
062 BEFORE triggers run the trigger action before the triggering statement is run. This type of trigger is commonly used in the following situations:
  • When the trigger action determines whether the triggering statement should be allowed to complete. Using a BEFORE trigger for this purpose, you can eliminate unnecessary processing of the triggering statement and its eventual rollback in cases where an exception is raised in the trigger action.
  • To derive specific column values before completing a triggering INSERT or UPDATE statement.
 BEFORE 触发器在触发语句[triggering statement]运行前执行触发操作[trigger action]。此种触发器可以在以下情况使用：
  • 由触发操作决定触发语句是否可以执行。此时使用 BEFORE 触发器可以避免触发语句因异常而最终被回滚，因此减少了数据库中不必要的语句处理工作量。
  • 在执行 INSERT 或 UPDATE 触发语句之前计算特定的列值。
063

AFTER Triggers

22.3.2.2 AFTER 触发器
064 AFTER triggers run the trigger action after the triggering statement is run.
　		 AFTER 触发器在触发语句[triggering statement]运行后执行触发操作[trigger action]。
　
065

Trigger Type Combinations

22.3.2.3 触发器类型的组合
066 Using the options listed previously, you can create four types of row and statement triggers:
  • BEFORE statement trigger

    Before executing the triggering statement, the trigger action is run.
  • BEFORE row trigger

    Before modifying each row affected by the triggering statement and before checking appropriate integrity constraints, the trigger action is run, if the trigger restriction was not violated.
  • AFTER statement trigger

    After executing the triggering statement and applying any deferred integrity constraints, the trigger action is run.
  • AFTER row trigger

    After modifying each row affected by the triggering statement and possibly applying appropriate integrity constraints, the trigger action is run for the current row provided the trigger restriction was not violated. Unlike BEFORE row triggers, AFTER row triggers lock rows.
 用户可以根据前面讲述的触发器类型选项，创建 4 种行触发器[row trigger]及语句触发器[statement trigger]：
  • BEFORE 语句触发器

    在执行触发语句[triggering statement]之前，首先执行触发操作[trigger action]。
  • BEFORE 行触发器

    在触发语句修改各数据行，及进行相关完整性约束检查之前，如果符合触发限制条件[trigger restriction]则执行触发操作。
  • AFTER 语句触发器

    在触发语句执行完毕，且相关延迟完整性约束[deferred integrity constraint]也检查完毕后，执行触发操作。
  • AFTER 行触发器

    当触发语句修改了一行数据，且相关完整性约束检查完毕后，如果符合触发限制条件[trigger restriction]则执行触发操作。与 BEFORE row 触发器不同，AFTER row 触发器将对数据行加锁。
067 You can have multiple triggers of the same type for the same statement for any given table. For example, you can have two BEFORE statement triggers for UPDATE statements on the employees table. Multiple triggers of the same type permit modular installation of applications that have triggers on the same tables. Also, Oracle materialized view logs use AFTER row triggers, so you can design your own AFTER row trigger in addition to the Oracle-defined AFTER row trigger.
　		 用户可以为一个数据表创建触发语句及类型（BEFORE 或 AFTER）均相同的触发器。例如，用户可以在 employees 上为 UPDATE 语句创建两个 BEFORE statement 触发器。此特性允许不同的应用程序在同一数据表上创建类型相同的触发器。Oracle 在物化视图日志[materialized view log]中使用 AFTER row 触发器，用户可以在 Oracle 定义的触发器之外定义自己的 AFTER row 触发器。
　
068 You can create as many triggers of the preceding different types as you need for each type of DML statement, (INSERT, UPDATE, or DELETE).
　		 用户为各种 DML 语句（INSERT，UPDATE，或 DELETE）创建前述各 类型触发器的总数量不受限制。
　
069
See Also:

Oracle Database Application Developer's Guide - Fundamentals for examples of trigger applications
另见：

Oracle Database Application Developer's Guide - Fundamentals 了解应用触发器的例子
070

INSTEAD OF Triggers

22.3.3 INSTEAD OF 触发器
071 INSTEAD OF triggers provide a transparent way of modifying views that cannot be modified directly through DML statements (INSERT, UPDATE, and DELETE). These triggers are called INSTEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.
　		 用户可以使用 INSTEAD OF 触发器透明地修改不能由 DML 语句（INSERT，UPDATE，及 DELETE）直接修改的视图。此类触发器被称为 INSTEAD OF 触发器的原因是 Oracle 只运行触发操作[trigger action]而不运行触发语句[triggering statement]。
　
072 You can write normal INSERT, UPDATE, and DELETE statements against the view and the INSTEAD OF trigger is fired to update the underlying tables appropriately. INSTEAD OF triggers are activated for each row of the view that gets modified.
　		 用户可以对视图执行普通的 INSERT，UPDATE，及 DELETE 语句，并定义 INSTEAD OF 触发器修改相关的底层表。视图中每个被修改的数据行都将触发 INSTEAD OF 触发器运行一次。
　
073

Modify Views

22.3.3.1 修改视图数据
074 Modifying views can have ambiguous results:
  • Deleting a row in a view could either mean deleting it from the base table or updating some values so that it is no longer selected by the view.
  • Inserting a row in a view could either mean inserting a new row into the base table or updating an existing row so that it is projected by the view.
  • Updating a column in a view that involves joins might change the semantics of other columns that are not projected by the view.
 修改视图数据对基表[base table]的影响是不确定的：
  • 删除视图内的数据行时，既可以删除基表内的数据行，也可以更新基表的某些值使之不能再被视图查询。
  • 向视图内插入数据行时，既可以向基表插入新数据行，也可以更新基表内已有的数据行使之包含在视图的查询结果中。
  • 更新一个包含连接的视图时，有可能修改未包含于视图定义内的其他数据列的语义。
075 Object views present additional problems. For example, a key use of object views is to represent master/detail relationships. This operation inevitably involves joins, but modifying joins is inherently ambiguous.
　		 操作对象视图[object view]时还存在额外的问题。例如，对象视图的重要用途是表现主信息/明细信息关系[master/detail relationship]。此类对象视图中必然包含连接，因此修改此类视图时对基表的影响也是不确定的。
　
076 As a result of these ambiguities, there are many restrictions on which views are modifiable. An INSTEAD OF trigger can be used on object views as well as relational views that are not otherwise modifiable.
　		 由于存在不确定性，因此一个视图是否能被修改存在诸多限制。对于不可修改的关系型视图[relational view]及对象视图[object view]可以使用 INSTEAD OF 触发器进行修改。
　
077 A view is inherently modifiable if data can be inserted, updated, or deleted without using INSTEAD OF triggers and if it conforms to the restrictions listed as follows. Even if the view is inherently modifiable, you might want to perform validations on the values being inserted, updated or deleted. INSTEAD OF triggers can also be used in this case. Here the trigger code performs the validation on the rows being modified and if valid, propagate the changes to the underlying tables.
　		 如果一个视图满足修改的限制条件（详见下节），可以直接插入，更新，或删除而无需使用 INSTEAD OF 触发器，则称此视图为内在可修改的[inherently modifiable]。 即便视图是内在可修改的，用户可能还需要在插入，更新，或删除前验证相关的数据值。此种需求也可以通过 INSTEAD OF 触发器实现。用户可以在触发器代码中验证将要被修改的数据是否有效，验证正确后才将修改应用到相关的底层表中。
　
078 INSTEAD OF triggers also enable you to modify object view instances on the client-side through OCI. To modify an object materialized by an object view in the client-side object cache and flush it back to the persistent store, you must specify INSTEAD OF triggers, unless the object view is inherently modifiable. However, it is not necessary to define these triggers for just pinning and reading the view object in the object cache.
　		 用户还可以在 INSTEAD OF 触发器中通过 OCI 修改客户端[client-side]的对象视图实例[instance]。如果对象视图不是内在可修改的，则必须使用 INSTEAD OF 触发器修改客户端对象缓存[object cache]内的视图实例，或对视图实例进行持续存储[persistent store]。如果只需锁定[pinning]或读取[reading]对象缓存内的对象视图，则无需使用触发器。
　
079

See Also:

另见：
080

Views That Are Not Modifiable

22.3.3.2 不可修改数据的视图
081 If the view query contains any of the following constructs, the view is not inherently modifiable and you therefore cannot perform inserts, updates, or deletes on the view:
  • Set operators
  • Aggregate functions
  • GROUP BY, CONNECT BY, or START WITH clauses
  • The DISTINCT operator
  • Joins (however, some join views are updatable)
 如果视图查询内包含以下结构，则视图不是内在可修改的[inherently modifiable]，用户不能直接对视图执行插入，删除，或更新操作：
  • 集合操作符[set operator]
  • 聚合函数[aggregate function]
  • GROUP BY，CONNECT BY，或 START WITH 子句
  • DISTINCT 操作符
  • 连接[join]（但有些连接视图[join view]是可更新的）
082 If a view contains pseudocolumns or expressions, you can only update the view with an UPDATE statement that does not refer to any of the pseudocolumns or expressions.
　		 如果视图查询中包含虚列[pseudocolumn]或表达式[expression]，用户只能提交没有引用虚列及表达式的 UPDATE 语句修改视图。
　
083
See Also:

"Updatable Join Views"
另见：

“可更新的连接视图”
084

INSTEAD OF Triggers on Nested Tables

22.3.3.3 嵌套表上的 INSTEAD OF 触发器
085 You cannot modify the elements of a nested table column in a view directly with the TABLE clause. However, you can do so by defining an INSTEAD OF trigger on the nested table column of the view. The triggers on the nested tables fire if a nested table element is updated, inserted, or deleted and handle the actual modifications to the underlying tables.
　		 用户不能直接使用 TABLE 子句修改视图中嵌套表列[nested table column]内的元素[element]。但此需求可以通过在嵌套表上定义 INSTEAD OF 触发器来实现。如果插入，更新或删除了视图内的嵌套表元素，嵌套表的触发器将被触发，对嵌套表执行实际的修改。
　
086

See Also:

另见：
087

Triggers on System Events and User Events

22.3.4 系统事件触发器与用户事件触发器
088 You can use triggers to publish information about database events to subscribers. Applications can subscribe to database events just as they subscribe to messages from other applications. These database events can include:
  • System events

    • Database startup and shutdown
    • Data Guard role transitions
    • Server error message events
  • User events

    • User logon and logoff
    • DDL statements (CREATE, ALTER, and DROP)
    • DML statements (INSERT, DELETE, and UPDATE)
 用户可以使用触发器[trigger]向订阅者[subscriber]发布[publish]数据库事件[database event]信息。应用程序可以订阅数据库事件，这与订阅其他应用程序发布的消息类似。可供订阅的数据库事件包括：
  • 系统事件[system event]

    • 数据库启动[startup]与关闭[shutdown]
    • Data Guard 角色变换[role transition]
    • 服务器错误消息事件[server error message event]
  • 用户事件[user event]

    • 用户登录[logon]与退出[logoff]
    • DDL 语句（CREATE，ALTER，与 DROP）
    • DML 语句（INSERT，DELETE，与 UPDATE）
089 Triggers on system events can be defined at the database level or schema level. The DBMS_AQ package is one example of using database triggers to perform certain actions. For example, a database shutdown trigger is defined at the database level:
　		 针对数据库事件的触发器可以定义在数据库级[database level]或方案级[schema level]。常见的例子是在触发器中调用 DBMS_AQ 包执行特定操作。如下所示，在数据库级定义了数据库关闭触发器：
090
CREATE TRIGGER register_shutdown
ON DATABASE
SHUTDOWN
BEGIN
...
DBMS_AQ.ENQUEUE(...);
...
END;
CREATE TRIGGER register_shutdown
ON DATABASE
SHUTDOWN
BEGIN
...
DBMS_AQ.ENQUEUE(...);
...
END;
091 Triggers on DDL statements or logon/logoff events can also be defined at the database level or schema level. Triggers on DML statements can be defined on a table or view. A trigger defined at the database level fires for all users, and a trigger defined at the schema or table level fires only when the triggering event involves that schema or table.
　		 DDL 语句触发器及登录/退出触发器也可以定义于数据库级或方案级。而 DML 语句触发器只能定义在表或视图上。定义于数据库级的触发器可以被所有用户触发，而定义于方案级或表级的触发器只有在方案或表被包含于触发事件[triggering event]时才得到触发。
　
092

Event Publication

22.3.4.1 事件发布
093 Event publication uses the publish-subscribe mechanism of Oracle Streams Advanced Queuing. A queue serves as a message repository for subjects of interest to various subscribers. Triggers use the DBMS_AQ package to enqueue a message when specific system or user events occur.
　		 事件发布[event publication]采用的是 Oracle 数据流高级队列[Streams Advanced Queuing]的发布-订阅机制[publish-subscribe mechanism]。队列[queue]如同一个消息资料库[message repository]，其中存储了订阅者[subscriber]感兴趣的信息。触发器使用 DBMS_AQ 包将指定的系统事件或用户事件加入队列。
　
094

See Also:

另见：
095

Event Attributes

22.3.4.2 事件属性
096 Each event allows the use of attributes within the trigger text. For example, the database startup and shutdown triggers have attributes for the instance number and the database name, and the logon and logoff triggers have attributes for the user name. You can specify a function with the same name as an attribute when you create a trigger if you want to publish that attribute when the event occurs. The attribute's value is then passed to the function or payload when the trigger fires. For triggers on DML statements, the :OLD column values pass the attribute's value to the :NEW column value.
　		 在触发器代码内可以使用事件属性[event attribute]。例如数据库启动/关闭[startup/shutdown]触发器可以获得实例编号及数据库名等属性，用户登录/退出[logon/logoff]触发器可以获得用户名等属性。开发者在创建触发器时可以创建与事件属性同名的函数，以便在事件发生时发布此属性。当触发器被触发时事件属性值将被传递给相应的函数。对于 DML 语句触发器，:OLD 列值能够把属性值传递给 :NEW 列值。
　
097

System Events

22.3.4.3 系统事件
098 System events that can fire triggers are related to instance startup and shutdown and error messages. Triggers created on startup and shutdown events have to be associated with the database. Triggers created on error events can be associated with the database or with a schema.
  • STARTUP triggers fire when the database is opened by an instance. Their attributes include the system event, instance number, and database name.
  • SHUTDOWN triggers fire just before the server starts shutting down an instance. You can use these triggers to make subscribing applications shut down completely when the database shuts down. For abnormal instance shutdown, these triggers cannot be fired. The attributes of SHUTDOWN triggers include the system event, instance number, and database name.
  • SERVERERROR triggers fire when a specified error occurs, or when any error occurs if no error number is specified. Their attributes include the system event and error number.
  • DB_ROLE_CHANGE triggers fire when a role transition (failover or switchover) occurs in a Data Guard configuration. The trigger notifies users when a role transition occurs, so that client connections can be processed on the new primary database and applications can continue to run.
 能够引发触发器执行的系统事件[system event]主要包括实例启动[startup]与关闭[shutdown]，及错误消息[error message]。启动与关闭触发器只能定义于数据库级[database level]。而错误事件[error event]触发器可以定义于数据库级或方案级[schema level]。
  • 当数据库被实例打开[open]时将引发 STARTUP 触发器。此触发器可使用的事件属性有：系统事件[system event]，实例号[instance number]，及数据库名[database name]。
  • 当数据库服务器开始关闭实例时将引发 SHUTDOWN 触发器。此触发器能够向订阅了数据库关闭事件的应用程序发送消息，从而确保应用程序在数据库关闭时也能完全关闭。如果实例是异常关闭的则无法引发此触发器。SHUTDOWN 触发器可使用的事件属性有：系统事件，实例号，及数据库名。
  • 当发生了指定的错误或任意错误（且没有指定错误编号）时将引发 SERVERERROR 触发器。此触发器可使用的事件属性有：系统事件及错误编号[error number]。
  • 当 Data Guard 系统中发生角色变换（故障切换[failover ]或一般切换[switchover]）时将引发 DB_ROLE_CHANGE 触发器。此触发器能够通知用户发生了角色变换，使客户连接[client connection]转移到新的主数据库[primary database]中处理，从而确保应用程序继续运行。
099

User Events

22.3.4.4 用户事件
100 User events that can fire triggers are related to user logon and logoff, DDL statements, and DML statements.
　		 用户登录/退出[logon/logoff]，DDL 语句，及 DML 语句等用户事件[user event]能够引发触发器。
　
101

Triggers on LOGON and LOGOFF Events

22.3.4.4.1 登录及退出事件的触发器
102 LOGON and LOGOFF triggers can be associated with the database or with a schema. Their attributes include the system event and user name, and they can specify simple conditions on USERID and USERNAME.
  • LOGON triggers fire after a successful logon of a user.
  • LOGOFF triggers fire at the start of a user logoff.
 LOGON 与 LOGOFF 触发器可以定义在数据库或方案[schema]上。此类触发器可使用的属性包括：系统事件[system event]及用户名[user name]。此类触发器中可以使用 USERID 及 USERNAME 函数定义简单条件。
  • 当用户成功登录后将引发 LOGON 触发器。
  • 当用户开始退出时将引发 LOGOFF 触发器。
103

Triggers on DDL Statements

22.3.4.4.2 DDL 语句触发器
104 DDL triggers can be associated with the database or with a schema. Their attributes include the system event, the type of schema object, and its name. They can specify simple conditions on the type and name of the schema object, as well as functions like USERID and USERNAME. DDL triggers include the following types of triggers:
  • BEFORE CREATE and AFTER CREATE triggers fire when a schema object is created in the database or schema.
  • BEFORE ALTER and AFTER ALTER triggers fire when a schema object is altered in the database or schema.
  • BEFORE DROP and AFTER DROP triggers fire when a schema object is dropped from the database or schema.
 DDL 触发器可以定义在数据库或方案[schema]上。此类触发器可使用的属性包括：系统事件[system event]，方案对象类型及名称。此类触发器中可以使用方案对象类型及名称，或 USERID 及 USERNAME 函数来定义简单的条件。DDL 触发器包括以下类型：
  • BEFORE CREATE 及 AFTER CREATE 触发器，当在数据库或某一方案中创建方案对象时被触发。
  • BEFORE ALTER 及 AFTER ALTER 触发器，当修改数据库或某一方案中的方案对象时被触发。
  • BEFORE DROP 及 AFTER DROP 触发器，当移除数据库或某一方案中的方案对象时被触发。
105

Triggers on DML Statements

22.3.4.4.3 DML 语句触发器
106 DML triggers for event publication are associated with a table. They can be either BEFORE or AFTER triggers that fire for each row on which the specified DML operation occurs. You cannot use INSTEAD OF triggers on views to publish events related to DML statements—instead, you can publish events using BEFORE or AFTER triggers for the DML operations on a view's underlying tables that are caused by INSTEAD OF triggers.
　		 用于事件发布[event publication]的 DML 触发器针对数据库表。此类触发器可以是 BEFORE 或 AFTER 触发器，当指定的 DML 操作发生时，语句所影响的每行数据都将引发触发器执行。在 Oracle 中无法通过在视图上定义 INSTEAD OF 触发器来发布与视图相关的 DML 事件，要实现此类需求，用户需要在视图的底层表上定义 BEFORE 或 AFTER 触发器来发布事件。
　
107 The attributes of DML triggers for event publication include the system event and the columns defined by the user in the SELECT list. They can specify simple conditions on the type and name of the schema object, as well as functions (such as UID, USER, USERENV, and SYSDATE), pseudocolumns, and columns. The columns can be prefixed by :OLD and :NEW for old and new values. Triggers on DML statements include the following triggers:
  • BEFORE INSERT and AFTER INSERT triggers fire for each row inserted into the table.
  • BEFORE UPDATE and AFTER UPDATE triggers fire for each row updated in the table.
  • BEFORE DELETE and AFTER DELETE triggers fire for each row deleted from the table.
 用于事件发布的 DML 触发器可使用的事件属性包括：系统事件[system event]，及定义在 SELECT 列表中的列值。此类触发器中可以使用方案对象类型及名称，函数（例如 UID，USER，USERENV，或 SYSDATE 等），虚列[pseudocolumn]或列值来定义简单的条件。通过前缀 :OLD 及 :NEW 可以引用列的旧值及新值。DML 触发器包括以下类型：
  • BEFORE INSERT 及 AFTER INSERT 触发器，每个新插入数据表的数据行将引发此类触发器。
  • BEFORE UPDATE 及 AFTER UPDATE 触发器，每个被更新的数据行将引发此类触发器。
  • BEFORE DELETE 及 AFTER DELETE 触发器，每个被删除的数据行将引发此类触发器。
108

See Also:

另见：
109

Trigger Execution

22.4 触发器执行
110 A trigger is in either of two distinct modes:
　		 一个触发器之可能处于以下两种状态：
　
111
Trigger Mode
　		 Definition
　
Enabled
　		 An enabled trigger runs its trigger action if a triggering statement is issued and the trigger restriction (if any) evaluates to true.
　
Disabled
　		 A disabled trigger does not run its trigger action, even if a triggering statement is issued and the trigger restriction (if any) would evaluate to true.
　
　
触发器状态
　		 定义
　
启用[enabled]
　		 当触发器处于启用状态时，如果有触发语句[triggering statement]被提交，且触发限制条件[trigger restriction]为真，触发操作[trigger action]将被引发
　
禁用[disabled]
　		 当触发器处于禁用状态时，如果有触发语句被提交，且触发限制条件为真，触发操作也不会被引发
　
　
112 For enabled triggers, Oracle automatically performs the following actions:
  • Oracle runs triggers of each type in a planned firing sequence when more than one trigger is fired by a single SQL statement. First, statement level triggers are fired, and then row level triggers are fired.
  • Oracle performs integrity constraint checking at a set point in time with respect to the different types of triggers and guarantees that triggers cannot compromise integrity constraints.
  • Oracle provides read-consistent views for queries and constraints.
  • Oracle manages the dependencies among triggers and schema objects referenced in the code of the trigger action
  • Oracle uses two-phase commit if a trigger updates remote tables in a distributed database.
  • Oracle fires multiple triggers in an unspecified, random order, if more than one trigger of the same type exists for a given statement; that is, triggers of the same type for the same statement are not guaranteed to fire in any specific order.
 对于处在启用状态下的触发器，Oracle 将自动地执行以下操作：
  • 如果一个 SQL 语句将引发多个不同类型触发器，Oracle 将按照固定的触发顺序运行各种类型的触发器。Oracle 首先引发语句级触发器[statement level trigger]，之后引发行级触发器[row level trigger]。
  • Oracle 在执行完整性约束检查[integrity constraint check]时使用数据的读一致性视图[read-consistent view]，能够获取各类触发器对数据的修改，同时确保触发器不会违背完整性约束。
  • Oracle 能为查询[query]及约束[constraint]提供数据的读一致性视图[read-consistent view]。
  • Oracle 负责管理触发器及触发操作[trigger action]代码内引用的方案对象[schema object]的依赖性[dependency]。
  • 如果触发器对远程数据库表进行修改，Oracle 将采取两步提交[two-phase commit]的方式。
  • 如果一个 SQL 语句将引发多个相同类型触发器，Oracle 运行各触发器的顺序是随机的。即 Oracle 不保证同一语句触发的同类触发器的执行顺序。
113

The Execution Model for Triggers and Integrity Constraint Checking