What Is A Database Trigger? A Database Trigger is a PL/SQL program stored within the database 数据库触发器是存储在数据库中的 PL/SQL 程序• Very similar to a PL/SQL code • Triggers are unique because of they fire as the result of a database event — This may sometimes be referred to as the trigger firing timing point

The following demonstrates the timing of two different database triggersCascading Triggers • As you can see, there can be many triggers associated with different DML events for a single table一个表的不同 DML 事件可以关联多个触发器 - • Just like any other PL/SQL program, a trigger may contain SQL statements to update another table in the database • In this situation, you can have one trigger event fire multiple different trigger events for other objects一个触发器事件触发其他对象的多个不同触发器事件

Trigger Types There are several different types, and even subtypes of triggers. The first and primary distinction between trigger types are: • Triggers are attached to Table DML events • Triggers are attached to View DML events • Triggers are associated with predefined database system events.触发器类型之间的首要区别是 - 触发器与表 DML 事件相关联 - 触发器与视图 DML 事件相关联 - 触发器与预定义的数据库系统事件相关

Trigger Subtypes Within the category of DML event triggers, there are the following subtypes of triggers: • Statement-Level Triggers — These fire once for the entire triggering statement • Row-Level Triggers — These fire once for each row affected by the triggering statement • INSTEAD OF Triggers — Used to execute in place of any update operations on a particular View在 DML 事件触发器类别中，有以下触发器子类型： - 语句级触发器 - 这些触发器对整个触发语句触发一次 - 行级触发器 - 这些触发器对受触发语句影响的每一行触发一次 - 替代触发器 - 用于替代对特定虚拟信息的任何更新操作。

Database Trigger Example • The Trigger Name must be unique • The Trigger Event defines when the trigger will fire relative to the DML event触发器名称必须唯一 - 触发器事件定义触发器相对于 DML 事件的触发时间Database Trigger Information Triggers are unique in their purpose and when they are executed, but they are similar to every other program unit • Share the same space in the PL/SQL cache • Can become invalid if changes are made to dependent objects • Can invoke stored procedures触发器在目的和执行时间上都是独一无二的，但它们与其他程序单元相似 - 在 PL/SQL 缓存中共享相同的空间 - 如果对依赖对象进行更改，触发器可能会失效 - 可以调用存储过程

Using Triggers For Security • DBMS provides standard object security features used to restrict which users may access or the type of access to database objects DBMS 提供标准的对象安全功能，用于限制哪些用户可以访问数据库对象或访问数据库对象的类型

Statement-Level Triggers Statement-Level Triggers fire once for the entire triggering statement • The system doesn’t care how many rows are effected by the DML event语句级触发器对整个触发语句触发一次

The DML Event • The Trigger Declaration allows you to specify when it should fire 触发器声明允许您指定何时触发 — BEFORE — AFTER • It also specifies exactly which DML event should cause the execution还指定了导致执行的 DML 事件 — INSERT — UPDATE — DELETE

The DML Event • Additionally, you can qualify the UPDATE specification to list individual columns within the table — When omitted, any update will fire the Trigger — When included, only updates to those fields will fire the Trigger还可以限定 UPDATE 规范，以列出表中的个别列 - 如果省略，任何更新都会触发触发器 - 如果包含，只有对这些字段的更新才会触发触发器

When To Use Trigger Events The challenge when creating a Trigger is deciding how to use the feature as part of the overall design • BEFORE Trigger Event — Might be good for creating journal entries — Enforcing application security • AFTER Trigger Event — Might be good for logging journal entries for successful operations 创建触发器的时机- 触发事件前 - 可能适合创建日志条目 - 执行应用程序安全 - 触发事件后 - 可能适合记录成功操作的日志条目

Clarifying The Event Within The Implementation When the trigger has been defined for multiple DML events, the logic might require different actions depending upon which specific event fired it • INSERTING • DELETING • UPDATING • UPDATING (’Column1’) — The UPDATING function includes the option to specify the column as a parameterUPDATING函数包括将列指定为参数的选项当为多个 DML 事件定义了触发器时，根据触发的具体事件，逻辑可能需要不同的操作 

The Boolean functions might be referenced within the implementation logic as shown布尔函数使用

、

