Oracle專題16之觸發器

1、觸發器的概述

a、觸發器是什么？

• 觸發器是指存放在數據庫中，并且被隱含執行的存儲過程。
• 當發生特定事件時，Oracle會自動執行觸發器的相應代碼。

  b、觸發器的類型

• DML觸發器；DDL觸發器；替代（instead of）觸發器；系統觸發器。

  c、觸發器的組成

  1、觸發事件：即在何種情況下觸發TRIGGER。
  2、觸發時間：即該TRIGGER是在觸發事件發生之前（BEFORE）還是之后（AFTER）觸發。
  3、觸發器本身：即該TRIGGER被觸發之后的目的和意圖，正是觸發器本省要做的事情。
  4、觸發頻率：說明觸發器內定義的動作被執行的次數。

  d、創建第一個觸發器

• 示例：每次執行刪除操作之后，都會信息提示：“這是刪除操作！”

  SQL> --當對emp01表執行刪除操作之后，它就會出現一個提示信息，提示：這是刪除操作
  SQL> CREATE TRIGGER first_trigger
      2  AFTER DELETE
      3  ON emp01
      4  BEGIN
      5     DBMS_OUTPUT.put_line('這是刪除操作！');
      6  END;
      7  /
  
  Trigger created
  
  SQL> SET SERVEROUTPUT ON
  SQL> DELETE FROM emp01 WHERE empno = 7782;
  
  這是刪除操作！
  1 row deleted

  2、DDL觸發器

  a、什么是DDL觸發器？

• 當創建、修改或者刪除數據庫對象時，也會引起相應的觸發器操作事件，而此時就可以利用觸發器來對這些數據庫對象的DDL操作進行監控。

  b、創建DDL觸發器的語法格式

  CREATE [OR REPLACE] TRIGGER 觸發器的名稱
  [BEFORE  | AFTER | INTEAD OF ] [DDL事件] ON [DATABASE | SCHEMA] 
  [WHEN 觸發條件] 
  [DECLARE] 
      [程序的聲明部分;] 
  BEGIN
      程序的代碼部分
  END;
  /

  c、DDL事件描述和觸發時機

  

  d、代碼示例1：禁止scott用戶的觸發器操作

• 禁止scott用戶的DDL操作

  SQL> CREATE OR REPLACE TRIGGER scott_trigger
      2  BEFORE DDL
      3  ON SCHEMA
      4  BEGIN
      5     RAISE_APPLICATION_ERROR(-20005, 'scott用戶禁止所有的DDL操作');
      6  END;
      7  /
  
  Trigger created
  
  SQL> CREATE SEQUENCE test_seq;
  CREATE SEQUENCE test_seq
  
  ORA-00604: 遞歸 SQL 級別 1 出現錯誤
  ORA-20005: scott用戶禁止所有的DDL操作
  ORA-06512: 在 line 2

• 注意：RAISE_APPLICAITON_ERROR是用來測試的異常處理，能夠將應用程序專有的錯誤從服務器端轉達到客戶端應用程序中（其他機器上的SQLPlus或者其他前臺開發語言），其存儲過程有兩個參數，如：

  RAISE_APPLICAITON_ERROR(error_number_in IN NUMBER, error_msg_in IN VARCHAR2);
  -- error_msg_in的長度不能超過2k，超過2k后會進行截取

  e、代碼示例2：實現對數據庫對象操作的日志記錄

• 分解成三個步驟：
  1、創建數據庫對象DDL操作日志記錄表；2、創建實現對數據庫對象DDL操作記錄的觸發器；3、測試。

  SQL> connect system/02000059 as sysdba;
  Connected to Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 
  Connected as system AS SYSDBA
  
  SQL> CREATE TABLE object_log(   --創建數據庫對象DDL操作日志記錄表
      2  logid number constraint pk_logid primary key,
      3  operatedate date not null,
      4  objecttype varchar2(50) not null,
      5  objectowner varchar2(50) not null);
  
  Table created
  
  SQL> CREATE SEQUENCE object_log_seq;  
  
  Sequence created
  
  SQL> CREATE OR REPLACE TRIGGER ojbect_trigger  --創建實現對數據庫對象DDL操作記錄的觸發器
      2  AFTER CREATE OR DROP OR ALTER
      3  ON DATABASE
      4  BEGIN
      5     INSERT INTO object_log(logid, operatedate, objecttype, objectowner) VALUES (object_log_seq.nextval, sysdate, ora_dict_obj_type, ora_dict_obj_owner);
      6  END;
      7  /
  
  Trigger created
  
  SQL> CREATE SEQUENCE test_seq;  --測試
  
  Sequence created
  
  SQL> select * from object_log;
  
           LOGID OPERATEDATE OBJECTTYPE                                         OBJECTOWNER
  ---------- ----------- -------------------------------------------------- --------------------------------------------------
                   1 2017/12/21  SEQUENCE                                           SYS

