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

 

After update of parts_on_hand on inventory --- Trigger Statement

When ( new.parts_on_hand < new.reorder_point ) --- Trigger Restriction

For each row                                                          --- Action

Declare

       Xxx

End;

 

 

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

 

单个:

Alter trigger reorder ENABLE.

 

表上所有的

ALTER TABLE inventory ENABLE ALL TRIGGERS;

 

4.2 Disable Triggers

 

单个:

Alter trigger reorder DISABLE.

 

表上所有的

ALTER TABLE inventory DISABLE ALL 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>>

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10248702/viewspace-623918/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/10248702/viewspace-623918/