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

 

复合触发器（Compound Triggers）

Review Section
This section has described the following points about the Oracle Database 12c compound database trigger architecture:
1. Compound database triggers support a global state that lets you define variables available at any of the four timing points.
2. Compound database triggers support DML events at four timing points: BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, and AFTER STATEMENT.
3. Compound database triggers don't support a global exception block, but each of the timing blocks supports exception blocks.

 

INSTEAD OF Triggers

Review Section
This section has described the following points about the Oracle Database 12c INSTEAD OF database trigger architecture:
1. You can use the INSTEAD OF trigger to intercept INSERT, UPDATE, and DELETE statements and replace those instructions with alternative procedural code. Nonupdatable views generally have INSTEAD OF triggers to accept the output and resolve the issues that make the view nonupdatable. INSTEAD OF triggers are editionable from Oracle Database 11g Release 2 forward.
2. The INSTEAD OF trigger intercepts INSERT, UPDATE, and DELETE statements against nonupdatable views and enables writing changes to the underlying tables.
3. DML event functions support taking different actions based on the firing event.

 

系统和数据库事件触发器（System and Database Event Triggers）

Review Section
This section has described the following points about the Oracle Database 12c system and
database event triggers:
1. System and database event triggers let you capture events like logging on or off the system, or connecting to different schemas.
2. System and database event triggers are best suited to log activities rather than raise exceptions.

 

触发器限制（Trigger Restrictions）

Review Section
This section has described the following points about Oracle Database 12c trigger restrictions:
1. A trigger body can be no longer than 32,760 bytes, which is the size of a LONG data type.
2. Nonsystem trigger bodies can't contain DDL statements.
3. LONG and LONG RAW data types are legacy components, and you should avoid implementing solutions with them.
4. Mutating tables are tables undergoing change, and you can't query or modify data while they're changing.
5. You can encounter problems with DDL statements when system triggers fail to compile.

 

Mastery Check

The mastery check is a series of true-or-false and multiple-choice questions that let you confirm
how well you understand the material in the chapter. You may check Appendix I for answers to
these questions.

True or False:
1. Statement-level database triggers can change the new pseudo-record column values with the INSERT and UPDATE statements.
False. Statement-level triggers can’t access the pseudo-records because they run once per statement, not for each row touched by the transaction.
2. Oracle Database 12c supports triggers on Data Definition Language (DDL) statements.
True. Oracle Database 12c supports DDL statement triggers.
3. Row-level database triggers can change the new pseudo-record column values with the INSERT and UPDATE statements.
True. Row-level triggers can access the pseudo-records because they run once for each row touched by an INSERT or UPDATE statement.
4. Compound database triggers have four timing points.
True. Oracle Database 12c’s compound database triggers have four timing points: BEFORE STATEMENT, BEFORE EACH ROW, AFTER EACH ROW, and AFTER STATEMENT.

5. Compound database triggers can implement a global exception handler.
False. Compound database triggers can’t implement a global exception handler. They can only implement exception handlers within the timing event blocks.
6. Event attribute functions are designed for use in triggers and non-trigger PL/SQL program units.
False. Event attribute functions are designed for exclusive use in database triggers, and
they have no context outside of database triggers.
7. You can implement event attribute functions in system event triggers.
True. You can implement event attribute functions inside DDL triggers or system event triggers.
8. You can define a single DML trigger that fires for INSERT, UPDATE, or DELETE statements on the same table.
True. Oracle supports triggers that work with an INSERT, UPDATE, or DELETE statement,
or with two or three of the statements through the OR inclusion operators.
9. You can define a DDL trigger for a MERGE statement.
False. The Oracle MERGE statement is a combination of an INSERT statement and an
UPDATE statement. You use an INSERT OR UPDATE trigger to capture changes from a MERGE statement.
10. It’s possible to define an autonomous trigger body.
True. You can define an autonomous trigger body by using a precompiler directive, PRAGMA AUTONOMOUS_TRANSACTION.

Multiple Choice:
11. Which of the following types of database triggers work in an Oracle database? (Multiple answers possible)
A. DDL triggers
B. TCL triggers
C. DML triggers
D. INSTEAD OF triggers
E. Compound triggers
A, C, D, and E are correct. Oracle supports DDL, DML, INSTEAD OF, and compound
triggers. There is no such thing as a TCL trigger.
12. Which of the following types of database triggers work with a nonupdatable view in an Oracle database? (Multiple answers possible)
A. DDL triggers
B. DML triggers
C. System event triggers
D. TCL triggers
E. INSTEAD OF triggers
E is correct. Only the INSTEAD OF trigger works with nonupdatable views.

13. You have new and old pseudo-record structures for which triggers in an Oracle database?
(Multiple answers possible)
A. DML statement-level triggers
B. DDL row-level triggers
C. DDL statement-level triggers
D. DML row-level triggers
E. Compound triggers
D is correct. The Oracle database supports the new and old pseudo-record structures only in a DML row-level trigger.
14. Which of the following are event functions? (Multiple answers possible)
A. A MERGING function
B. An INSERTING function
C. An UPDATING function
D. A DELETING function
E. All of the above
B, C, and D are correct. The INSERTING, UPDATING, and DELETING event functions
are the only event functions in Oracle Database 12c.
15. Oracle requires what syntax to access new column values from an INSERT or UPDATE statement in the code block? (Multiple answers possible)
A. new.column_name
B. :new.column_name
C. old.column_name
D. :old.column_name
E. None of the above
B is correct. Oracle can only access a new pseudo-record by using a bind variable, :new,
from inside the trigger body. The reason is that the trigger body acts like a subshell and
can gain access to the new pseudo-record structure’s scope only by referring outside of its
scope. The prefacing colon (signifying a bind variable) lets the program refer to the DML
statement’s scope.