oracle 建立觸發器_oracle建立觸發器

建立觸發器

建立觸發器的一般文法是:

CREATE [OR REPLACE] TRIGGER trigger_name

{BEFORE | AFTER }

{INSERT | DELETE | UPDATE [OF column [, column …]]}

[OR {INSERT | DELETE | UPDATE [OF column [, column …]]}...]

ON [schema.]table_name | [schema.]view_name 

[REFERENCING {OLD [AS] old | NEW [AS] new| PARENT as parent}]

[FOR EACH ROW ]

[WHEN condition]

PL/SQL_BLOCK | CALL procedure_name;

其中：

BEFORE 和AFTER指出觸發器的觸發時序分别為前觸發和後觸發方式，前觸發是在執行觸發事件之前觸發目前所建立的觸發器，後觸發是在執行觸發事件之後觸發目前所建立的觸發器。

FOR EACH ROW選項說明觸發器為行觸發器。行觸發器和語句觸發器的差別表現在：行觸發器要求當一個DML語句操走影響資料庫中的多行資料時，對于其中的每個資料行，隻要它們符合觸發限制條件，均激活一次觸發器；而語句觸發器将整個語句操作作為觸發事件，當它符合限制條件時，激活一次觸發器。當省略FOR EACH ROW 選項時，BEFORE 和AFTER 觸發器為語句觸發器，而INSTEAD OF 觸發器則隻能為行觸發器。

REFERENCING 子句說明相關名稱，在行觸發器的PL/SQL塊和WHEN 子句中可以使用相關名稱參照目前的新、舊列值，預設的相關名稱分别為OLD和NEW。觸發器的PL/SQL塊中應用相關名稱時，必須在它們之前加冒号(:)，但在WHEN子句中則不能加冒号。

WHEN 子句說明觸發限制條件。Condition 為一個邏輯表達時，其中必須包含相關名稱，而不能包含查詢語句，也不能調用PL/SQL 函數。WHEN 子句指定的觸發限制條件隻能用在BEFORE 和AFTER 行觸發器中，不能用在INSTEAD OF 行觸發器和其它類型的觸發器中。

當一個基表被修改( INSERT, UPDATE, DELETE)時要執行的存儲過程，執行時根據其所依附的基表改動而自動觸發，是以與應用程式無關，用資料庫觸發器可以保證資料的一緻性和完整性。

每張表最多可建立12 種類型的觸發器，它們是:

BEFORE INSERT

BEFORE INSERT FOR EACH ROW

AFTER INSERT

AFTER INSERT FOR EACH ROW

BEFORE UPDATE

BEFORE UPDATE FOR EACH ROW

AFTER UPDATE

AFTER UPDATE FOR EACH ROW

BEFORE DELETE

BEFORE DELETE FOR EACH ROW

AFTER DELETE

AFTER DELETE FOR EACH ROW

1 觸發器觸發次序

1.        執行 BEFORE語句級觸發器;

2.        對與受語句影響的每一行：

l         執行 BEFORE行級觸發器

l         執行 DML語句

l         執行 AFTER行級觸發器 

3.        執行 AFTER語句級觸發器

2 建立DML觸發器

    觸發器名與過程名和包的名字不一樣，它是單獨的名字空間，因而觸發器名可以和表或過程有相同的名字，但在一個模式中觸發器名不能相同。

DML觸發器的限制

l         CREATE TRIGGER語句文本的字元長度不能超過32KB；

l         觸發器體内的SELECT 語句隻能為SELECT … INTO …結構，或者為定義遊标所使用的SELECT 語句。

l         觸發器中不能使用資料庫事務控制語句 COMMIT; ROLLBACK, SVAEPOINT 語句；

l         由觸發器所調用的過程或函數也不能使用資料庫事務控制語句；

l         觸發器中不能使用LONG, LONG RAW 類型；

l         觸發器内可以參照LOB 類型列的列值，但不能通過 :NEW 修改LOB列中的資料；

DML觸發器基本要點

