程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle數據庫基礎 >> Oracle 觸發器學習筆記

Oracle 觸發器學習筆記

編輯：Oracle數據庫基礎

最近要做一個日志記錄功能，記錄一些表的操作記錄，我的第一想法就是使用觸發器來實現。雖然想到了觸發器，但是自己還真沒有辦法立刻動手寫出觸發器，對於觸發器的語法都有很多地方不熟悉，甚至不理解。借著這次機會，好好的把觸發器又溫習了一篇，所以總結成文，以便後期查閱。

觸發器簡介

觸發器是存儲在數據庫服務器中的程序單元，當一個表或一個視圖被改變，或者數據庫發生某些事件時，Oracle會自動觸發觸發器，並執行觸發器中的代碼。只有在觸發器中定義的事件發生時，觸發器才被觸發。觸發器是自動執行的代碼塊，和存儲過程的區別在於，用戶可以直接調用存儲過程，而不能直接調用觸發器。

觸發事件

能夠觸發觸發器的事件有以下幾種：

DML操作（INSERT、UPDATE、DELETE）
DDL操作（CREATE、ALTER、DROP）
系統事件（數據庫的關閉與啟動等）
用戶事件（用戶的登陸等）

上述的這些語句都可以觸發觸發器。如果你想在這些事件發生時干些別的事情，這個時候只需要定義對應的觸發器即可，在觸發器中完成你的工作。

觸發器的組成

一個觸發器由三部分組成：

觸發事件或語句
觸發事件或語句可以是SQL語句、數據庫事件、用戶事件。這些事件引起觸發器被觸發；
觸發限制
觸發器限制是一個布爾表達式，當觸發器觸發時，會判斷該布爾表達式；當布爾表達式的值為Unknown或者False，“觸發動作”將不會被執行；當布爾表達式的值為True時，將會執行觸發動作語句。觸發限制是用WHEN子句來指定的。
觸發動作
觸發動作是一個PL/SQL過程塊，由SQL語句和PL/SQL語句等組成。當觸發限制為真時，它才被執行。

觸發器的簡單分類

觸發器是基於表、視圖、模式、數據庫的，於此，我們可以把觸發器分為下面的幾類：

行級觸發器和語句級觸發器
行級觸發器，即觸發機制是基於行的，當表中數據改變時，將觸發行級觸發器，改變一行數據，觸發一次；改變N行數據，就會觸發N次；
語句級觸發器是基於語句級的，當一條SQL語句改變數據時，無論這條SQL語句影響多少條記錄，語句級觸發器都只觸發一次。SQL語句每執行一次，語句級觸發器就被觸發一次。
BEFORE和AFTER觸發器
BEFORE表示在觸發語句運行前先運行“觸發動作”。AFTER表示觸發語句運行之後才運行“觸發動作”。BEFORE和AFTER適用於行級觸發器和語句級觸發器。
復合觸發器
復合觸發器是表上的觸發器，它有4個時間點，可以讓我們針對不同的時間點指定不同的處理動作。這四個時間點分別如下：
- 在觸發語句執行前（BEFORE STATEMENT）
- 在觸發語句執行後（AFTER STATEMENT）
- 在每行記錄被修改之前（BEFORE EACH ROW）
- 在每行記錄被修改之後（AFTER EACH ROW）

觸發語句必須是DML。如果觸發語句沒有影響任何一行數據，並且也沒有指定BEFORE STATEMENT和AFTER STATEMENT兩個時間點，則觸發器也不會被觸發。

INSTEAD OF觸發器
有的視圖，我們不能直接對其進行更新操作，但是我們可以再這種視圖上建立觸發器，利用觸發器對視圖的基表進行更新操作，這種類型的觸發器就叫做“INSTEAD OF觸發器”。
系統級觸發器
系統事件觸發器是基於數據庫系統的觸發器，系統事件觸發器與表、視圖沒有關系。系統事件包括數據庫啟動、關閉、服務器錯誤、數據庫角色改變等。當這些事件發生時，就會觸發系統事件觸發器。可以通過系統事件觸發器實現對數據庫的審計。
用戶級觸發器
用戶事件包括用戶登錄數據庫、用戶退出數據庫、用戶執行DDL/DML語句等。當這些事件發生時，會觸發用戶事件觸發器。

如何編寫觸發器

在觸發器中，最重要的是觸發動作部分，實際完成工作的部分也就是觸發動作。觸發動作是一個PL/SQL塊或者一個對子程序（存儲過程和函數）的調用。

