程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> PL/SQL之--觸發器，plsql觸發器

PL/SQL之--觸發器，plsql觸發器

編輯：Oracle教程

PL/SQL之--觸發器，plsql觸發器

一、簡介
　　觸發器在數據庫裡以獨立的對象進行存儲，它與存儲過程和函數不同的是，存儲過程與函數需要用戶顯示調用才執行，而觸發器是由一個事件來觸發運行。oracle事件指的是對數據庫的表或視圖進行的insert、update及delete操作或對視圖進行類似的操作。oracle將觸發器的功能擴展到了觸發ORACLE，如用戶的登錄、數據庫的啟動與關閉等。所以觸發器常用來完成由數據庫的完整性約束難以完成的復雜業務規則的約束，或用來監視對數據庫的各種操作，實現審計相關的功能。

觸發器的組成如下：

觸發事件：在任何情況下觸發trigger；如insert，update，delete

觸發時間：該trigger是在觸發事件發生之前（before）還是之後（after）觸發，也就是觸發事件和該trigger的操作順序。

觸發器本身：該trigger被觸發之後的執行體。例如PL/SQL塊。

觸發頻率：說明觸發器內定義的動作被執行的次數。即語句級（statement）觸發器和行級（row）觸發器

　　語句級（statement）觸發器：指當觸發事件發生時，該觸發器只執行一次；

　　行級（row）觸發器：指當某觸發事件發生時，對收到該操作一下的每一行數據，觸發器都單獨執行。
oracle觸發器分為如下三種：
1、DML觸發器 DML 數據操縱語言(Data Manipulation Language)
　　在DML語句(insert、update及delete)進行觸發，可以在DML操作前或操作後進行觸發，並且可以對每個行或語句操作上進行觸發。
2、替代觸發器
　　由於在ORACLE裡，不能直接對由兩個以上的表建立的視圖進行操作。所以給出了替代觸發器。它就是ORACLE 8專門為進行視圖操作的一種處理方法。
3、系統觸發器
　　ORACLE 8i 提供了第三種類型的觸發器叫系統觸發器。它可以在ORACLE數據庫系統的事件中進行觸發，如ORACLE系統的啟動與關閉等。

二、DML觸發器

　　oracle可以在dml語句進行觸發，可以在dml操作前或操作後進行觸發，並且可以對每個行（行級）或語句（級）操作上進行觸發。DML觸發器分為如下兩種觸發器：

行觸發器：是指為受到影響的各個行激活的觸發器，定義與語句觸發器類似，有以下兩個例外：

　　　　1、定義語句中包含FOR EACH ROW子句。
　　　　2、在BEFORE……FOR EACH ROW觸發器中，用戶可以引用受到影響的行值。

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

　　創建觸發器的一般語法如下：

　　create or replace trigger trigger_name
　　{before|after}
　　{insert|delete|update[of column]}
　　on [schema] table_name
　　　　[referencing {old[as] old|new[as]new|parent as parent}]
　　[for each row] 　　　
　　　　[when condition]
　　trigger body;

　　以下person表結構如下：

DROP TABLE person ;
CREATE TABLE person (
id NUMBER(11) NOT NULL ,
username VARCHAR2(255 ) NULL ,
age NUMBER(11) NULL ,
password VARCHAR2(255) NULL ,
PRIMARY KEY (id)
)

　　1、insert行級觸發器

create or replace trigger trigger_insert
  before insert on person
　referencing new as new old as old
  for each row
declare
  v_username varchar2(50);
begin
  v_username := :new.username; 
  :new.age := :new.age + 2; --插入的年齡加2
  dbms_output.put_line(v_username); -- 插入插入記錄的username字段
end trigger_insert; 
--添加數據
insert into person values (1, '張三', 20, 'zhangsan');

　　2、update行級觸發器

create or replace trigger trigger_update_before
  before update on person  
  referencing new as new old as old  
  for each row
declare
  v_username varchar2(50);
begin
  v_username := :new.username; -- 記錄新名字
  :new.age := :new.age + 3; -- 修改年齡
  dbms_output.put_line(v_username);