l         觸發時機：指定觸發器的觸發時間。如果指定為BEFORE，則表示在執行DML操作之前觸發，以便防止某些錯誤操作發生或實作某些業務規則；如果指定為AFTER，則表示在執行DML操作之後觸發，以便記錄該操作或做某些事後處理。

l         觸發事件：引起觸發器被觸發的事件，即DML操作（INSERT、UPDATE、DELETE）。既可以是單個觸發事件，也可以是多個觸發事件的組合（隻能使用OR邏輯組合，不能使用AND邏輯組合）。

l         條件謂詞：當在觸發器中包含多個觸發事件（INSERT、UPDATE、DELETE）的組合時，為了分别針對不同的事件進行不同的處理，需要使用ORACLE提供的如下條件謂詞。

1）。INSERTING：當觸發事件是INSERT時，取值為TRUE，否則為FALSE。

2）。UPDATING [（column_1,column_2,…,column_x）]：當觸發事件是UPDATE      時，如果修改了column_x列，則取值為TRUE，否則為FALSE。其中column_x是可選的。

3）。DELETING：當觸發事件是DELETE時，則取值為TRUE，否則為FALSE。

解發對象：指定觸發器是建立在哪個表、視圖上。

l         觸發類型：是語句級還是行級觸發器。

l         觸發條件：由WHEN子句指定一個邏輯表達式，隻允許在行級觸發器上指定觸發條件，指定UPDATING後面的列的清單。

問題：當觸發器被觸發時，要使用被插入、更新或删除的記錄中的列值，有時要使用操作前、        後列的值.

實作:  :NEW 修飾符通路操作完成後列的值

        :OLD 修飾符通路操作完成前列的值

特性

INSERT

UPDATE

DELETE

OLD

NULL

實際值

NEW

例1: 建立一個觸發器, 當職工表 emp 表被删除一條記錄時，把被删除記錄寫到職工表删除日志表中去。

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2; 

CREATE OR REPLACE TRIGGER tr_del_emp 

   BEFORE DELETE --指定觸發時機為删除操作前觸發

   ON scott.emp 

   FOR EACH ROW   --說明建立的是行級觸發器 

BEGIN

   --将修改前資料插入到日志記錄表 del_emp ,以供監督使用。

   INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )

       VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );

END;

DELETE emp WHERE empno=7788;

DROP TABLE emp_his;

DROP TRIGGER del_emp;

例2：限制對Departments表修改（包括INSERT,DELETE,UPDATE）的時間範圍，即不允許在非工作時間修改departments表。

CREATE OR REPLACE TRIGGER tr_dept_time

BEFORE INSERT OR DELETE OR UPDATE 

ON departments

 IF (TO_CHAR(sysdate,'DAY') IN ('星期六', '星期日')) OR (TO_CHAR(sysdate, 'HH24:MI') NOT BETWEEN '08:30' AND '18:00') THEN

     RAISE_APPLICATION_ERROR(-20001, '不是上班時間，不能修改departments表');

 END IF;

例3：限定隻對部門号為80的記錄進行行觸發器操作。

CREATE OR REPLACE TRIGGER tr_emp_sal_comm

BEFORE UPDATE OF salary, commission_pct

       OR DELETE

ON HR.employees

FOR EACH ROW

WHEN (old.department_id = 80)

 CASE

     WHEN UPDATING ('salary') THEN

        IF :NEW.salary < :old.salary THEN

           RAISE_APPLICATION_ERROR(-20001, '部門80的人員的工資不能降');

        END IF;

     WHEN UPDATING ('commission_pct') THEN

        IF :NEW.commission_pct < :old.commission_pct THEN

           RAISE_APPLICATION_ERROR(-20002, '部門80的人員的獎金不能降');

     WHEN DELETING THEN

          RAISE_APPLICATION_ERROR(-20003, '不能删除部門80的人員記錄');

     END CASE;

END; 

/*

執行個體：

UPDATE employees SET salary = 8000 WHERE employee_id = 177;

DELETE FROM employees WHERE employee_id in (177,170);

*/

