ORACLE觸發器語法

 

 

     觸發器是特定事件出現的時候，自動執行的程式碼塊。類似於儲存過程，觸發器與儲存過程的區別在於: 儲存過程是由使用者或應用程式顯式呼叫的,而觸發器是不能被直接呼叫的。

功能：

1、 允許/限制對錶的修改

2、 自動生成派生列，比如自增欄位

3、 強制資料一致性

4、 提供審計和日誌記錄

5、 防止無效的事務處理

6、 啟用複雜的業務邏輯

 

觸發器觸發時間有兩種：after和before。

、觸發器的語法：

CREATE [OR REPLACE] TIGGER 觸發器名 觸發時間 觸發事件

ON 表名

[FOR EACH ROW]

BEGIN

     pl/sql 語句

END

其中：

觸發器名：觸發器物件的名稱。

由於觸發器是資料庫自動執行的，因此該名稱只是一個名稱，沒有實質的用途。

觸發時間：指明觸發器何時執行，該值可取：

before---表示在資料庫動作之前觸發器執行；

after---表示在資料庫動作之後出發器執行。

觸發事件：指明哪些資料庫動作會觸發此觸發器：

insert：資料庫插入會觸發此觸發器；

update：資料庫修改會觸發此觸發器；

delete：資料庫刪除會觸發此觸發器。

表 名：資料庫觸發器所在的表。

for each row：對錶的每一行觸發器執行一次。如果沒有這一選項，則只對整個表執行一次。

、舉例：

下面的觸發器在更新表auths之前觸發，目的是不允許在週末修改表：

create trigger auth_secure before insert or update or delete //對整表更新前觸發

on auths

begin

if(to_char(sysdate,'DY')='SUN'

RAISE_APPLICATION_ERROR(-20600,'不能在週末修改表auths');

end if;

end

 

例子：

CREATE OR REPLACE TRIGGER CRM.T_SUB_USERINFO_AUR_NAME AFTER UPDATE OF STAFF_NAME

ON CRM.T_SUB_USERINFO

REFERENCING OLD AS OLD NEW AS NEW

FOR EACH ROW

declare

begin

  if :NEW.STAFF_NAME!=:OLD.STAFF_NAME then

   begin

     --客戶投訴

    update T_COMPLAINT_MANAGE set SERVE_NAME=:NEW.STAFF_NAME where SERVE_SEED=:OLD.SEED;

    --客戶關懷

update T_CUSTOMER_CARE set EXECUTOR_NAME=:NEW.STAFF_NAME

where EXECUTOR_SEED=:OLD.SEED;

    --客戶服務

update T_CUSTOMER_SERVICE set EXECUTOR_NAME=:NEW.STAFF_NAME

where EXECUTOR_SEED=:OLD.SEED;

    end;

  end if;

end T_sub_userinfo_aur_name;

/

  

開始：

create trigger biufer_employees_department_id

before insert or update of department_id on employees

referencing old as old_value new as new_value

for each row

when (new_value.department_id<>80 )

begin

:new_value.commission_pct :=0;

end;

/

   、觸發器的組成部分：

1、 觸發器名稱

2、 觸發語句

3、 觸發器限制

4、 觸發操作

    、觸發器名稱

create trigger biufer_employees_department_id

 

命名習慣：

biufer（before insert update for each row）

employees 表名

department_id 列名

    、觸發語句

比如：

表或檢視上的DML語句

DDL語句

資料庫關閉或啟動,startup shutdown 等等

before insert or update

       of department_id

       on employees

referencing old as old_value

            new as new_value

for each row

說明：

1、 無論是否規定了department_id ，對employees表進行insert的時候

2、 對employees表的department_id列進行update的時候

    、觸發器限制

when (new_value.department_id<>80 )

限制不是必須的。此例表示如果列department_id不等於80的時候，觸發器就會執行。

其中的new_value是代表更新之後的值。

    、觸發操作

是觸發器的主體

begin

       :new_value.commission_pct :=0;

end;

主體很簡單，就是將更新後的commission_pct列置為0

 

觸發：

insert into employees(employee_id,last_name,first_name,hire_date,job_id,email,

department_id,salary,commission_pct )

values( 12345,’Chen’,’Donny’, sysdate, 12, ‘donny@hotmail.com’,60,10000,.25);

select commission_pct from employees where employee_id=12345;

觸發器不會通知使用者，便改變了使用者的輸入值。

、觸發器的型別有:

觸發器型別：

1、 語句觸發器

2、 行觸發器

3、 INSTEAD OF 觸發

4、 系統條件觸發器

5、 使用者事件觸發器

、語句級觸發器.(語句級觸發器對每個DML語句執行一次)

       是在表上或者某些情況下的檢視上執行的特定語句或者語句組上的觸發器。能夠與INSERT、UPDATE、DELETE或者組合上進行關聯。但是無論使用什麼樣的組合，各個語句觸發器都只會針對指定語句啟用一次。比如，無論update多少行，也只會呼叫一次update語句觸發器。

例項:

create or replace trigger tri_test

after insert or update or delete on test

begin

if updating then

dbms_output.put_line('修改');

elsif deleting then

dbms_output.put_line('刪除');

elsif inserting then

dbms_output.put_line('插入');

end if;

end;  

、行級觸發器.(行級觸發器對DML語句影響的每個行執行一次)

例項一:

--觸發器

--行級觸發器

create table test(sid number,sname varchar2(20));--建立一個表

create sequence seq_test;--建立序列

 

create or replace trigger tri_test--建立觸發器

before insert or update of sid on test

for each row--觸發每一行

begin

if inserting then

select seq_test.nextval into:new.sid from dual;

else

raise_application_error(-20020,'不允許更新ID值!');--中斷程式

end if;

end;  

--測試,插入幾條記錄

insert into test values(0,'ff');

insert into test values(0,'ff');

insert into test values(0,'tt');

輸出結果如下圖所示:

 

例項二:

--建立一個觸發器,無論使用者插入新記錄,還是修改emp表的job列,都將使用者指定的job列的值轉換成大寫.

create or replace trigger trig_job

before insert or update of job

on emp

for each row

<SPAN lang=EN-US style="FONT-FAMILY: 宋體; mso-bidi-font-size: 10.5