end trigger_update_before;
-- after update 觸發器
create or replace trigger trigger_update_after
  before update on person
  referencing new as new old as old
  for each row
declare
  v_old_name varchar2(50);
begin
  v_old_name :=  :old.username;  -- 記錄修改前的名字
  dbms_output.put_line(v_old_name);   
end trigger_update_after;    
-- 更新數據
update person set username='張三1', age = 19 where id = 1;

　　3、delete行級觸發器

create or replace trigger trigger_delete
  before delete on person
  referencing new as new old as old  
  for each row
declare
  v_old_name varchar2(50);
begin
  v_old_name :=  :old.username;  -- 獲取被刪除的用戶名稱
  dbms_output.put_line(v_old_name);
end trigger_delete;
--刪除數據
delete from person where id = 1;

　　4、語句觸發器

-- 記錄操作表的信息
create table person_log(
  who varchar2(30),
  when date,
  action varchar2(50)
)
--觸發器
create or replace trigger trigger_person_statment
  before insert or update or delete on person
declare
  -- 保存操作信息
  v_action person_log.action%type;
begin
  if inserting then
    v_action := 'Insert';
  elsif updating then
    v_action := 'Update';
  elsif deleting then
    v_action := 'Delete';
  else
   raise_application_error(-20001,'You should never ever get this error.');
  end if;
  insert into person_log(who,action,when)
  values(user, v_action,sysdate);
end trigger_person_statment;
-- 操作shuj
declare
begin  
   insert into person values(1003, '1342', 20, '234234');
   insert into person values(1004, '1342', 20, '234234');
   update person set age = 19 where id >1000 and id <1003;-- 此時觸發器之後執行一次
   delete from person where id >1000 and id <1003;-- 此時觸發器之後執行一次
end;

　　觸發器觸發次序

1、執行BEFORE語句級觸發器;
2、對與受語句影響的每一行
2.1、執行BEFORE行級觸發器
2.2、執行DML語句
2.3、執行AFTER行級觸發器
3、執行AFTER語句級觸發器

三、替代觸發器

用於執行一個替代操作來代替觸發事件的操作。例如：針對INSERT事件的替代觸發器，它由INSERT語句觸發，當出現INSERT語句時，該語句不會被執行，而是執行替代觸發器中定義的語句。
創建INSTEADOF觸發器需要注意以下幾點：

只能被創建在視圖上，並且該視圖沒有指定WITHCHECKOPTION選項。
不能指定BEFORE或AFTER選項。FOREACHROW子可是可選的，即INSTEADOF觸發器只能在行級上觸發、或只能是行級觸發器，沒有必要指定。
沒有必要在針對一個表的視圖上創建INSTEADOF觸發器，只要創建DML觸發器就可以了.

　　示例代碼：

-- 視圖
create or replace view v_person as  
  select username, age, password,  id p_id from person; 
-- 觸發器
create or replace trigger update_v_person INSTEAD OF 
update on v_person 
begin 
  update person set 
    username = substr(:new.username, instr(:new.username,',')+2),     
    age = :new.age, 
    password = :new.password 
  where id = :new.p_id; -- 視圖中p_id字段
end update_v_person; 

-- 嘗試更新name 
update person set username = 'Chen1, Donny1', age =18 where id=1
select * from person where id = 1;

四、系統觸發器
系統事件觸發器在發生如數據庫啟動或者關閉等系統事件時觸發，包括數據庫服務器的啟動或關閉，用戶的登錄與退出、數據庫服務錯誤等。系統觸發器可以在DDL語句（數據庫定義語句，如crate，alter，drop等）事件來觸發。

系統觸發器的時間和運行時機如下：

事件　　運行啟動的時機說明啟動（startup）之後實例啟動時激活關閉（shutdown）之前實例正常關閉時激活服務器錯誤（servererror）之後只要有錯誤就激活登錄（login）之後成功登錄後激活注銷（logoff）之前開始注銷時激活創建（create）之前，之後在創建之前或之後激活刪除（delete）之前，之後在撤銷之前或之後激活修改（alter）之前，之後在變更之前或之後激活

　　示例

　　記錄用戶登錄信息