例4：利用行觸發器實作級聯更新。在修改了主表regions中的region_id之後（AFTER），級聯的、自動的更新子表countries表中原來在該地區的國家的region_id。

CREATE OR REPLACE TRIGGER tr_reg_cou

AFTER update OF region_id

ON regions

 DBMS_OUTPUT.PUT_LINE('舊的region_id值是'||:old.region_id

                  ||'、新的region_id值是'||:new.region_id);

 UPDATE countries SET region_id = :new.region_id

 WHERE region_id = :old.region_id;

例5：在觸發器中調用過程。

CREATE OR REPLACE PROCEDURE add_job_history

 ( p_emp_id          job_history.employee_id%type

   , p_start_date      job_history.start_date%type

  , p_end_date        job_history.end_date%type

   , p_job_id          job_history.job_id%type

   , p_department_id   job_history.department_id%type

   )

IS

 INSERT INTO job_history (employee_id, start_date, end_date,

                           job_id, department_id)

  VALUES(p_emp_id, p_start_date, p_end_date, p_job_id, p_department_id);

END add_job_history;

--建立觸發器調用存儲過程...

CREATE OR REPLACE TRIGGER update_job_history

 AFTER UPDATE OF job_id, department_id ON employees

 FOR EACH ROW

 add_job_history(:old.employee_id, :old.hire_date, sysdate,

                  :old.job_id, :old.department_id);

3 建立替代(INSTEAD OF)觸發器

INSTEAD OF

ON [schema.] view_name --隻能定義在視圖上

[FOR EACH ROW ] --因為INSTEAD OF觸發器隻能在行級上觸發,是以沒有必要指定

PL/SQL_block | CALL procedure_name;

            INSTEAD OF 選項使ORACLE激活觸發器，而不執行觸發事件。隻能對視圖和對象視圖建立INSTEAD OF觸發器，而不能對表、模式和資料庫建立INSTEAD OF 觸發器。

            FOR EACH ROW選項說明觸發器為行觸發器。行觸發器和語句觸發器的差別表現在：行觸發器要求當一個DML語句操走影響資料庫中的多行資料時，對于其中的每個資料行，隻要它們符合觸發限制條件，均激活一次觸發器；而語句觸發器将整個語句操作作為觸發事件，當它符合限制條件時，激活一次觸發器。當省略FOR EACH ROW 選項時，BEFORE 和AFTER 觸發器為語句觸發器，而INSTEAD OF 觸發器則為行觸發器。

            REFERENCING 子句說明相關名稱，在行觸發器的PL/SQL塊和WHEN 子句中可以使用相關名稱參照目前的新、舊列值，預設的相關名稱分别為OLD和NEW。觸發器的PL/SQL塊中應用相關名稱時，必須在它們之前加冒号(:)，但在WHEN子句中則不能加冒号。

    INSTEAD_OF 用于對視圖的DML觸發，由于視圖有可能是由多個表進行聯結(join)而成，因而并非是所有的聯結都是可更新的。但可以按照所需的方式執行更新，例如下面情況：

例1：

CREATE OR REPLACE VIEW emp_view AS 

SELECT deptno, count(*) total_employeer, sum(sal) total_salary 

FROM emp GROUP BY deptno;

在此視圖中直接删除是非法：

SQL>DELETE FROM emp_view WHERE deptno=10;

DELETE FROM emp_view WHERE deptno=10

ERROR 位于第 1 行:

ORA-01732: 此視圖的資料操縱操作非法

但是我們可以建立INSTEAD_OF觸發器來為 DELETE 操作執行所需的處理，即删除EMP表中所有基準行：

CREATE OR REPLACE TRIGGER emp_view_delete

   INSTEAD OF DELETE ON emp_view FOR EACH ROW

   DELETE FROM emp WHERE deptno= :old.deptno;

END emp_view_delete; 

DELETE FROM emp_view WHERE deptno=10; 

DROP TRIGGER emp_view_delete;

DROP VIEW emp_view; 

