oracle触发器高级教程
【轉自】http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/statements_7004.htm#i2153503
?
?
CREATE TRIGGER
Purpose
Use the?CREATE?TRIGGER?statement to create and enable a?database trigger, which is:
-
A stored PL/SQL block associated with a table, a schema, or the database or
-
An anonymous PL/SQL block or a call to a procedure implemented in PL/SQL or Java
Oracle Database automatically executes a trigger when specified conditions occur.
When you create a trigger, the database enables it automatically. You can subsequently disable and enable a trigger with the?DISABLE?and?ENABLE?clause of the?ALTER?TRIGGER?or?ALTER?TABLE?statement.
See Also:
-
Oracle Database Concepts?for a description of the various types of triggers and?Oracle Database Application Developer's Guide - Fundamentals?for more information on how to design triggers
-
ALTER TRIGGER?and?ALTER TABLE?for information on enabling, disabling, and compiling triggers, and?DROP TRIGGER?for information on dropping a trigger
Prerequisites
Before a trigger can be created, the user?SYS?must run a SQL script commonly called?DBMSSTDX.SQL. The exact name and location of this script depend on your operating system.
-
To create a trigger in your own schema on a table in your own schema or on your own schema (SCHEMA), you must have the?CREATE?TRIGGER?system privilege.
-
To create a trigger in any schema on a table in any schema, or on another user's schema (schema.SCHEMA), you must have the?CREATE?ANY?TRIGGER?system privilege.
-
In addition to the preceding privileges, to create a trigger on?DATABASE, you must have the?ADMINISTER?DATABASE?TRIGGER?system privilege.
If the trigger issues SQL statements or calls procedures or functions, then the owner of the trigger must have the privileges necessary to perform these operations. These privileges must be granted directly to the owner rather than acquired through roles.
Syntax
create_trigger::=
DML_event_clause?::=
referencing_clause::=
Semantics
OR REPLACE
Specify?OR?REPLACE?to re-create the trigger if it already exists. Use this clause to change the definition of an existing trigger without first dropping it.
schema
Specify the schema to contain the trigger. If you omit?schema, then Oracle Database creates the trigger in your own schema.
trigger
Specify the name of the trigger to be created.
If a trigger produces compilation errors, then it is still created, but it fails on execution. This means it effectively blocks all triggering DML statements until it is disabled, replaced by a version without compilation errors, or dropped. You can see the associated compiler error messages with the SQL*Plus command?SHOW?ERRORS.
Note:
If you create a trigger on a base table of a materialized view, then you must ensure that the trigger does not fire during a refresh of the materialized view. During refresh, the?DBMS_MVIEW?procedure?I_AM_A_REFRESH?returns?TRUE.BEFORE
Specify?BEFORE?to cause the database to fire the trigger before executing the triggering event. For row triggers, the trigger is fired before each affected row is changed.
Restrictions on BEFORE Triggers?BEFORE?triggers are subject to the following restrictions:
-
You cannot specify a?BEFORE?trigger on a view or an object view.
-
You can write to the :NEW?value but not to the :OLD?value.
AFTER
Specify?AFTER?to cause the database to fire the trigger after executing the triggering event. For row triggers, the trigger is fired after each affected row is changed.
Restrictions on AFTER Triggers?AFTER?triggers are subject to the following restrictions:
-
You cannot specify an?AFTER?trigger on a view or an object view.
-
You cannot write either the :OLD?or the :NEW?value.
Note:
When you create a materialized view log for a table, Oracle Database implicitly creates an?AFTER?ROW?trigger on the table. This trigger inserts a row into the materialized view log whenever an?INSERT,?UPDATE, or?DELETE?statement modifies data in the master table. You cannot control the order in which multiple row triggers fire. Therefore, you should not write triggers intended to affect the content of the materialized view.See Also:
CREATE MATERIALIZED VIEW LOG?for more information on materialized view logsINSTEAD OF
Specify?INSTEAD?OF?to cause Oracle Database to fire the trigger instead of executing the triggering event.?INSTEAD?OF?triggers are valid for DML events on views. They are not valid for DDL or database events.
If a view is inherently updatable and has?INSTEAD?OF?triggers, then the triggers take preference. In other words, the database fires the triggers instead of performing DML on the view.
If the view belongs to a hierarchy, then the trigger is not inherited by subviews.
Note:
Oracle Database fine-grained access control lets you define row-level security policies on views. These policies enforce specified rules in response to DML operations. If anINSTEAD?OF?trigger is also defined on the view, then the database will not enforce the row-level security policies, because the database fires the?INSTEAD?OF?trigger instead of executing the DML on the view.INSTEAD OF Triggers?
-
INSTEAD?OF?triggers are valid only for views. You cannot specify an?INSTEAD?OF?trigger on a table.
-
You can read both the :OLD?and the :NEW?value, but you cannot write either the :OLD?or the :NEW?value.
Note:
You can create multiple triggers of the same type (BEFORE,?AFTER, or?INSTEAD?OF) that fire for the same statement on the same table. The order in which Oracle Database fires these triggers is indeterminate. If your application requires that one trigger be fired before another of the same type for the same statement, then combine these triggers into a single trigger whose trigger action performs the trigger actions of the original triggers in the appropriate order.See Also:
"Creating an INSTEAD OF Trigger: Example"DML_event_clause
The?DML_event_clause?lets you specify one of three DML statements that can cause the trigger to fire. Oracle Database fires the trigger in the existing user transaction.
You cannot specify the?MERGE?keyword in the?DML_event_clause. If you want a trigger to fire in relation to a?MERGE?operation, you must create triggers on the?INSERT?and?UPDATE?operations to which the?MERGE?operation decomposes.
See Also:
"Creating a DML Trigger: Examples"DELETE
Specify?DELETE?if you want the database to fire the trigger whenever a?DELETE?statement removes a row from the table or removes an element from a nested table.
INSERT
Specify?INSERT?if you want the database to fire the trigger whenever an?INSERT?statement adds a row to a table or adds an element to a nested table.
UPDATE
Specify?UPDATE?if you want the database to fire the trigger whenever an?UPDATE?statement changes a value in one of the columns specified after?OF. If you omit?OF, then the database fires the trigger whenever an?UPDATEstatement changes a value in any column of the table or nested table.
For an?UPDATE?trigger, you can specify object type, varray, and?REF?columns after?OF?to indicate that the trigger should be fired whenever an?UPDATE?statement changes a value in one of the columns. However, you cannot change the values of these columns in the body of the trigger itself.
Note:
Using OCI functions or the?DBMS_LOB?package to update LOB values or LOB attributes of object columns does not cause Oracle Database to fire triggers defined on the table containing the columns or the attributes.Restrictions on Triggers on UPDATE Operations?The?UPDATE?clause is subject to the following restrictions:
-
You cannot specify?UPDATE?OF?for an?INSTEAD?OF?trigger. Oracle Database fires?INSTEAD?OF?triggers whenever an?UPDATE?changes a value in any column of the view.
-
You cannot specify a nested table or LOB column in the?UPDATE?OF?clause.
See Also:
AS?subquery?clause of?CREATE VIEW?for a list of constructs that prevent inserts, updates, or deletes on a viewPerforming DML operations directly on nested table columns does not cause Oracle Database to fire triggers defined on the table containing the nested table column.
ddl_event
Specify one or more types of DDL statements that can cause the trigger to fire. You can create triggers for these events on?DATABASE?or?SCHEMA?unless otherwise noted. You can create?BEFORE?and?AFTER?triggers for these events. Oracle Database fires the trigger in the existing user transaction.
Restriction on Triggers on DDL Events?You cannot specify as a triggering event any DDL operation performed through a PL/SQL procedure.
See Also:
"Creating a DDL Trigger: Example"The following?ddl_event?values are valid:
ALTER?Specify?ALTER?to fire the trigger whenever an?ALTER?statement modifies a database object in the data dictionary.
Restriction on Triggers on ALTER Operations?The trigger will not be fired by an?ALTER?DATABASE?statement.
ANALYZE?Specify?ANALYZE?to fire the trigger whenever the database collects or deletes statistics or validates the structure of a database object.
See Also:
ANALYZE?for information on various ways of collecting statisticsASSOCIATE STATISTICS?Specify?ASSOCIATE?STATISTICS?to fire the trigger whenever the database associates a statistics type with a database object.
AUDIT?Specify?AUDIT?to fire the trigger whenever the database tracks the occurrence of a SQL statement or tracks operations on a schema object.
COMMENT?Specify?COMMENT?to fire the trigger whenever a comment on a database object is added to the data dictionary.
CREATE?Specify?CREATE?to fire the trigger whenever a?CREATE?statement adds a new database object to the data dictionary.
Restriction on Triggers on CREATE Operations?The trigger will not be fired by a?CREATE?DATABASE?or?CREATE?CONTROLFILE?statement.
DISASSOCIATE STATISTICS?Specify?DISASSOCIATE?STATISTICS?to fire the trigger whenever the database disassociates a statistics type from a database object.
DROP?Specify?DROP?to fire the trigger whenever a?DROP?statement removes a database object from the data dictionary.
GRANT?Specify?GRANT?to fire the trigger whenever a user grants system privileges or roles or object privileges to another user or to a role.
NOAUDIT?Specify?NOAUDIT?to fire the trigger whenever a?NOAUDIT?statement instructs the database to stop tracking a SQL statement or operations on a schema object.
RENAME?Specify?RENAME?to fire the trigger whenever a?RENAME?statement changes the name of a database object.
REVOKE?Specify?REVOKE?to fire the trigger whenever a?REVOKE?statement removes system privileges or roles or object privileges from a user or role.
TRUNCATE?Specify?TRUNCATE?to fire the trigger whenever a?TRUNCATE?statement removes the rows from a table or cluster and resets its storage characteristics.
DDL?Specify?DDL?to fire the trigger whenever any of the preceding DDL statements is issued.
database_event
Specify one or more particular states of the database that can cause the trigger to fire. You can create triggers for these events on?DATABASE?or?SCHEMA?unless otherwise noted. For each of these triggering events, Oracle Database opens an autonomous transaction scope, fires the trigger, and commits any separate transaction (regardless of any existing user transaction).
See Also:
"Creating a Database Event Trigger: Example"SERVERERROR??Specify?SERVERERROR?to fire the trigger whenever a server error message is logged.
The following errors do not cause a?SERVERERROR?trigger to fire:
-
ORA-01403: no data found
-
ORA-01422: exact fetch returns more than requested number of rows
-
ORA-01423: error encountered while checking for extra rows in exact fetch
-
ORA-01034: ORACLE not available
-
ORA-04030: out of process memory when trying to allocate?string?bytes (string,?string)
LOGON?Specify?LOGON?to fire the trigger whenever a client application logs onto the database.
LOGOFF??Specify?LOGOFF?to fire the trigger whenever a client application logs off the database.
STARTUP?Specify?STARTUP?to fire the trigger whenever the database is opened.
SHUTDOWN?Specify?SHUTDOWN?to fire the trigger whenever an instance of the database is shut down.
SUSPEND?Specify?SUSPEND?to fire the trigger whenever a server error causes a transaction to be suspended.
DB_ROLE_CHANGE?In a Data Guard configuration, specify?DB_ROLE_CHANGE?to fire the trigger whenever a role change occurs from standby to primary or from primary to standby.
Notes:
-
Only?AFTER?triggers are relevant for?LOGON,?STARTUP,?SERVERERROR,?SUSPEND, and?DB_ROLE_CHANGE.
-
Only?BEFORE?triggers are relevant for?LOGOFF?and?SHUTDOWN.
-
AFTER?STARTUP?and?BEFORE?SHUTDOWN?triggers apply only to?DATABASE.
See Also:
PL/SQL User's Guide and Reference?for more information on autonomous transaction scopeON?table?|?view
The?ON?clause lets you determine the database object on which the trigger is to be created. Specify the?schema?and?table?or?view?name of one of the following on which the trigger is to be created:
-
Table or view
-
Object table or object view
-
A column of nested-table type
If you omit?schema, then Oracle Database assumes the table is in your own schema. You can create triggers on index-organized tables.
Restriction on Schema?You cannot create a trigger on a table in the schema?SYS.
NESTED TABLE Clause
Specify the?nested_table_column?of a view upon which the trigger is being defined. Such a trigger will fire only if the DML operates on the elements of the nested table.
Restriction on Triggers on Nested Tables?You can specify?NESTED?TABLE?only for?INSTEAD?OF?triggers.
DATABASE
Specify?DATABASE?to define the trigger on the entire database. The trigger fires whenever any database user initiates the triggering event.
SCHEMA
Specify?SCHEMA?to define the trigger on the current schema. The trigger fires whenever any user connected as?schema?initiates the triggering event.
See Also:
"Creating a SCHEMA Trigger: Example"referencing_clause
The?referencing_clause?lets you specify correlation names. You can use correlation names in the PL/SQL block and?WHEN?condition of a row trigger to refer specifically to old and new values of the current row. The default correlation names are?OLD?and?NEW. If your row trigger is associated with a table named?OLD?or?NEW, use this clause to specify different correlation names to avoid confusion between the table name and the correlation name.
-
If the trigger is defined on a nested table, then?OLD?and?NEW?refer to the row of the nested table, and?PARENT?refers to the current row of the parent table.
-
If the trigger is defined on an object table or view, then?OLD?and?NEW?refer to object instances.
Restriction on the?referencing_clause?The?referencing_clause?is not valid with?INSTEAD?OF?triggers on?CREATE?DDL events.
FOR EACH ROW
Specify?FOR?EACH?ROW?to designate the trigger as a row trigger. Oracle Database fires a row trigger once for each row that is affected by the triggering statement and meets the optional trigger constraint defined in theWHEN?condition.
Except for?INSTEAD?OF?triggers, if you omit this clause, then the trigger is a statement trigger. Oracle Database fires a statement trigger only once when the triggering statement is issued if the optional trigger constraint is met.
INSTEAD?OF?trigger statements are implicitly activated for each row.
Restriction on Row Triggers?This clause is valid only for DML event triggers, not for DDL or database event triggers.
WHEN Clause
Specify the trigger condition, which is a SQL condition that must be satisfied for the database to fire the trigger. See the syntax description of?condition?in?Chapter 7, "Conditions". This condition must contain correlation names and cannot contain a query.
The?NEW?and?OLD?keywords, when specified in the?WHEN?clause, are not considered bind variables, so are not preceded by a colon (:). However, you must precede?NEW?and?OLD?with a colon in all references other than theWHEN?clause.
See Also:
"Calling a Procedure in a Trigger Body: Example"Restrictions on Trigger Conditions?Trigger conditions are subject to the following restrictions:
-
If you specify this clause for a DML event trigger, then you must also specify?FOR?EACH?ROW. Oracle Database evaluates this condition for each row affected by the triggering statement.
-
You cannot specify trigger conditions for?INSTEAD?OF?trigger statements.
-
You can reference object columns or their attributes, or varray, nested table, or LOB columns. You cannot invoke PL/SQL functions or methods in the trigger condition.
pl/sql_block
Specify the PL/SQL block that Oracle Database executes to fire the trigger.
The PL/SQL block of a database trigger can contain one of a series of built-in functions in the?SYS?schema designed solely to extract system event attributes. These functions can be used?only?in the PL/SQL block of a database trigger.
Restrictions on Trigger Implementation?The implementation of a trigger is subject to the following restrictions:
-
The PL/SQL block of a trigger cannot contain transaction control SQL statements (COMMIT,?ROLLBACK,?SAVEPOINT, and?SET?CONSTRAINT) if the block is executed within the same transaction.
-
You can reference and use LOB columns in the trigger action inside the PL/SQL block. You can modify the :NEW?values but not the :OLD?values of LOB columns within the trigger action.
See Also:
-
PL/SQL User's Guide and Reference?for information on PL/SQL, including how to write PL/SQL blocks
-
Oracle Database Application Developer's Guide - Fundamentals?for information on these functions
-
"Calling a Procedure in a Trigger Body: Example"
call_procedure_statement
The?call_procedure_statement?lets you call a stored procedure rather than specifying the trigger code inline as a PL/SQL block. The syntax of this statement is the same as that for?CALL, with the following exceptions:
-
You cannot specify the?INTO?clause of?CALL, because it applies only to functions.
-
You cannot specify bind variables in?expr.
-
To reference columns of tables on which the trigger is being defined, you must specify :NEW?and :OLD.
See Also:
"Calling a Procedure in a Trigger Body: Example"Examples
Creating a DML Trigger: Examples?This example shows the basic syntax for a?BEFORE?statement trigger. You would write such a trigger to place restrictions on DML statements issued on a table, for example, when such statements could be issued.
CREATE TRIGGER schema.trigger_name BEFORE DELETE OR INSERT OR UPDATE ON schema.table_name pl/sql_blockOracle Database fires such a trigger whenever a DML statement affects the table. This trigger is a?BEFORE?statement trigger, so the database fires it once before executing the triggering statement.
The next example shows a partial?BEFORE?row trigger. The PL/SQL block might specify, for example, that an employee's salary must fall within the established salary range for the employee's job:
CREATE TRIGGER hr.salary_checkBEFORE INSERT OR UPDATE OF salary, job_id ON hr.employeesFOR EACH ROWWHEN (new.job_id <> 'AD_VP')pl/sql_blockOracle Database fires this trigger whenever one of the following statements is issued:
-
An?INSERT?statement that adds rows to the?employees?table
-
An?UPDATE?statement that changes values of the?salary?or?job_id?columns of the?employees?table
salary_check?is a?BEFORE?row trigger, so the database fires it before changing each row that is updated by the?UPDATE?statement or before adding each row that is inserted by the?INSERT?statement.
salary_check?has a trigger condition that prevents it from checking the salary of the administrative vice president (AD_VP).
Creating a DDL Trigger: Example?This example creates an?AFTER?statement trigger on any DDL statement?CREATE. Such a trigger can be used to audit the creation of new data dictionary objects in your schema.
CREATE TRIGGER audit_db_object AFTER CREATEON SCHEMApl/sql_blockCalling a Procedure in a Trigger Body: Example?You could create the?salary_check?trigger described in the preceding example by calling a procedure instead of providing the trigger body in a PL/SQL block. Assume you have defined a procedure?check_sal?in the?hrschema, which verifies that an employee's salary is in an appropriate range. Then you could create the trigger?salary_check?as follows:
CREATE TRIGGER salary_checkBEFORE INSERT OR UPDATE OF salary, job_id ON employeesFOR EACH ROWWHEN (new.job_id <> 'AD_VP')CALL check_sal(:new.job_id, :new.salary, :new.last_name)The procedure?check_sal?could be implemented in PL/SQL, C, or Java. Also, you can specify :OLD?values in the?CALL?clause instead of :NEW?values.
Creating a Database Event Trigger: Example?This example shows the basic syntax for a trigger to log all errors. The hypothetical PL/SQL block does some special processing for a particular error (invalid logon, error number 1017). This trigger is an?AFTERstatement trigger, so it is fired after an unsuccessful statement execution, such as unsuccessful logon.
CREATE TRIGGER log_errors AFTER SERVERERROR ON DATABASE BEGIN IF (IS_SERVERERROR (1017)) THEN <special processing of logon error> ELSE <log error number> END IF; END;Creating an INSTEAD OF Trigger: Example?In this example, an?oe.order_info?view is created to display information about customers and their orders:
CREATE VIEW order_info ASSELECT c.customer_id, c.cust_last_name, c.cust_first_name,o.order_id, o.order_date, o.order_statusFROM customers c, orders o WHERE c.customer_id = o.customer_id;Normally this view would not be updatable, because the primary key of the?orders?table (order_id) is not unique in the result set of the join view. To make this view updatable, create an?INSTEAD?OF?trigger on the view to process?INSERT?statements directed to the view. The PL/SQL trigger implementation is shown in italics.
CREATE OR REPLACE TRIGGER order_info_insertINSTEAD OF INSERT ON order_info DECLARE duplicate_info EXCEPTION; PRAGMA EXCEPTION_INIT (duplicate_info, -00001); BEGIN INSERT INTO customers (customer_id, cust_last_name, cust_first_name) VALUES ( :new.customer_id, :new.cust_last_name, :new.cust_first_name); INSERT INTO orders (order_id, order_date, customer_id) VALUES ( :new.order_id, :new.order_date, :new.customer_id); EXCEPTION WHEN duplicate_info THEN RAISE_APPLICATION_ERROR ( num=> -20107, msg=> 'Duplicate customer or order ID'); END order_info_insert; /You can now insert into both base tables through the view (as long as all?NOT?NULL?columns receive values):
INSERT INTO order_info VALUES(999, 'Smith', 'John', 2500, '13-MAR-2001', 0);Creating a SCHEMA Trigger: Example?The following example creates a?BEFORE?statement trigger on the sample schema?hr. When a user connected as?hr?attempts to drop a database object, the database fires the trigger before dropping the object:
CREATE OR REPLACE TRIGGER drop_trigger BEFORE DROP ON hr.SCHEMA BEGINRAISE_APPLICATION_ERROR (num => -20000,msg => 'Cannot drop object');END; / ############################################## ############################################## ####################另 一 篇#################### ############################################## ############################################## 【轉自】http://heisetoufa.javaeye.com/blog/367314oracle 觸發器的種類和觸發事件,DML觸發器,DDL事件觸發器,替代觸發器,查看觸發器,
關鍵字: oracle 觸發器 種類 觸發 事件 dml ddl 事件 替代 查看觸發器的種類和觸發事件?觸發器必須由事件才能觸發。觸發器的觸發事件分可為3類，分別是DML事件、DDL事件和數據庫事件。?
每類事件包含若干個事件，如下所示。數據庫的事件是具體的，在創建觸發器時要指明觸發的事件。?
種? 類 關 鍵 字 含??? 義?
Sql代碼?
觸發器的類型可劃分為4種：數據操縱語言(DML)觸發器、替代(INSTEAD OF)觸發器、數據定義語言(DDL)觸發器和數據庫事件觸發器。
各類觸發器的作用如下所示。?
Sql代碼?
DML觸發器的要點?
DML觸發器是定義在表上的觸發器，由DML事件引發。編寫DML觸發器的要素是：?
* 確定觸發的表，即在其上定義觸發器的表。?
* 確定觸發的事件，DML觸發器的觸發事件有INSERT、UPDATE和DELETE三種，說明見下。?
* 確定觸發時間。觸發的時間有BEFORE和AFTER兩種，分別表示觸發動作發生在DML語句執行之前和語句執行之后。?
* 確定觸發級別，有語句級觸發器和行級觸發器兩種。語句級觸發器表示SQL語句只觸發一次觸發器，行級觸發器表示SQL語句影響的每一行都要觸發一次。?
由于在同一個表上可以定義多個DML觸發器，因此觸發器本身和引發觸發器的SQL語句在執行的順序上有先后的關系。它們的順序是：?
* 如果存在語句級BEFORE觸發器，則先執行一次語句級BEFORE觸發器。?
* 在SQL語句的執行過程中，如果存在行級BEFORE觸發器，則SQL語句在對每一行操作之前，都要先執行一次行級BEFORE觸發器，然后才對行進行操作。如果存在行級AFTER觸發器，則SQL語句在對每一行操作之后，都要再執行一次行級AFTER觸發器。?
* 如果存在語句級AFTER觸發器，則在SQL語句執行完畢后，要最后執行一次語句級AFTER觸發器。?
DML觸發器還有一些具體的問題，說明如下：?
* 如果有多個觸發器被定義成為相同時間、相同事件觸發，且最后定義的觸發器是有效的，則最后定義的觸發器被觸發，其他觸發器不執行。?
* 一個觸發器可由多個不同的DML操作觸發。在觸發器中，可用INSERTING、DELETING、UPDATING謂詞來區別不同的DML操作。這些謂詞可以在IF分支條件語句中作為判斷條件來使用。?
* 在行級觸發器中，用:new 和:old(稱為偽記錄)來訪問數據變更前后的值。但要注意，INSERT語句插入一條新記錄，所以沒有:old記錄，而DELETE語句刪除掉一條已經存在的記錄，所以沒有:new記錄。UPDATE語句既有:old記錄，也有:new記錄，分別代表修改前后的記錄。引用具體的某一列的值的方法是：?
???????ld.字段名或:new.字段名?
* 觸發器體內禁止使用COMMIT、ROLLBACK、SAVEPOINT語句，也禁止直接或間接地調用含有上述語句的存儲過程。?
定義一個觸發器時要考慮上述多種情況，并根據具體的需要來決定觸發器的種類。?
DML觸發器的創建?
創建DML觸發器需要CREATE TRIGGER系統權限。創建DML觸發器的語法如下：?
CREATE [OR REPLACE] TRIGGER 觸發器名?
{BEFORE|AFTER|INSTEAD OF} 觸發事件1 [OR 觸發事件2...]?
ON 表名?
WHEN 觸發條件?
[FOR EACH ROW]?
DECLARE?
聲明部分?
BEGIN?
主體部分?
END;?
其中：?
OR REPLACE：表示如果存在同名觸發器，則覆蓋原有同名觸發器。?
BEFORE、AFTER和INSTEAD OF：說明觸發器的類型。?
WHEN 觸發條件：表示當該條件滿足時，觸發器才能執行。?
觸發事件：指INSERT、DELETE或UPDATE事件，事件可以并行出現，中間用OR連接。?
對于UPDATE事件，還可以用以下形式表示對某些列的修改會引起觸發器的動作：?
UPDATE OF 列名1，列名2...?
ON 表名：表示為哪一個表創建觸發器。?
FOR EACH ROW：表示觸發器為行級觸發器，省略則為語句級觸發器。?
觸發器的創建者或具有DROP ANY TIRGGER系統權限的人才能刪除觸發器。刪除觸發器的語法如下：?
DROP TIRGGER 觸發器名?
可以通過命令設置觸發器的可用狀態，使其暫時關閉或重新打開，即當觸發器暫時不用時，可以將其置成無效狀態，在使用時重新打開。該命令語法如下：?
ALTER TRIGGER 觸發器名 {DISABLE|ENABLE}?
其中，DISABLE表示使觸發器失效，ENABLE表示使觸發器生效。?
同存儲過程類似，觸發器可以用SHOW ERRORS 檢查編譯錯誤。?
行級觸發器的應用?
在行級觸發器中，SQL語句影響的每一行都會觸發一次觸發器，所以行級觸發器往往用在對表的每一行的操作進行控制的場合。若在觸發器定義中出現FOR EACH ROW子句，則為語句級觸發器。?
【訓練1】? 創建包含插入、刪除、修改多種觸發事件的觸發器DML_LOG，對EMP表的操作進行記錄。用INSERTING、DELETING、UPDATING謂詞來區別不同的DML操作。?
在創建觸發器之前，需要先創建事件記錄表LOGS，該表用來對操作進行記錄。該表的字段含義解釋如下：?
LOG_ID：操作記錄的編號，數值型，它是該表的主鍵，由序列自動生成。?
LOG_TABLE：進行操作的表名，字符型，非空，該表設計成可以由多個觸發器共享使用。比如我們可以為dept表創建類似的觸發器，同樣將操作記錄到該表。?
LOG_DML：操作的動作，即INSERT、DELETE或UPDATE三種之一。?
LOG_KEY_ID：操作時表的主鍵值，數值型。之所以記錄表的主鍵，是因為主鍵是表的記錄的惟一標識，可以識別是對哪一條記錄進行了操作。對于emp表，主鍵是empno。?
LOG_DATE：操作的日期，日期型，取當前的系統時間。?
LOG_USER：操作者，字符型，取當時的操作者賬戶名。比如登錄SCOTT賬戶進行操作，在該字段中，記錄賬戶名為SCOTT。?
步驟1：在SQL*Plus中登錄STUDENT賬戶，創建如下的記錄表LOGS：?
Sql代碼?
執行結果：?
Sql代碼?
步驟2：創建一個LOGS表的主鍵序列LOGS_ID_SEQ：?
Sql代碼?
執行結果：?
Sql代碼?
步驟3：創建和編譯以下觸發器：?
Sql代碼?
執行結果：?
觸發器已創建?
步驟4：在EMP表中插入記錄：?
Sql代碼?
執行結果：?
Sql代碼?
步驟5：檢查LOGS表中記錄的信息：?
Sql代碼?
執行結果為：?
???Sql代碼?
說明：本例中在emp表上創建了一個由INSERT或DELETE或UPDATE事件觸發的行級觸發器，觸發器的名稱是LOG_EMP。對于不同的操作，記錄的內容不同。本例中只插入了一條記錄，如果用一條不帶WHERE條件的UPDATE語句來修改所有雇員的工資，則將逐行觸發觸發器。?
INSERT、DELETE和UPDATE都能引發觸發器動作，在分支語句中使用INSERTING、DELETING和UPDATING來區別是由哪種操作引發的觸發器動作。?
在本例的插入動作中，LOG_ID字段由序列LOG_ID_SQU自動填充為1；LOGS表LOG_KEY_ID字段記錄的是新插入記錄的主鍵8001；LOD_DML字段記錄的是插入動作INSERT；LOG_TABLE字段記錄當前表名EMP；LOG_DATE字段記錄插入的時間04年3月1日；LOG_USER字段記錄插入者STUDENT。?
【練習1】修改、刪除剛剛插入的雇員記錄，提交后檢查LOGS表的結果。?
【練習2】為DEPT表創建同樣的觸發器，使用LOGS表進行記錄，并檢驗結果。?
【訓練2】? 創建一個行級觸發器LOG_SAL，記錄對職務為CLERK的雇員工資的修改，且當修改幅度超過200時才進行記錄。用WHEN條件限定觸發器。?
在創建觸發器之前，需要先創建事件記錄表LOGERR，該表用來對操作進行記錄。該表的字段含義解釋如下：?
NUM：數值型，用于記錄序號。?
MESSAGE：字符型，用于記錄錯誤信息。?
步驟1：在SQL*Plus中登錄STUDENT賬戶，創建如下的記錄表LOGERR：?
Sql代碼?
執行結果：?
Sql代碼?
步驟2：創建和編譯以下觸發器：?
Sql代碼?
執行結果：?
Sql代碼?
步驟3：在EMP表中更新記錄：?
Sql代碼?
執行結果：?
Sql代碼?
步驟4：檢查LOGSAL表中記錄的信息：?
Sql代碼?
執行結果為：
??????Sql代碼?
? 說明：本例中，在emp表的sal列上創建了一個由UPDATE事件觸發的行級觸發器，觸發器的名稱是LOG_SAL。該觸發器由WHEN語句限定，只有當被修改工資的雇員職務為CLERK，且修改的工資超過200時才進行觸發，否則不進行觸發。?
所以在驗證過程中，雖然修改了3條記錄，但通過查詢語句發現：第一條修改語句修改編號為7788的SCOTT記錄，因為SCOTT的職務是ANALYST，不符合WHEN條件，沒有引起觸發器動作；第二條修改語句修改編號為7369的SMITH的記錄，職務為CLERK，因為增加的工資(500)超過了200，所以引起觸發器動作，并在LOGERR表中進行了記錄；第三條修改語句修改編號為7876的雇員ADAMS的記錄，雖然ADAMS的職務為CLERK，但修改的工資(50)沒有超過200，所以沒有引起觸發器動作。?
注意：在WHEN條件中引用new和old不需要在前面加“: ”。?
在以上實例中，記錄了對工資的修改超出范圍的信息，但沒有限制對工資的修改。那么當對雇員工資的修改幅度不滿足條件時，能否直接限制對工資的修改呢？答案是肯定的。?
【訓練3】? 創建觸發器CHECK_SAL，當對職務為CLERK的雇員的工資修改超出500至2000的范圍時，進行限制。?
步驟1：創建和編譯以下觸發器：?
Sql代碼?
執行結果：?
Sql代碼?
步驟2：在EMP表中插入記錄：?
Sql代碼?
執行結果：?
Sql代碼?
步驟3：檢查工資的修改結果：?
Sql代碼?
執行結果為：?
???Sql代碼?
說明：在觸發器中，當IF語句的條件滿足時，即對職務為CLERK的雇員工資的修改超出指定范圍時，用RAISE_APPLICATION_ERROR語句來定義一個臨時定義的異常，并立即引發異常。由于觸發器是BEFORE類型，因此觸發器先執行，觸發器因異常而終止，SQL語句的執行就會取消。?
通過步驟2的執行信息可以看到，第一條語句修改編號為7876的雇員ADAMS的工資為800，成功執行。第二條語句修改雇員ADAMS的工資為450，發生異常，執行失敗。這樣就阻止了不符合條件的工資的修改。通過步驟3的查詢可以看到，雇員ADAMS最后的工資是800，即發生異常之前的修改結果。?
【練習3】限定對emp表的修改，只能修改部門10的雇員工資。?
?? 【訓練4】? 創建一個行級觸發器CASCADE_UPDATE，當修改部門編號時，EMP表的相關行的部門編號也自動修改。該觸發器稱為級聯修改觸發器。?
步驟1：創建和編譯以下觸發器：?
Sql代碼?
執行結果：?
Sql代碼?
步驟2：驗證觸發器：?
Sql代碼?
執行結果：?
Sql代碼?
執行查詢：?
Sql代碼?
執行結果：?
??Sql代碼?
說明：通過檢查雇員的部門編號，發現原來編號為10的部門編號被修改為11。?
本例中的UPDATE OF deptno表示只有在修改表的DEPTNO列時才引發觸發器，對其他列的修改不會引起觸發器的動作。在觸發器中，對雇員表的部門編號與修改之前的部門編號一樣的雇員，修改其部門編號為新的部門編號。注意，在語句中同時用到了:new和:old來引用修改部門編號前后的部門編號。?
【練習4】建立級聯刪除觸發器CASCADE_DELETE，當刪除部門時，級聯刪除EMP表的雇員記錄。?
利用觸發器還可以修改數據。?
【訓練5】? 將插入的雇員的名字變成以大寫字母開頭。?
步驟1：創建和編譯以下觸發器：?
Sql代碼?
執行結果：?
Sql代碼?
步驟2：驗證運行結果：?
Sql代碼?
執行結果：?
Sql代碼?
執行查詢：?
Sql代碼?
執行結果：?
Sql代碼?
說明：在本例中，通過直接為:new.ename進行賦值，修改了插入的值，但是這種用法只能在BEFORE型觸發器中使用。驗證結果為，在插入語句中雇員名稱為大寫的BILL，查詢結果中雇員名稱已經轉換成以大寫開頭的Bill。?
【練習5】限定一次對雇員的工資修改不超過原工資的10%。?
語句級觸發器的應用?
同行級觸發器不同，語句級觸發器的每個操作語句不管操作的行數是多少，只觸發一次觸發器，所以語句級觸發器適合于對整個表的操作權限等進行控制。在觸發器定義中若省略FOR EACH ROW子句，則為語句級觸發器。?
【訓練1】? 創建一個語句級觸發器CHECK_TIME，限定對表EMP的修改時間為周一至周五的早8點至晚5點。?
步驟1：創建和編譯以下觸發器：?
Sql代碼?
執行結果：?
Sql代碼?
步驟2：當前時間為18點50分，在EMP表中插入記錄：?
Sql代碼?
顯示結果為：?
Sql代碼?
? 說明：通過引發異常限制對數據庫進行的插入、刪除和修改操作的時間。SYSDATE用來獲取系統當前時間，并按不同的格式字符串進行轉換。“DY”表示獲取英文表示的星期簡寫，“HH24”表示獲取24小時制時間的小時。?
當在18點50分修改表中的數據時，由于時間在8點至17點(晚5點)之外，所以產生“非法時間修改表錯誤”的用戶自定義錯誤，修改操作終止。?
【練習1】設計一個語句級觸發器，限定只能對數據庫進行修改操作，不能對數據庫進行插入和刪除操作。在需要進行插入和刪除時，將觸發器設置為無效狀態，完成后重新設置為生效狀態。?
數據庫事件觸發器?
數據庫事件觸發器有數據庫級和模式級兩種。前者定義在整個數據庫上，觸發事件是數據庫事件，如數據庫的啟動、關閉，對數據庫的登錄或退出。后者定義在模式上，觸發事件包括模式用戶的登錄或退出，或對數據庫對象的創建和修改(DDL事件)。?
數據庫事件觸發器的觸發事件的種類和級別如表9-3所示。?
Sql代碼?
定義數據庫事件和模式事件觸發器?
創建數據庫級觸發器需要ADMINISTER DATABASE TRIGGER系統權限，一般只有系統管理員擁有該權限。?
對于模式級觸發器，為自己的模式創建觸發器需要CREATE TRIGGER權限，如果是為其他模式創建觸發器，需要CREATE ANY TRIGGER權限。?
數據庫事件和模式事件觸發器的創建語法與DML觸發器的創建語法類似。數據庫事件或模式事件觸發器的創建語法如下：?
CREATE [OR REPLACE] TRIGGER 觸發器名?
{BEFORE|AFTER }?
{DDL事件1 [DDL事件2...]| 數據庫事件1 [數據庫事件2...]}?
ON {DATABASE| [模式名.]SCHEMA }?
[WHEN (條件)]?
DECLARE?
聲明部分?
BEGIN?
主體部分?
END;?
其中：DATABASE表示創建數據庫級觸發器，數據庫級要給出數據庫事件；SCHEMA表示創建模式級觸發器，模式級要給出模式事件或DDL事件。?
在數據庫事件觸發器中，可以使用如表9-4所示的一些事件屬性。不同類型的觸發器可以使用的事件屬性有所不同。?
Sql代碼?
數據庫事件觸發器?
下面是一個綜合的數據庫事件觸發器練習。先為STUDENT賬戶授予創建數據庫事件觸發器的權限，ADMINISTER DATABASE TRIGGER，然后創建有關的表和觸發器，最后予以驗證。?
? 【訓練1】? 創建觸發器，對本次數據庫啟動以來的用戶登錄時間進行記錄，每次數據庫啟動后，先清空該表。?
步驟1：創建登錄事件記錄表：?
Sql代碼?
執行結果：?
Sql代碼?
步驟2：創建數據庫STARTUP事件觸發器：?
Sql代碼?
執行結果：?
Sql代碼?
步驟3：創建數據庫LOGON事件觸發器：?
Sql代碼?
執行結果：?
Sql代碼?
步驟4：驗證DATABASE_LOGON觸發器：?
Sql代碼?
執行結果：?
Sql代碼?
執行查詢：?
Sql代碼?
執行結果：?
Sql代碼?
步驟5：驗證INIT_LOGON觸發器。?
重新啟動數據庫，登錄STUDENT賬戶：?
Sql代碼?
執行結果：?
Sql代碼?
說明：本例中共創建了兩個數據庫級事件觸發器。DATABASE_LOGON在用戶登錄時觸發，向表userlog中增加一條記錄，記錄登錄用戶名和登錄時間。INIT_LOGON在數據庫啟動時觸發，清除userlog表中記錄的數據。所以當數據庫重新啟動后，重新登錄STUDENT賬戶，此時userlog表中只有一條記錄。?
【訓練2】? 創建STUDENT_LOGON模式級觸發器，專門記錄STUDENT賬戶的登錄時間：?
Sql代碼?
執行結果：?
Sql代碼?
說明：為當前模式創建觸發器，可以省略SCHEMA前面的模式名。?
【練習1】修改DATABASE_LOGON觸發器和userlog表，增加對退出時間的記錄。?
DDL事件觸發器?
【訓練1】? 通過觸發器阻止對emp表的刪除。?
步驟1：創建DDL觸發器：?
Sql代碼?
執行結果：?
Sql代碼?
步驟2：通過刪除emp表驗證觸發器：?
Sql代碼?
執行結果：?
Sql代碼?
? ?? 說明：該觸發器阻止在當前模式下對emp表的刪除，但不阻止刪除其他對象。Sys.Dictionary_obj_name屬性返回要刪除的對象名稱。?
替代觸發器?
【訓練1】? 在emp表的視圖上，通過觸發器修改emp表。?
步驟1：創建視圖emp_name：?
Sql代碼?
執行結果：?
Sql代碼?
步驟1：創建替代觸發器：?
Sql代碼?
執行結果：?
Sql代碼?
步驟2：向emp_name視圖插入記錄：?
Sql代碼?
執行結果：?
Sql代碼?
? 說明：向視圖直接插入雇員名將會發生錯誤，因為emp表的雇員編號列不允許為空。通過創建替代觸發器，將向視圖插入雇員名稱轉換為向emp表插入雇員編號和雇員名稱，雇員編號取當前的最大雇員編號加1。試檢查emp表的雇員列表。?
【訓練2】? 在emp表的視圖emp_name上，通過觸發器阻止對emp表的刪除。?
步驟1：阻止通過視圖刪除雇員，并顯示用戶自定義錯誤信息：?
Sql代碼?
執行結果：?
Sql代碼?
步驟2：通過對視圖進行刪除來驗證觸發器：?
Sql代碼?
執行結果：?
Sql代碼?
說明：可以通過視圖emp_name對雇員進行刪除，比如執行DELETE FROM emp_name語句將刪除雇員表的全部雇員。但是由于在emp_name視圖中只能看到一部分雇員信息，所以刪除可能會產生誤操作。通過定義一個替代觸發器，可阻止通過emp_name視圖對emp表雇員進行刪除，但不阻止直接對emp表進行刪除。?
查看觸發器?
【訓練1】? 顯示觸發器CHECK_TIME的體部分：?
Sql代碼?
結果為：?
Sql代碼?
階段訓練?
【訓練1】? 創建觸發器，進行表的同步復制。?
步驟1：創建emp表的復本employee:?
Sql代碼?
執行結果：?
Sql代碼?
步驟2：創建和編譯以下觸發器:?
Sql代碼?
執行結果：?
Sql代碼?
步驟3：對emp表進行插入、刪除和更新：?
Sql代碼?
執行結果：?
Sql代碼?
步驟4：檢查emp表和employee表中被插入、刪除和更新的雇員。?
運行結果略，請自行驗證。?
? 說明：在觸發器中判斷觸發事件，根據不同的事件對employee表進行不同的操作。?
【練習1】創建一個emp表的觸發器EMP_TOTAL，每當向雇員表插入、刪除或更新雇員信息時，將新的統計信息存入統計表EMPTOTAL，使統計表總能夠反映最新的統計信息。?
統計表是記錄各部門雇員總人數、總工資的統計表，結構如下：?
部門編號 number(2)?
總人數 number(5)?
總工資 number(10,2)?
練習?
1. 下列有關觸發器和存儲過程的描述，正確的是：?
A. 兩者都可以傳遞參數?
B. 兩者都可以被其他程序調用?
C. 兩種模塊中都可以包含數據庫事務語句?
D. 兩者創建的系統權限不同?
2. 下列事件，屬于DDL事件的是：?
??? A.? INSERT B.? LOGON?
??? C.? DROP D.? SERVERERROR?
3. 假定在一個表上同時定義了行級和語句級觸發器，在一次觸發當中，下列說法正確的是：?
??? A. 語句級觸發器只執行一次?
??? B. 語句級觸發器先于行級觸發器執行?
?????????? C. 行級觸發器先于語句級觸發器執行?
??????????????? D. 行級觸發器對表的每一行都會執行一次?
4. 有關行級觸發器的偽記錄，下列說法正確的是：?
?? A.? INSERT事件觸發器中，可以使用:old偽記錄。?
?? B.? DELETE事件觸發器中，可以使用:new偽記錄。?
?? C.? UPDATA事件觸發器中，只能使用:new偽記錄。?
??????????????? D.? UPDATA事件觸發器中，可以使用:old偽記錄。?
5. 下列有關替代觸發器的描述，正確的是：?
??? A. 替代觸發器創建在表上?
??????????????? B. 替代觸發器創建在數據庫上?
??????????????? C. 通過替代觸發器可以向基表插入數據?
??????????????? D. 通過替代觸發器可以向視圖插入數據?
黑色頭發:http://heisetoufa.javaeye.com/############################################## ############################################## ###################自己寫了個例子################################################################# ############################################## drop trigger c_object; drop trigger d_object; CREATE OR REPLACE TRIGGER d_object BEFORE DROP ON SCHEMA DECLARE BEGINdelete from dict_columns where table_name = lower(Sys.Dictionary_obj_name); END; / CREATE OR REPLACE TRIGGER c_object AFTER CREATE ON SCHEMA DECLAREaa VARCHAR2(64);sql_str VARCHAR2(768); BEGINFOR curs IN (SELECT table_name, column_name, data_type, data_length, data_default FROM user_tab_columns where table_name not like '%$%' ORDER BY table_name, column_id) LOOPIF curs.data_default IS NULL THENcurs.data_default := ' ';END IF;sql_str := 'INSERT INTO dict_columns (table_name,column_name,data_type,data_length,data_default) VALUES(:1,:2,:3,:4,:6)';EXECUTE IMMEDIATE sql_str USING curs.table_name,curs.column_name,curs.data_type,curs.data_length,curs.data_default;END LOOP; END; /
?
總結
以上是生活随笔為你收集整理的oracle触发器高级教程的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： 华硕笔记本u盘启动不了怎么办 华硕笔记本
- 下一篇： ORACLE中对LONG类型进行处理的方