ORACLE 觸發器語法及實例 一

一  ORACLE觸發器語法

http://www.zw1840.com/oracle/translation/concepts/html/22.Triggers.htm

http://www.zw1840.com/oracle/index.htm

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

功能：

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

2、 自動生成派生列，比如自增字段

3、 強制數據一致性

4、 提供審計和日志記錄

5、 防止無效的事務處理

6、 啟用復雜的業務邏輯

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

1、觸發器的語法：

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

ON 表名

[FOR EACH ROW]

BEGIN

     pl/sql 語句

END

其中：

觸發器名：觸發器對象的名稱。

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

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

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

after---表示在數據庫動作之后出發器執行。

觸發事件：指明哪些數據庫動作會觸發此觸發器：

insert：數據庫插入會觸發此觸發器；

update：數據庫修改會觸發此觸發器；

delete：數據庫刪除會觸發此觸發器。

表 名：數據庫觸發器所在的表。

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

2、舉例：

下面的觸發器在更新表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;

/

二  Oracle觸發器詳解  

開始：

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、觸發器的組成部分：

1、 觸發器名稱

2、 觸發語句

3、 觸發器限制

4、 觸發操作

    1.1、觸發器名稱

create trigger biufer_employees_department_id

命名習慣：

biufer（before insert update for each row）

employees 表名

department_id 列名

    1.2、觸發語句

比如：

表或視圖上的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的時候

    1.3、觸發器限制

when (new_value.department_id<>80 )

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

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

    1.4、觸發操作

是觸發器的主體

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;

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

2、觸發器的類型有:

觸發器類型：

1、 語句觸發器

2、 行觸發器

3、 INSTEAD OF 觸發

4、 系統條件觸發器

5、 用戶事件觸發器

2.1、語句級觸發器.(語句級觸發器對每個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;  

2.2、行級觸發器.(行級觸發器對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