例2：建立複雜視圖，針對INSERT操作建立INSTEAD OF觸發器，向複雜視圖插入資料。

l         建立視圖：

CREATE OR REPLACE FORCE VIEW "HR"."V_REG_COU" ("R_ID", "R_NAME", "C_ID", "C_NAME")

AS

 SELECT r.region_id,

    r.region_name,

    c.country_id,

    c.country_name

 FROM regions r,

    countries c

 WHERE r.region_id = c.region_id;

l         建立觸發器：

CREATE OR REPLACE TRIGGER "HR"."TR_I_O_REG_COU" INSTEAD OF

 INSERT ON v_reg_cou FOR EACH ROW DECLARE v_count NUMBER;

 SELECT COUNT(*) INTO v_count FROM regions WHERE region_id = :new.r_id;

 IF v_count = 0 THEN

    INSERT INTO regions

      (region_id, region_name

      ) VALUES

      (:new.r_id, :new.r_name

      );

 SELECT COUNT(*) INTO v_count FROM countries WHERE country_id = :new.c_id;

    INSERT

    INTO countries

      (

        country_id,

        country_name,

        region_id

      )

      VALUES

        :new.c_id,

        :new.c_name,

        :new.r_id

建立INSTEAD OF觸發器需要注意以下幾點：

l         隻能被建立在視圖上，并且該視圖沒有指定WITH CHECK OPTION選項。

l         不能指定BEFORE 或 AFTER選項。

l         FOR EACH ROW子可是可選的，即INSTEAD OF觸發器隻能在行級上觸發、或隻能是行級觸發器，沒有必要指定。

l         沒有必要在針對一個表的視圖上建立INSTEAD OF觸發器，隻要建立DML觸發器就可以了。

3 建立系統事件觸發器

    ORACLE10G提供的系統事件觸發器可以在DDL或資料庫系統上被觸發。DDL指的是資料定義語言，如CREATE 、ALTER及DROP 等。而資料庫系統事件包括資料庫伺服器的啟動或關閉，使用者的登入與退出、資料庫服務錯誤等。建立系統觸發器的文法如下： 

CREATE OR REPLACE TRIGGER [sachema.]trigger_name

{BEFORE|AFTER} 

{ddl_event_list | database_event_list}

ON { DATABASE | [schema.]SCHEMA }

其中: ddl_event_list：一個或多個DDL 事件，事件間用 OR 分開；

         database_event_list：一個或多個資料庫事件，事件間用 OR 分開；

            系統事件觸發器既可以建立在一個模式上，又可以建立在整個資料庫上。當建立在模式(SCHEMA)之上時，隻有模式所指定使用者的DDL操作和它們所導緻的錯誤才激活觸發器, 預設時為目前使用者模式。當建立在資料庫(DATABASE)之上時，該資料庫所有使用者的DDL操作和他們所導緻的錯誤，以及資料庫的啟動和關閉均可激活觸發器。要在資料庫之上建立觸發器時，要求使用者具有ADMINISTER DATABASE TRIGGER權限。

下面給出系統觸發器的種類和事件出現的時機（前或後）：

事件

允許的時機

說明

STARTUP

AFTER

啟動資料庫執行個體之後觸發

SHUTDOWN

BEFORE

關閉資料庫執行個體之前觸發（非正常關閉不觸發）

SERVERERROR

資料庫伺服器發生錯誤之後觸發

LOGON

成功登入連接配接到資料庫後觸發

LOGOFF

開始斷開資料庫連接配接之前觸發

CREATE

BEFORE，AFTER

在執行CREATE語句建立資料庫對象之前、之後觸發

DROP

在執行DROP語句删除資料庫對象之前、之後觸發

ALTER

在執行ALTER語句更新資料庫對象之前、之後觸發

DDL

在執行大多數DDL語句之前、之後觸發

GRANT

執行GRANT語句授予權限之前、之後觸發

REVOKE

執行REVOKE語句收權限之前、之後觸犯發

RENAME

執行RENAME語句更改資料庫對象名稱之前、之後觸犯發

