oracle 觸發器開發____oracle

 觸發器開發的目的：確保資料庫滿足特定的商業規則和企業邏輯，可以使用觸發器，約束，子程式。因為
   約束最簡單且效能好，所選約束，如果約束不能滿足，就用觸發器，若觸發器都不能滿足選擇子程式。
  
   一、觸發器功能：
   1，控制資料安全,在伺服器層級控制資料安全是通過授權和回收對象許可權來完成的，比如ams使用者對scott/tiger 使用者對錶DML 操作、
   grant select ,delete,update,insert on emp to ams;
   但是為了實現更複雜的安全模式，比如限制對某個表要修改的資料，就需要用到觸發器，比如，只有在9：00到17：00才能改變資料庫EMP表對資料庫表操作
   create or replace trigger triger_stat_before
    before insert or update or delete on emp
    
    declare
    -- local variables here
    begin
     if (to_char(sysdate,'HH24') not between '9' and '17') then
       RAISE_APPLICATION_ERROR(-20101,'非工作日時間');
     end if;
   end triger_before;

   2，實現資料審計，監視非法和可疑的資料活動，oracle 本身提供了審計功能。如對emp DML 操作以後，
   把SQL操作的資訊(使用者，時間等)寫入字典。但是只能審計sql 操作，不能記載資料變化。如果要記載資料
   變化必須使用DML觸發器。比如工資變化寫入資料字典表。
   --建立字典表
      drop table EMP_CHANGE;
   create table EMP_CHANGE(
    CNAME       VARCHAR2(200),
    OLDSAL      NUMBER,
    NEWSAL      NUMBER,
    CHANGE_TIME TIMESTAMP(6)
   )
  --建立觸發器
  create or replace trigger triger_row_after
   after update of sal  on emp 
   for each row
   declare
   -- local variables here
   v_temp number;
   begin
    select count(*) into v_temp from emp_change where emp_change.cname=:old.ename;
    if v_temp =0 then
     insert into emp_change values(:old.ename,:old.sal,:new.sal,sysdate);
    else
     update  emp_change set oldSal=:old.sal,newSal=:new.sal,change_time = sysdate
     where emp_change.cname = :old.ename;
    end if;
  end triger_row_after;
  
   執行sql:update emp set sal = sal*1 where deptno=30
   會把員工的工資變化情況寫入emp_change表

   3，實現資料完整性，有寫check 做不到的時候就要觸發器，比如新工資不能低於原來的工資也不能高於原來工資的20%。
  create or replace trigger triger_row_before
   before update of sal on emp 
   for each row
   when (new.sal <old.sal or new.sal >old.sal*1.2)
   declare
   -- local variables here
   begin
    raise_application_error(-20931,'新工資不能低於原來的工資,並且漲幅不能高於20%');
  end triger_row_before;
   執行sql: update emp set sal = sal*0.3 where deptno=30
   會報錯：新工資不能低於原來的工資,並且漲幅不能高於20%

   4，實現參照完整性，參照完整性是指兩個表有關聯關係，主外鍵關係，當刪除主表資料時，從表資料也對應被刪除
   修改主表的主鍵列資料時，會串聯更新從表相關資料列被修改。串聯刪除可以通過約束 on delete cascade 關鍵字
   alert table emp add constraint fk_deptno foreign key(deptno) references dept(deptno) on delete cascade;
   但是不能實現串聯更新。
   如：update dept set dept.deptno=50 where dept.deptno=20
   違法約束條件，已經找到子記錄
   觸發器可以實現串聯更新功能。
  create or replace trigger triger_up_casecade
   after update of deptno on dept 
   for each row
   declare
   -- local variables here
   begin
    update emp set emp.deptno=:new.deptno where emp.deptno = :old.deptno;
  end triger_up_casecade;
  執行sql:update dept set dept.deptno =50 where dept.deptno =10
  串聯更新了emp 表裡原來部門號為10的員工部門號為50.
  
   二、觸發器是被隱含執行的預存程序，允許基於表和試圖的DML操作和基於系統事件(啟用資料庫，關閉資料庫，登入)
   1)各種DML觸發器：
     觸發器組成:觸發事件，觸發器條件和觸發器操作三部分組成
    a 觸發事件是指引起觸發器被觸發的sql,資料庫事件，使用者事件，具體如下
     啟動關閉常式
     oracle 錯誤訊息
     使用者登入和斷開會話
     表或者視圖上的DML 操作
     任何方案上的DDL操作
    b 觸發器條件是指where子句指定一個運算式
    c 觸發器操作是指執行sql語句或者pl/sql塊
   
  重點是DML觸發器。
  觸發時機  before 表示在在執行DML操作之前觸發觸發器，after 表示在在執行DML操作之後觸發觸發器
  觸發類型  觸發事件發生以後，需要執行幾次觸發操作，如果語句觸發器(預設)，只會執行一次觸發代碼
  如果指定行觸發類型，則會在每個被作用行上執行一次觸發器代碼。
  注意：
  對於oracle行級觸發器(for each row)，不能對本表做任何操作，包括讀取
  原則：    就是為了防止髒讀 
  在before   insert觸發器中，可以實現對本表的訪問；  
  在after   insert觸發器中，不能實現對本表的訪問；  
  在before/after   update/delete觸發器中，都不能實現對本表的訪問。
  
  語句觸發器主要用在審計DML操作或者確保DML操作安全執行時,可以使用語句觸發器，如：對一個員工表更新之前
  檢查當前日期是不是周六，或者周日，如果是不能更新。
  
  before 語句觸發器：確保DML語句在正常情況下執行
  after  語句觸發器：審計DML操作，或者在DML操作以後進行匯總操作
  before 行觸發器：確保資料符合邏輯，使用約束對輸入條件加以限制，比如員工新工資不能低於原來的工資
  after  行觸發器：審計資料變化，比如一個員工工資變化了，把變化了的員工資訊寫入審計表
  
  限制行觸發器：就是在行觸發器中，並不是對所有行都觸發而是加了條件，格式
  create or replace trigger triger_test
  before  update or delete on trigger_tab 
  for each row --行觸發器都的加上這個語句
  where (:old.name='chen')
  declare
  v_name varchar2(200);
  begin
  end;
   
  RAISE_APPLICATION_ERROR 方法介紹： 
  裡面的錯誤碼和內容，都是自訂的。說明是自訂，當然就不是系統中已經命名存在的錯誤類別，是屬於一種自訂事務錯誤類型，才調用此函數。
  error_number_in 之容許從 -20000 到 -20999 之間，這樣就不會與 ORACLE 的任何錯誤碼發生衝突。
  error_msg_in 的長度不能超過 2K，否則截取 2K。
  
  :old.name和:new.name 介紹
  :new.value是name的新值。
  :old.value是name修改前的值。
  :old只對Update或者Delete有，對Insert來說，其之前沒有資料，所以只能用：new 
  當在trigges 體引用old 和new，必須加首碼:，和綁定變數的時候一樣，申明部分不需要。
  
  sql指令碼
  create table trigger_tab(
   userID number not null primary key,
   userName varchar2(200),
   age number,
   address varchar2(200)
  )
  insert into trigger_tab(userID,userName,age,address) values(1,'chen',20,'茶陵');
  insert into trigger_tab(userID,userName,age,address) values(2,'li',15,'悠閑');
  insert into trigger_tab(userID,userName,age,address) values(3,'zh',29,'北京');

  建立觸發器
  create or replace trigger triger_test
    before  update or delete on trigger_tab 
    for each row --行觸發器都的加上這個語句
  declare
    -- local variables here
  begin
    if(:new.age<18)then --:old.age 是更新記錄前的值， :new.age是記錄更新後的值
        RAISE_APPLICATION_ERROR(-20001,'年齡小於18歲，不能刪除和修改');
    end if;
  end triger_test;

  command window 視窗執行sql
  update trigger_tab t  set t.address='長沙' where t.username='li';
  後台列印
  ORA-20000: 年齡小於18歲，不能修改和刪除
  ORA-06512: 在"SCOTT.TRIGER_TEST", line 5
  ORA-04088: 觸發器 'SCOTT.TRIGER_TEST' 執行過程中出錯

 

  java 中調用
  public class TestTriger {
   public static void main(String []args){
    String sqlStr = "update trigger_tab t  set t.address='長沙' where t.username='li'";
    Connection conn = Connector.getConn();
    PreparedStatement pstmt;
    try {
     pstmt = conn.prepareStatement(sqlStr);
     int rowNum = pstmt.executeUpdate();
    } catch (SQLException e) {
     if(e.getErrorCode()==20001){
      String errorM = e.getMessage();
      System.out.println(errorM);
      System.out.println(errorM.split("ORA")[1].replace("-"+e.getErrorCode()+":", ""));
     }
    }
   }
  }
  捕獲到的異常都一個異常Code,這個code 就是在RAISE_APPLICATION_ERROR(-20001,'年齡小於18歲，不能刪除和修改');
  設定的-20001，去掉前面的負號。
  異常資訊得到
  ORA-20000: 年齡小於18歲，不能修改和刪除和修改
  ORA-06512: 在"SCOTT.TRIGER_TEST", line 5
  ORA-04088: 觸發器 'SCOTT.TRIGER_TEST' 執行過程中出錯
  格式化以後就是想到得到的異常資訊了,"年齡小於18歲，不能修改和刪除和修改"。