create trigger tri_p
before insert or update of id on tb_student
for each row
when (new.id <> '00813027')
call check_id(:new.id)

上面的代碼創建了名為tri_p的觸發器，它的主體是子程序調用，調用存儲過程check_id。上面的代碼是調用的子程序，如果觸發主體不是一個子程序調用，而是一個PL/SQL塊，則需要對PL/SQL塊進行編碼。進行PL/SQL編碼時，需要注意以下幾個方面的東西。

在PL/SQL主體中訪問列的值
在觸發器中，列的值分為觸發語句執行前的值和執行後的值。如果觸發器涉及嵌套表，還可以使用PARENT對父表進行引用。PL/SQL新值和舊值的引用形式如下：
- :new.列名
- :old.列名
同時，需要注意的是，INSERT沒有舊值，只有新值。DELETE沒有新值，只有舊值。:new和:old只用於行級觸發器。比如以下的代碼：
```
create or replace trigger tri_tb_student
after update on jelly.tb_student
for each row
begin
    dbms_output.put_line('old value:' || :old.name);
    dbms_output.put_line('new value:' || :new.name);end;
```
我運行語句：
```
update jelly.tb_student set name='Jelly' where id='00813017';
```
就會輸出：
```
old value:JellyThinknew value:Jelly
```

判斷觸發的SQL語句類型
定義觸發器時，在一個觸發器中，可以指定多個觸發語句。但是在實際工作中，我們需要知道到底是哪個語句觸發了觸發器。Oracle提供了三個判斷條件（INSERTING、 DELETING、 UPDATING），用於判斷觸發觸發器的是INSERT、UPDATE還是DELETE操作。

create or replace trigger dml_trg
after insert or update or delete on jelly.tb_student
begin
    if updating then
        dbms_output.put_line('updating data from tb_student');
    elsif deleting then
        dbms_output.put_line('deleting data from tb_student');
    elsif inserting then
        dbms_output.put_line('inserting data into tb_student');
    end if;end;

編寫幾個簡單的觸發器

上面對觸發器進行了簡單的分類，並總結了編寫觸發主體時需要注意的事項，下面就對不同類型的觸發器進行實際的操作。

使用CREATE TRIGGER命令創建觸發器。用戶可以在自己的模式下創建觸發器，但是必須具有CREATE TRIGGER權限。用戶也可以在其他模式下創建觸發器，但是必須具有系統權限CREATE ANY TRIGGER。用戶也可以創建系統級的觸發器，但是必須具有ADMINISTER DATABASE TRIGGER權限。創建觸發器的常規語法如下：

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;

下面就來進行具體的代碼編寫：

創建行級觸發器

create or replace trigger row_trg
before update of name
on jelly.tb_student
for each row
begin
    dbms_output.put_line('ID:' || :old.id || '=>' || :new.id);
    dbms_output.put_line('Name:' || :old.name || '=>' || :new.name);
    dbms_output.put_line('Sex:' || :old.sex || '=>' || :new.sex);
    dbms_output.put_line('Age:' || :old.age || '=>' || :new.age);end;

重點是for each row。

創建語句級觸發器

create or replace trigger sentence_trg
before delete or insert
on jelly.tb_student
begin
    dbms_output.put_line('Called Once');end;

沒有使用for each row，表示創建的語句級觸發。當我運行以下語句：

insert into jelly.tb_student select * from jelly.tb_student;

此時，這個觸發器將只輸出一次Called Once。

創建BEFORE觸發器參見上面的行級觸發器和語句級觸發器，都是使用的BEFORE觸發器。

創建AFTER觸發器

create or replace trigger before_trg
after delete
on jelly.tb_student
begin
    dbms_output.put_line('After Trigger');end;

和BEFORE觸發器基本一樣，就是將BEFORE換成AFTER了。

創建復合觸發器

create or replace trigger compound_trigger
for update of name on jelly.tb_student compound trigger

    info1 constant varchar2(200) := 'Before Statement';
    info2 constant varchar2(200) := 'Before Each Row';
    info3 constant varchar2(200) := 'After Each Row';
    info4 constant varchar2(200) := 'After Statement';

    before statement is
    begin
        dbms_output.put_line(info1);
    end before statement;

    before each row is
    begin
        dbms_output.put_line(info2);
    end before each row;

    after each row is
    begin
        dbms_output.put_line(info3);
    end after each row;

    after statement is
    begin
        dbms_output.put_line(info4);
    end after statement;end