Oracle觸發器1-介紹

Oracle官方參考：PL/SQL Language Referenc->9 PL/SQL Trigger

Reasons to Use Trigger:

■ Automatically generate calculated column values 
■ Log events
■ Gather statistics on table access
■ Modify table data when DML statements are issued against views
■ Enforce referential integrity when child and parent tables are on different nodes of a distributed database
■ Publish information about database events, user events, and SQL statements to subscribing applications
■ Prevent DML operations on a table after regular business hours
■ Prevent invalid transactions
■ Enforce complex business or referential integrity rules that you cannot define with constraints

本人E比較差，防止誤導別人，也就不一一解釋。

觸發器在資料庫裡以獨立的Object Storage Service，它與預存程序和函數不同的是，預存程序與函數需要使用者顯示調用才執行，而觸發器是由一個事件來啟動運行。即觸發器是當某個事件發生時自動地隱式運行。並且，觸發器不能像預存程序一樣接收參數。ORACLE事件指的是對資料庫的表進行的INSERT、UPDATE及DELETE操作或對視圖進行類似的操作。ORACLE將觸發器的功能擴充到了觸發ORACLE，如資料庫的啟動與關閉等。所以觸發器常用來完成由資料庫的完整性條件約束難以完成的複雜商務規則的約束，或用來監視對資料庫的各種操作，實現審計的功能。

DML觸發器

ORACLE可以在DML語句進行觸發，可以在DML操作前或操作後進行觸發，並且可以對每個行或語句操作上進行觸發。

DDL觸發器

建立DDL觸發器文法和DML觸發器很類似，只是觸發事件不同（create table,alter index,drop trigger等），這種觸發器不是作用於某個表的。

替代觸發器

由於在ORACLE裡，不能直接對由兩個以上的表建立的視圖進行操作。所以給出了替代觸發器。

資料庫事件觸發程序

ORACLE 提供了第三種類型的觸發器，資料庫事件觸發程序也叫系統觸發器。它可以在ORACLE資料庫系統的事件中進行觸發，如ORACLE系統的啟動與關閉等。

      觸發器組成部分

1、 觸發器名稱
2、 觸發語句
3、 觸發器限制
4、 觸發操作

Trigger Design Guidelines:

■ Use triggers to ensure that whenever a specific event occurs, any necessary actions are done (regardless of which user or application issues the triggering statement).
For example, use a trigger to ensure that whenever anyone updates a table, its log file is updated.
■ Do not create triggers that duplicate database features.
For example, do not create a trigger to reject invalid data if you can do the same with constraints.
■ Do not create triggers that depend on the order in which a SQL statement processes rows (which can vary).
For example, do not assign a value to a global package variable in a row trigger if the current value of the variable depends on the row being processed by the row trigger. If a trigger updates global package variables, initialize those variables in a BEFORE statement trigger.
■ Use BEFORE row triggers to modify the row before writing the row data to disk.
■ Use AFTER row triggers to obtain the row ID and use it in operations.An AFTER row trigger fires when the triggering statement results in ORA-2292.

■ If the triggering statement of a BEFORE statement trigger is an UPDATE or DELETEstatement that conflicts with an UPDATE statement that is running, then the
database does a transparent ROLLBACK to SAVEPOINT and restarts the triggering statement. The database can do this many times before the triggering statement
completes successfully. Each time the database restarts the triggering statement, the trigger fires. The ROLLBACK to SAVEPOINT does not undo changes to package
variables that the trigger references. To detect this situation, include a counter variable in the package.
■ Do not create recursive triggers.
For example, do not create an AFTER UPDATE trigger that issues an UPDATE statement on the table on which the trigger is defined.

The trigger fires recursively until it runs out of memory

■ Use DATABASE triggers judiciously. They fire every time any database user initiates a triggering event.
■ If a trigger runs the following statement, the statement returns the owner of the trigger, not the user who is updating the table:
SELECT Username FROM USER_USERS;
■ Only committed triggers fire.
A trigger is committed, implicitly, after the CREATE TRIGGER statement that creates it succeeds.

Therefore, the following statement cannot fire the trigger that it creates:
CREATE OR REPLACE TRIGGER my_trigger
  AFTER CREATE ON DATABASE
BEGIN
  NULL;
END;
/
■ To allow the modular installation of applications that have triggers on the same tables, create multiple triggers of the same type, rather than a single trigger that
runs a sequence of operations.Each trigger sees the changes made by the previously fired triggers. Each trigger can see OLD and NEW values.