-- 准備表登錄後保存用戶登錄信息
create table loggin_event(
    username   varchar2(50),
    logintime  date
); 
-- 登錄觸發器
create or replace trigger t_login
   after LOGON ON DATABASE
declare

begin
  -- 保存用戶登錄信息
  insert into loggin_event(username, logintime)
  values (USER, sysdate);
  commit;
end t_login;

　　記錄創建表的信息

-- 記錄創建表的信息
drop table test_create_log;
create table test_create_log(
     event  varchar2(50),
     type varchar2(50),
     name varchar2(50),
     owner varchar2(50),
     createtime date  default sysdate   
);

-- 創建表的時候記錄
create or replace trigger t_create
   after create on database  
declare
   v_event varchar2(50);
   v_type varchar2(50);
   v_name varchar2(50);
   v_owner varchar2(50);
begin
  -- 讀取DDL事件屬性
   v_event := sysevent; --事件
   v_type := dictionary_obj_type; -- 對象類型，如表
   v_name := dictionary_obj_name; -- 對象名稱；如表沒
   v_owner := dictionary_obj_owner; -- 擁有者
   insert into test_create_log values(v_event, v_type, v_name, v_owner, sysdate);
end t_create;   
   
select * from test_create_log;
drop table t_1;
create table t_1(id number);
select * from test_create_log;

四、觸發器需要注意的地方

　　before和afterz是支持觸發器的觸發時間，是前觸發還是後觸發。

　　for each row 說名觸發器是行觸發器。當聖靈for each row語句時，觸發器為語句觸發器，替代觸發器（instead of ）為行觸發器。

　　feferencing 子句說明相關名稱，在行觸發器的PL/SQL塊和WHEN子句中可以使用相關名稱，參照當前的新、舊列值，默認的名稱成為old和new。觸發器的PL/SQL塊中應相關名稱時，必須在他們之前加冒號（:），但在when子句中則不能加冒號。

　　:new 訪問操作完成後的值，:old訪問操作完成前的值。

　　　　對於insert 只有:new有效,對於update :new、:old都有效，對於delete只有:old有效。

　　oracle觸發器的應用一般應用在以下方面：

　　1、數據庫的安全性
　　　　可以基於時間限制用戶的操作，例如不允許下班後和節假日修改數據庫數據。可以基於數據庫中的數據限制用戶的操作，例如不允許價格的升幅一次超過10%。

　　2、實施復雜的安全性授權
　　　　利用觸發器控制實體的安全性，可以將權限藉於各種數據庫的值。

　　3、提供復雜的審計功能
　　　　審計用戶操作數據庫的語句。把用戶對數據庫的更新寫入審計表。

　　4、維護不同數據庫之間同步表
　　　　在不同的數據庫之間可以利用快照來實現數據的復制，但有些系統(例如某個系統有兩個數據庫，一個只提供系統讀，一個值提供系統寫)要求兩個數據庫數據
實時同步，就必須利用觸發器從一個數據庫中向另一個數據庫復制數據。

　　5、實現復雜的數據完整性規則
　　　　實現非標准的數據完整性檢查和約束。觸發器可產生比規則更為復雜的限制。與規則不
同，觸發器可以引用列或數據庫對象。提供可變的缺省值。

　　6、實現復雜的非標准的數據庫相關完整性規則
　　　　觸發器可以對數據庫中相關的表進行連環更新。例如，在auths表author_code列上的
刪除觸發器可導致相應刪除在其它表中的與之匹配的行。觸發器能夠拒絕或回退那些破壞相關完整性的變化，取消試圖進行數據更新的事務。當插入一個與其主健不匹配的外部鍵時，這種觸發器會起作用。

　　7、可提供表的同步復制

　　8、事件日志記錄

用pl/sql創建觸發器

在觸發器裡，沒法進行 select * from userinformation; 吧
如果可以，把這些信息 select 到什麼地去了？

提示錯誤：ora-04098：觸發器無效且未通過重新驗證。這說明你在建立觸發器的時候，報錯了，但觸發器是建立了，是處於一種有觸發器，但錯誤的狀態。你要重新運行一個創建觸發器的腳本，看一下是報什麼錯？