• 注意：此DDL觸發器事件示例中的使用了兩個屬性函數（之前未使用）：
• ORA_DICT_OBJ_OWNER：觸發DDL的數據庫對象的用戶；ORA_DICT_OBJ_TYPE：觸發DDL的數據庫對象的類型。

  3、DML觸發器

  a、什么是DML觸發器？

• DML觸發器是指基于DML操作所建立的觸發器。

  b、DML觸發器的作用

• DML觸發器可用于實現數據安全保護、數據審計、數據完整性、參照完整性、數據復制等功能。

  c、DML觸發器類型

• 包括語句觸發器和行觸發器。
  1、語句觸發器：在指定操作語句之前或者之后執行一次，不管這條語句影響了多少行。（針對行）
  2、行觸發器：觸發語句作用的每一條記錄都被觸發，在行級觸發器中使用:old和:new偽記錄變量，識別值的狀態。（針對表）
• :old表示操作該行之前，這一行的值；:new 表示操作該行之后，這一行的值。

  d、創建DML觸發器的語法格式

  CREATE [OR REPLACE] TRIGGER trigger_name 
  {BEFORE | AFTER}
  {DELETE | INSERT | UPDATE [ OF 列名]}
  ON 表名
  [FOR EACH ROW [WHEN (條件）])
  PLSQL塊

  e、示例1：實現數據安全保護（數據的安全性檢查）

• 示例：禁止在休息日（周六、周日）改變emp表的數據。

• 分析：1、使用to_char(sysdate, 'day')函數；2、采用語句觸發器。

  SQL> CREATE OR REPLACE TRIGGER emp_trigger
      2  BEFORE INSERT OR UPDATE OR DELETE
      3  ON emp
      4  BEGIN
      5     IF to_char(sysdate, 'day') IN ('星期六', '星期日') THEN
      6        RAISE_APPLICATION_ERROR(-20006, '不能在休息日改變員工信息！');
      7     END IF;
      8  END;
      9  /
  
  Trigger created
  
  SQL> DELETE FROM emp WHERE empno = 7369;
  DELETE FROM emp WHERE empno = 7369
  
  ORA-20006: 不能在休息日改變員工信息！
  ORA-06512: 在 "SCOTT.EMP_TRIGGER", line 3
  ORA-04088: 觸發器 'SCOTT.EMP_TRIGGER' 執行過程中出錯

  f、示例2：實現數據審計

• 示例：審計員工信息表數據的變化，審計刪除時間，以及被刪除的雇員名。

• 使用SQL WINDOW窗口：（逐步執行）

  --創建審計表
  CREATE TABLE delete_emp_audit(
               name VARCHAR2(10),
               delete_time DATE
  );
  
  --創建觸發器
  CREATE OR REPLACE TRIGGER del_emp_trigger
  AFTER DELETE ON emp
  FOR EACH ROW
  BEGIN
      INSERT INTO delete_emp_audit VALUES(:old.ename, SYSDATE); --插入被刪除員工的姓名和當前時間
  END;
  
  --測試
  DELETE FROM emp WHERE empno = 7499;
  SELECT * FROM delete_emp_audit;

• 在SQL語句和PLSQL語句中，old和new偽記錄變量需要加上冒號:；而在WEHN這樣的限制性條件語句當中，則不需要加上冒號:。

  g、示例3：實現數據完整性（數據確認）

• 數據完整性用于確保數據滿足商業邏輯或者企業規則。
• 實現數據完整性首選約束，約束無法實現的，可以使用觸發器實現數據完整性。

• 示例：比如要求員工漲后工資不能低于原來的工資，并且所漲的工資不能超過原工資的50%。

  SQL> CREATE OR REPLACE TRIGGER tr_check_sal
      2  BEFORE UPDATE OF sal ON emp
      3  FOR EACH ROW 
      4  WHEN (new.sal < old.sal OR new.sal > old.sal * 1.5)
      5  BEGIN
      6       RAISE_APPLICATION_ERROR(-20028, '工資直升不降，并且升幅不能超過50%');
      7  END;
      8  /
  
  Trigger created
  
  SQL> UPDATE emp SET sal = sal * 1.8 WHERE empno = 7902;
  UPDATE emp SET sal = sal * 1.8 WHERE empno = 7902
  
  ORA-20028: 工資直升不降，并且升幅不能超過50%
  ORA-06512: 在 "SCOTT.TR_CHECK_SAL", line 2
  ORA-04088: 觸發器 'SCOTT.TR_CHECK_SAL' 執行過程中出錯

  h、示例4：實現參照完整性（比如級聯更新）

• 為了實現級聯刪除，可以在定義外部鍵約束時指定ON DELETE CASCADE關鍵字。
• 但是使用約束卻不能實現級聯更新，為了實現級聯更新，需要使用觸發器。

• 示例：級聯更新DEPT表的主鍵以及EMP表的外部鍵列。

  CREATE OR REPLACE TRIGGER upd_cascade_trigger
  AFTER UPDATE OF deptno
  ON dept
  FOR EACH ROW 
  BEGIN
      UPDATE emp SET deptno = :new.deptno WHERE deptno = :old.deptno;
  END;
  
  --測試
  UPDATE dept SET deptno = 50 WHERE deptno = 10;
  SELECT deptno , ename FROM emp WHERE deptno = 50;

  4、INSTEAD OF觸發器

  a、什么是INSTEAD OF觸發器（替代觸發器）？

• 適用于視圖上的觸發器

  b、為什么使用INSTEAD OF觸發器？

• 在簡單視圖上往往可以執行INSET、UPDATE、DELETE操作。
• 但是在復雜視圖上執行INSET、UPDATE、DELETE操作時有限制。所以為了在這些復雜的視圖上執行DML操作，需要建立替代觸發器。

  c、INSTEAD OF 觸發器的限制

• 替代觸發器只適用于視圖。
• 替代觸發器不能指定BEFORE和AFTER選項。
• 不能在具有WITH CHECK OPTION選項的視圖上建立替代觸發器。

• 替代觸發器必須包含FOR EACH ROW選項。

  d、示例代碼

  1、創建emp_dept視圖：

  SQL> CREATE OR REPLACE VIEW emp_dept
      2  AS 
      3  SELECT d.deptno, d.dname, e.empno, e.ename
      4  FROM dept d, emp e
      5  WHERE d.deptno = e.deptno;
  
  View created

  2、當沒有創建替代觸發器時，對emp_dept視圖插入數據出錯：

  SQL> INSERT INTO emp_dept VALUES(50, 'DEVELOPMENT', 2222, 'ALICE');
  INSERT INTO emp_dept VALUES(50, 'DEVELOPMENT', 2222, 'ALICE')
  
  ORA-01779: 無法修改與非鍵值保存表對應的列

  3、創建emp_dept視圖（復雜視圖）的替代觸發器：

  CREATE OR REPLACE TRIGGER instead_of_trigger
  INSTEAD OF 
  INSERT
  ON emp_dept
  FOR EACH ROW
  DECLARE
      v_temp INT;
  BEGIN
      SELECT COUNT(*) INTO v_temp FROM dept WHERE deptno = :new.deptno;
      IF v_temp = 0 THEN
          INSERT INTO dept(deptno, dname) VALUES(:new.deptno, :new.dname);
      END IF;
      SELECT COUNT(*) INTO v_temp FROM emp WHERE empno = :new.empno;
      IF v_temp = 0 THEN
          INSERT INTO emp(empno, ename, deptno) VALUES(:new.empno, :new.ename, :new.deptno);
      END IF;
  END;

  4、對emp_dept視圖進行插入操作：

      SQL> INSERT INTO emp_dept VALUES(50, 'DEVELOPMENT', 2222, 'ALICE');
  
      1 row inserted
  
      SQL> SELECT * FROM EMP WHERE empno = 2222;
  
      EMPNO ENAME      JOB         MGR HIREDATE          SAL      COMM DEPTNO
      ----- ---------- --------- ----- ----------- --------- --------- ------
       2222 ALICE                                                          50
  
      SQL> SELECT *FROM DEPT WHERE deptno = 50;
  
      DEPTNO DNAME          LOC
      ------ -------------- -------------
              50 ACCOUNTING     NEW YORK
  
      SQL> SELECT * FROM emp_dept WHERE empno = 2222;
  
      DEPTNO DNAME          EMPNO ENAME
      ------ -------------- ----- ----------
              50 ACCOUNTING      2222 ALICE

  5、系統觸發器

  a、什么是系統觸發器？

• 系統觸發器是由特定系統事件所觸發的觸發器。（需要注意的是，系統觸發器是要有系統用戶來創建的）

• 系統事件是指與例程或者方案相關的數據庫事件，它包括STARTUP、SHUTDOWN、DB_ROLE_CHANGE和SERVERERROR的四種事件。
  1、STARTUP事件觸發器是在啟動數據庫后觸發。
  2、SHUTDOWN事件觸發器在關閉數據之前觸發。
  3、DB_ROLE_CHANGE事件觸發器在改變角色后第一次打開數據庫時觸發。
  4、SERVERERROR事件觸發器在發生Oracle錯誤時觸發。

  b、示例代碼

  1、創建記錄發生的數據庫系統EVENT_TALBE事件表：

  CREATE TABLE event_table(
               event VARCHAR2(50),
               event_time date
               );

  2、創建系統觸發器：

      SQL> CREATE OR REPLACE TRIGGER startup_trigger
          2  AFTER STARTUP ON DATABASE
          3  BEGIN
          4        INSERT INTO event_table VALUES(ORA_SYSEVENT, SYSDATE);
          5  END;
          6  /
  
      Trigger created