AUDIT / NOAUDIT

執行AUDIT或NOAUDIT進行審計或停止審計之前、之後觸發

4 系統觸發器事件屬性

事件屬性\事件

Startup/Shutdown

Servererror

Logon/Logoff

DML

事件名稱

?＊

＊

資料庫名稱

資料庫執行個體号

錯誤号

使用者名

模式對象類型

模式對象名稱

列

除DML語句的列屬性外，其餘事件屬性值可通過調用ORACLE定義的事件屬性函數來讀取。

函數名稱

資料類型

說    明

Ora_sysevent

VARCHAR2（20）

激活觸發器的事件名稱

Instance_num

NUMBER

資料庫執行個體名

Ora_database_name

VARCHAR2（50）

Server_error(posi)

錯誤資訊棧中posi指定位置中的錯誤号

Is_servererror(err_number)

BOOLEAN

檢查err_number指定的錯誤号是否在錯誤資訊棧中，如果在則傳回TRUE，否則傳回FALSE。在觸發器内調用此函數可以判斷是否發生指定的錯誤。

Login_user

VARCHAR2(30)

登陸或登出的使用者名稱

Dictionary_obj_type

VARCHAR2(20)

DDL語句所操作的資料庫對象類型

Dictionary_obj_name

DDL語句所操作的資料庫對象名稱

Dictionary_obj_owner

DDL語句所操作的資料庫對象所有者名稱

Des_encrypted_password

VARCHAR2(2)

正在建立或修改的經過DES算法加密的使用者密碼

例1：建立觸發器，存放有關事件資訊。

DESC ora_sysevent

DESC ora_login_user

--建立用于記錄事件用的表

CREATE TABLE ddl_event

(crt_date timestamp PRIMARY KEY,

 event_name VARCHAR2(20), 

 user_name VARCHAR2(10),

 obj_type VARCHAR2(20),

 obj_name VARCHAR2(20));

--建立觸犯發器

CREATE OR REPLACE TRIGGER tr_ddl

AFTER DDL ON SCHEMA

   INSERT INTO ddl_event VALUES

   (systimestamp,ora_sysevent, ora_login_user, 

    ora_dict_obj_type, ora_dict_obj_name);

END tr_ddl;

例2：建立登入、退出觸發器。

CREATE TABLE log_event

(user_name VARCHAR2(10),

 address VARCHAR2(20), 

 logon_date timestamp,

 logoff_date timestamp); 

--建立登入觸發器

CREATE OR REPLACE TRIGGER tr_logon

AFTER LOGON ON DATABASE

   INSERT INTO log_event (user_name, address, logon_date)

   VALUES (ora_login_user, ora_client_ip_address, systimestamp);

END tr_logon;

--建立退出觸發器

CREATE OR REPLACE TRIGGER tr_logoff

BEFORE LOGOFF ON DATABASE

   INSERT INTO log_event (user_name, address, logoff_date)

END tr_logoff;

5 使用觸發器謂詞

    ORACLE 提供三個參數INSERTING, UPDATING, DELETING 用于判斷觸發了哪些操作。

謂詞

行為

INSERTING

如果觸發語句是 INSERT 語句，則為TRUE,否則為FALSE

UPDATING

如果觸發語句是 UPDATE語句，則為TRUE,否則為FALSE

DELETING

如果觸發語句是 DELETE 語句，則為TRUE,否則為FALSE

6 重新編譯觸發器

如果在觸發器内調用其它函數或過程，當這些函數或過程被删除或修改後，觸發器的狀态将被辨別為無效。當DML語句激活一個無效觸發器時，ORACLE将重新編譯觸發器代碼，如果編譯時發現錯誤，這将導緻DML語句執行失敗。

在PL/SQL程式中可以調用ALTER TRIGGER語句重新編譯已經建立的觸發器，格式為：           

ALTER TRIGGER [schema.] trigger_name COMPILE [ DEBUG]

其中：DEBUG 選項要器編譯器生成PL/SQL 程式條使其所使用的調試代碼。