《Oracle PL/SQL开发指南》学习笔记26——触发器（Triggers）（章节回顾、测试）（第一部分）

触发器是管理数据库的有力工具，其用途有：

1. Control the behavior of DDL statements, such as altering, creating, or renaming objects
2. Control the behavior of DML statements, like INSERT, UPDATE, and DELETE statements
3. Control the sequence of and synchronize calls to triggers

4. Enforce referential integrity, complex business rules, and security policies
5. Control and redirect DML statements when they change data in a view
6. Audit information of system access and behavior by creating transparent logs

触发器简介（Introduction to Triggers）

Review Section
This section has described the following points in the introduction to Oracle Database 12c triggers:
1. Oracle Database 12c supports five types of database triggers: DDL, DML, compound, INSTEAD OF, and system or database event triggers.
2. DDL triggers let you capture events that create, modify, or drop objects.
3. DML triggers let you capture changes in data caused by INSERT, UPDATE, and DELETE statements. You can write these triggers to run as statement- or row-level triggers before or after the insert, update, or delete event.
4. You can sequence the execution of DML triggers by using the FOLLOWS clause.
5. Compound triggers let you capture changes in data from the perspective of four different timing events for INSERT, UPDATE, and DELETE statements.
6. INSTEAD OF triggers work on views typically, and let you engineer the logic to insert, update, or delete data in views that may not otherwise be writable. INSTEAD OF triggers also let you automate adding storage or partitioning clauses.
7. System or database event triggers let you manage the database environment.

 

数据库触发器架构（Database Trigger Architecture）

Review Section
This section has described the following points about the Oracle Database 12c database trigger architecture:
1. Database triggers are like packages because they have a trigger declaration and trigger body.
2. The trigger declarations consist of four parts: a trigger name, a statement, a restriction, and an action.
3. The trigger body is an anonymous block PL/SQL program unit. As an anonymous block, you must implement the execution block. You may implement a declaration and exception block.
4. Database triggers listen for an event and then take action when the event occurs. This is an implementation of the object-oriented analysis and design (OOAD) Observer pattern.
5. Database triggers have their own namespace, which means you can have the same name for a table as you have for a database trigger.
6. Database triggers may become invalid when dependencies in the trigger body are removed.
7. You can use the alter trigger command to recompile a trigger after a dependency of the trigger body has been restored.
DDL, statement-level DML, and system or database triggers have no access to the data they impact (an exception occurs when you alter or drop a column in a DDL trigger).
8.  Row-level DML triggers have access to the data they impact through the new and old pseudo-record structures.
9.  You can override the new and old pseudo-record structure names when you define the database trigger.
10. DML statement-level triggers have before and after statement timing events.
11. DML row-level triggers have before statement, before each row, after each row, and after statement timing events.
12. You can synchronize triggers by using the FOLLOWS clause when you define database triggers.
13. Compound database triggers have a global trigger state and the same four timing events as a DML row-level trigger.

 

数据定义语言触发器（Data Definition Language Triggers）

Review Section
This section has described the following points about the Oracle Database 12c DDL database trigger architecture:
1. DDL triggers fire when you create, change, or remove objects in a database.
2. Oracle Database 12c's event attribute functions provide a readymade set of code you can use in your DDL triggers and system or database event triggers.
3. You can create DDL triggers that work against the whole database, editions, or schemas.
4. You also can limit a DDL trigger to a single PDB instance in Oracle Database 12c's new multitenant architecture.
5. Oracle Database 12c's event attribute functions only return values in the context of a database trigger.
6. You can use the INSTEAD OF CREATE clause when you want to automate a standard partition storage clause every time you create a table.

 

数据操作语言触发器（Data Manipulation Language Triggers）

Review Section
This section has described the following points about the Oracle Database 12c DML database trigger architecture:
1. DML triggers can fire before or after an INSERT, UPDATE, or DELETE statement.
2. DML triggers can be statement-level or row-level triggers.
3. DML triggers can run in the same transaction scope as the INSERT, UPDATE, or DELETE statement, or they can run in an autonomous scope.
4. You can support the MERGE statement by providing a INSERT or UPDATE database trigger.
5. Statement-level DML triggers fire once before and after the INSERT, UPDATE, or DELETE statement.
6. Row-level DML triggers can pass the record structure of any INSERT, UPDATE, or DELETE statement, which allows you to evaluate and change old and new column values.
7. The WHEN clause lets you filter when a trigger fires its trigger body.
8. You can sequence database triggers by using the FOLLOWS clause from Oracle Database 11g forward.
9. Oracle Database 11g forward supports editioning of database triggers.
10. You can use DML triggers to mimic automatic numbering in surrogate key columns.
You do so by calling a sequence into the session memory and then using the next value of the sequence as a primary key value.
11. A single DML trigger can manage an INSERT, UPDATE, or DELETE event, or it can manage two or more events at the same time.