Oracle之Triggers學習與測試_20091229

Oracle Triggers 學習與測試

<>P475

<>P567

目錄

1 Introduction to Triggers

1.1 The events that fire a trigger

1.2 Parts of a Trigger

3 Types of Triggers

3.1 Row Triggers

3.2 Statement Triggers

3.3 BEFORE and AFTER Trigger

3.4 Trigger type Combinations

3.5 INSTEAD OF TRIGGERS

3.6 Triggers on System Events and User Events

4 Trigger Executions

4.1 Enabling Triggers

4.2 Disable Triggers

4.3 Execution Model

4.4 Storage of PL/SQL Triggers

5 Notes

 

1 Introduction to Triggers

 

1.1 The events that fire a trigger

 

1) DML statements that modify data in a table.(table or view)

2) DDL statements.

3) System events such as startup, shutdown, and error messages

4) User events such as logon and logoff

 

1.2 Parts of a Trigger

 

A trigger event or statement

A trigger restriction

A trigger action

 

 

 

3 Types of Triggers

 

3.1 Row Triggers

A row trigger is fired each time the table is affected by the triggering statement. (行觸發器,每影響一行,就觸發一次,如沒有行受影響,則不觸發.如Update語句)

 

3.2 Statement Triggers

A statement triggers is fired once on behalf of the triggering statement, regardless of the number of rows in the table that the triggering statement affects, even no rows affected.(語句觸發器,不管多少行受影響,即使沒用行受影響也會觸發,如Delete語句)

 

3.3 BEFORE and AFTER Trigger

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 a DML 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 a particular table. (前,後觸發器指定Trigger action run before or after the triggering statement. BEFORE and AFTER DML trigger只能定義在表上,而不能在檢視上,但由作業系統引起基表的變化同樣會引發觸發器.相反DDL觸發器只能定義在Database or Schema上,不能在基表上. )

 

3.4 Trigger type Combinations

Before statement trigger/Before row trigger

After statement trigger / after row trigger (Lock rows)

You can have multiple trigger of the same type for the same statement for any given table.(能夠定義多個同型別的觸發器,難道不能定義多個不同型別的?)

 

3.5 INSTEAD OF TRIGGERS

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 INSTAEAD OF triggers because, unlike other types of triggers, Oracle fires the trigger instead of executing the triggering statement.(INSTEAD OF TRIGGERS表示當DML啟動他後，他將以TRIGGER裡的程式碼來代替這個DML動作，也就是DML不會真正的執行，只會啟動INSTEAD TRIGGER，最終執行的是TRIGGER裡面的編碼. 適用的場合是當不能直接更新基表時,透過更改檢視的方式來間接達到目的. 做個測試)

 

3.6 Triggers on System Events and User Events

 

Event Publication uses the public-subscribe mechanism of Oracle 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 Attributes: Each event allows the use of attributes within trigger text. (Username, instance number, database name, schemas object type and name.事件的一些屬性)

 

4 Trigger Executions

 

4.1 Enabling Triggers

 

 

4.2 Disable Triggers

 

 

4.3 Execution Model (執行模型)

 

Oracle uses the following execution model to maintain the proper firing sequence of multiple triggers and constraint checking:

1. Run all BEFORE statement triggers that apply to the statement.

 

2. Loop for each row affected by the SQL statement.

a. Run all BEFORE row triggers that apply to the statement.

b. Lock and change row, and perform. integrity constraint checking. (The lockis not released until the transaction is committed.)

c. Run all AFTER row triggers that apply to the statement.

 

3. Complete deferred integrity constraint checking.

 

4. Run all AFTER statement triggers that apply to the statement.

 

描述了各類觸發器之間的執行順序.

However, although triggers of different types are fired in a specific order, triggers of the same type of the same statement are not guaranteed to fire in any specific order. (不同型別之間有順序,但在同類之間無具體的順序)

An important property of the execution model is that all actions and checks done as a result of a SQL statement must succeed. If an exception is raised within a trigger, and the exception is not explicitly handled, all actions performed as a result of the original SQL statement, including the actions performed by fired triggers, are rolled back. (由一條語句引起的所有觸發器動作組成一個事務)

 

4.4 Storage of PL/SQL Triggers

Oracle stores PL/SQL triggers in compiled form, just like stored procedures.資料庫級的儲存

 

5 Notes

1 Trigger 與 Constraints的比較

慎用觸發器,只有在Constraints不能滿足的情況下才用.

 

2 For abnormal instance shutdown, SHUTDOWN triggers cannot be fired.(異常關閉不會引發觸發器)

 

3 LOGON and LOGOFF triggers can be associated with the database or with a schema.(LOGON and LOGOFF 可以關聯資料庫和Schema)

 

4 觸發器的管理

<之Triggers管理_20091229.doc>>