Using RAISE APPLICATION ERROR() The RAISE APPLICATION ERROR() system-supplied procedure may be invoked within your trigger. The effect of calling this procedure for application errors are: • A user-defined database error number and text is generated • The trigger execution is aborted and the exception is raised • If the exception is not handled and it persists, the transaction is rolled back to the automatic savepoint 可以在触发器中调用系统提供的存储过程 RAISE APPLICATION ERROR()。 调用该存储过程处理应用程序错误的效果如下 - 生成用户定义的数据库错误编号和文本 - 中止触发器的执行并引发异常 - 如果异常未得到处理且持续存在，则将事务回滚到自动保存点

Using RAISE APPLICATION ERROR() The parameters available when calling the RAISE APPLICATION ERROR() procedure are:调用 RAISE APPLICATION ERROR() 存储过程时可用的参数About The Error Stack For you to better understand the Error Stack and the keep error stack parameter, review the code belowRow-Level Triggers Row-Level Triggers fire once for each row within a trigger statement • Indicated by the FOR EACH ROW clause • Must consider the performance implications行级触发器对触发器语句中的每一行触发一次 - 由 FOR EACH ROW 子句表示 - 必须考虑对性能的影响

Row-Level Triggers • Features available to the Statement-Level Trigger are also available to the Row-Level Trigger — RAISE APPLICATION ERROR() — IF INSERTING OR DELETING OR UPDATING • Row-Level Triggers offer additional capabilities — Track BEFORE and AFTER values — “Last Line of Defense”报表级触发器具有的功能也适用于行级触发器 - RAISE APPLICATION ERROR() - 如果正在输入、删除或更新 - 行级触发器提供额外功能 - 跟踪前值和后值 - "最后一道防线"。

Old & New Column Values Since Row-Level triggers execute within the realm of a single row, they can examine old and new column values • Column values may be referenced using what is known as correlation name and the column name由于行级触发器是在单行范围内执行的，因此可以检查新旧列值 - 列值可以使用所谓的相关名称和列名来引用

Reading OLD & NEW Values There are some limitations on when the old or new values may be read读取旧值或新值的限制条件

Correlation Names OLD and NEW are the default correlation names • They may be overridden using the REFERENCING clause • When referenced in the trigger body, they MUST use the preceding colon OLD 和 NEW 是默认的相关名称 - 可使用 REFERENCING 子句覆盖它们 - 在触发器正文中引用时，必须使用前面的冒号

Improve Performance Row-Level triggers permit a conditional expression to be included in the trigger specification • Include a WHEN() clause in the trigger specification to specify a condition • Much better than including in the body行级触发器允许在触发器规范中包含条件表达式 - 在触发器规范中包含 WHEN() 子句以指定条件 - 比在正文中包含条件表达式要好

Employee Salary Check Example This example will only allow an employee salary to exceed £70,000 if he/she is a manager. • This condition cannot be implemented with a simple declarative constraint • Application logic could perform this test before the update is ever processed

Employee Salary Check Test SQL • Attempt to update Smith

• Attempt to update Borg

Employee Journal Example This AFTER INSERT OR UPDATE Row-Level Trigger will record an audit entry of any employee whose salary has been raised above £70,000. • Requires a new table to hold the journal entries • It will use the OLD and NEW Column values • First create new table to hold the journal entries

Budget Event Example This AFTER INSERT OR UPDATE Row-Level Trigger will request additional money for a department’s budget when: • An employee is hired • An employee is given a raise • Budget event setup SQL

INSTEAD OF Triggers Update operations through views are limited to views which meet certain criteria • This is why users or applications may receive errors to DML operations. • When a DML statement is issued against a view, the INSTEAD OF clause is used instead • Use the following syntax to create the INSTEAD OF Trigger通过视图进行的更新操作仅限于符合特定条件的视图 - 这就是用户或应用程序可能会收到 DML 操作错误的原因。 - 当针对视图发布 DML 语句时，可以使用 INSTEAD OF 子句来代替

INSTEAD OF Triggers Scenario Consider the following View• It is illegal to delete from this view • Updates are only allowed under certain circumstances

从该视图中删除内容是非法的 - 只有在特定情况下才允许更新

Instead of Trigger