1、觸發(fā)器的概述

a、觸發(fā)器是什么？

• 觸發(fā)器是指存放在數(shù)據(jù)庫中，并且被隱含執(zhí)行的存儲過程。
• 當(dāng)發(fā)生特定事件時，Oracle會自動執(zhí)行觸發(fā)器的相應(yīng)代碼。

  b、觸發(fā)器的類型

• DML觸發(fā)器；DDL觸發(fā)器；替代（instead of）觸發(fā)器；系統(tǒng)觸發(fā)器。

  c、觸發(fā)器的組成

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

  創(chuàng)新互聯(lián)公司專注為客戶提供全方位的互聯(lián)網(wǎng)綜合服務(wù)，包含不限于成都網(wǎng)站制作、成都做網(wǎng)站、山西網(wǎng)絡(luò)推廣、小程序設(shè)計(jì)、山西網(wǎng)絡(luò)營銷、山西企業(yè)策劃、山西品牌公關(guān)、搜索引擎seo、人物專訪、企業(yè)宣傳片、企業(yè)代運(yùn)營等，從售前售中售后，我們都將竭誠為您服務(wù)，您的肯定，是我們最大的嘉獎；創(chuàng)新互聯(lián)公司為所有大學(xué)生創(chuàng)業(yè)者提供山西建站搭建服務(wù)，24小時服務(wù)熱線：13518219792，官方網(wǎng)址：www.cdcxhl.com

  d、創(chuàng)建第一個觸發(fā)器

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

  SQL> --當(dāng)對emp01表執(zhí)行刪除操作之后，它就會出現(xiàn)一個提示信息，提示：這是刪除操作
  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觸發(fā)器

  a、什么是DDL觸發(fā)器？

• 當(dāng)創(chuàng)建、修改或者刪除數(shù)據(jù)庫對象時，也會引起相應(yīng)的觸發(fā)器操作事件，而此時就可以利用觸發(fā)器來對這些數(shù)據(jù)庫對象的DDL操作進(jìn)行監(jiān)控。

  b、創(chuàng)建DDL觸發(fā)器的語法格式

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

  c、DDL事件描述和觸發(fā)時機(jī)

  

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

• 禁止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 出現(xiàn)錯誤
  ORA-20005: scott用戶禁止所有的DDL操作
  ORA-06512: 在 line 2

• 注意：RAISE_APPLICAITON_ERROR是用來測試的異常處理，能夠?qū)?yīng)用程序?qū)Ｓ械腻e誤從服務(wù)器端轉(zhuǎn)達(dá)到客戶端應(yīng)用程序中（其他機(jī)器上的SQLPlus或者其他前臺開發(fā)語言），其存儲過程有兩個參數(shù)，如：

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

  e、代碼示例2：實(shí)現(xiàn)對數(shù)據(jù)庫對象操作的日志記錄

• 分解成三個步驟：
  1、創(chuàng)建數(shù)據(jù)庫對象DDL操作日志記錄表；2、創(chuàng)建實(shí)現(xiàn)對數(shù)據(jù)庫對象DDL操作記錄的觸發(fā)器；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(   --創(chuàng)建數(shù)據(jù)庫對象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  --創(chuàng)建實(shí)現(xiàn)對數(shù)據(jù)庫對象DDL操作記錄的觸發(fā)器
      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觸發(fā)器事件示例中的使用了兩個屬性函數(shù)（之前未使用）：
• ORA_DICT_OBJ_OWNER：觸發(fā)DDL的數(shù)據(jù)庫對象的用戶；ORA_DICT_OBJ_TYPE：觸發(fā)DDL的數(shù)據(jù)庫對象的類型。

  3、DML觸發(fā)器

  a、什么是DML觸發(fā)器？

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

  b、DML觸發(fā)器的作用

• DML觸發(fā)器可用于實(shí)現(xiàn)數(shù)據(jù)安全保護(hù)、數(shù)據(jù)審計(jì)、數(shù)據(jù)完整性、參照完整性、數(shù)據(jù)復(fù)制等功能。

  c、DML觸發(fā)器類型

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

  d、創(chuàng)建DML觸發(fā)器的語法格式

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

  e、示例1：實(shí)現(xiàn)數(shù)據(jù)安全保護(hù)（數(shù)據(jù)的安全性檢查）

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

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

  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: 觸發(fā)器 'SCOTT.EMP_TRIGGER' 執(zhí)行過程中出錯

  f、示例2：實(shí)現(xiàn)數(shù)據(jù)審計(jì)

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

• 使用SQL WINDOW窗口：（逐步執(zhí)行）

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

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

  g、示例3：實(shí)現(xiàn)數(shù)據(jù)完整性（數(shù)據(jù)確認(rèn)）

• 數(shù)據(jù)完整性用于確保數(shù)據(jù)滿足商業(yè)邏輯或者企業(yè)規(guī)則。
• 實(shí)現(xiàn)數(shù)據(jù)完整性首選約束，約束無法實(shí)現(xiàn)的，可以使用觸發(fā)器實(shí)現(xiàn)數(shù)據(jù)完整性。

• 示例：比如要求員工漲后工資不能低于原來的工資，并且所漲的工資不能超過原工資的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: 觸發(fā)器 'SCOTT.TR_CHECK_SAL' 執(zhí)行過程中出錯

  h、示例4：實(shí)現(xiàn)參照完整性（比如級聯(lián)更新）

• 為了實(shí)現(xiàn)級聯(lián)刪除，可以在定義外部鍵約束時指定ON DELETE CASCADE關(guān)鍵字。
• 但是使用約束卻不能實(shí)現(xiàn)級聯(lián)更新，為了實(shí)現(xiàn)級聯(lián)更新，需要使用觸發(fā)器。

• 示例：級聯(lián)更新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觸發(fā)器

  a、什么是INSTEAD OF觸發(fā)器（替代觸發(fā)器）？

• 適用于視圖上的觸發(fā)器

  b、為什么使用INSTEAD OF觸發(fā)器？

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

  c、INSTEAD OF 觸發(fā)器的限制

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

• 替代觸發(fā)器必須包含F(xiàn)OR EACH ROW選項(xiàng)。

  d、示例代碼

  1、創(chuàng)建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、當(dāng)沒有創(chuàng)建替代觸發(fā)器時，對emp_dept視圖插入數(shù)據(jù)出錯：

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

  3、創(chuàng)建emp_dept視圖（復(fù)雜視圖）的替代觸發(fā)器：

  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視圖進(jìn)行插入操作：

      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、系統(tǒng)觸發(fā)器

  a、什么是系統(tǒng)觸發(fā)器？

• 系統(tǒng)觸發(fā)器是由特定系統(tǒng)事件所觸發(fā)的觸發(fā)器。（需要注意的是，系統(tǒng)觸發(fā)器是要有系統(tǒng)用戶來創(chuàng)建的）

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

  b、示例代碼

  1、創(chuàng)建記錄發(fā)生的數(shù)據(jù)庫系統(tǒng)EVENT_TALBE事件表：

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

  2、創(chuàng)建系統(tǒng)觸發(fā)器：

      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