程式師世界 >> 數據庫知識 >> Oracle數據庫 >> Oracle教程 >> Oracle學習筆記十三觸發器，oracle學習筆記

Oracle學習筆記十三觸發器，oracle學習筆記

編輯：Oracle教程

Oracle學習筆記十三觸發器，oracle學習筆記

簡介

觸發器是當特定事件出現時自動執行的存儲過程，特定事件可以是執行更新的DML語句和DDL語句，觸發器不能被顯式調用。觸發器的功能： 1.自動生成數據 2.自定義復雜的安全權限 3.提供審計和日志記錄 4.啟用復雜的業務邏輯

創建觸發器的語法

CREATE [OR REPLACE] TRIGGER trigger_name
AFTER | BEFORE | INSTEAD OF
[INSERT] [[OR] UPDATE [OF column_list]] 
[[OR] DELETE]
ON table_or_view_name
[REFERENCING {OLD [AS] old / NEW [AS] new}]
[FOR EACH ROW]
[WHEN ( condition ) ]
pl/sql_block;

:new --為一個引用最新的列值;
:old --為一個引用以前的列值; 這兩個變量只有在使用了關鍵字 "FOR EACH ROW"時才存在.

且update語句兩個都有,而insert只有:new ,delect 只有:old;

觸發器由三部分組成：

觸發器語句（事件）　　定義激活觸發器的 DML 事件和 DDL 事件觸發器限制　　執行觸發器的條件，該條件必須為真才能激活觸發器觸發器操作（主體）　　包含一些 SQL 語句和代碼，它們在發出了觸發器語句且觸發限制的值為真時運行

觸發器語句

CREATE OR REPLACE TRIGGER trig_sal
    AFTER UPDATE OF empsal ON salary_records  --在更新 emp_sal 列之後激活觸發器
    …

觸發器限制

        …
    FOR EACH ROW
    WHEN (NEW.empsal>OLD.empsal)    --只有在WHEN子句中的條件得到滿足時，才激活trig_sal 觸發器
    DECLARE
    Sal_diff NUMBER;
    …

觸發器操作

        …
    BEGIN
      sal_diff:=:NEW.empsal-:OLD.empsal;    --如果WHEN子句中的條件得到滿足，將執行BEGIN 塊中的代碼
      DBMS_OUTPUT.PUT_LINE(‘工資差額：’sal_diff);
    END;

BEFORE 觸發器的工作原理

創建觸發器

CREATE OR REPLACE TRIGGER aiu_itemfile
AFTER INSERT
ON itemfile
FOR EACH ROW
BEGIN
  IF (:NEW.qty_hand = 0) THEN
    DBMS_OUTPUT.PUT_LINE('警告：已插入記錄，但數量為零');
  ELSE
    DBMS_OUTPUT.PUT_LINE(‘已插入記錄');
  END IF;
END;

查看表的觸發器

select * from all_triggers where table_name =upper('tbname')

CREATE OR REPLACE TRIGGER TR_SEC_EMP
BEFOR INSERT  OR UPDATE OR DELETE ON EMP2
BEGIN 
 IF TO_CHAR(SYSDATE,'DY', 'nls_date_language=AMERICAN') IN ('SAT', 'SUN') THEN 
RAISE_APPLICATION_ERROR(-20002,’禁止修改數據！’);
END IF;
END;

工資一般來說都是往上調整，寫個觸發器禁止降低工資？

create or replace trigger guo_trigger----創建觸發器 
before update on emp2 ----指明觸發器時機 
for each row ----行觸發器標識 
when (new.sal<old.sal ) ----觸發條件 
begin 
raise_application_error(-20500,'不能給員工減少工資'); 
end;

觸發器類型

觸發器的類型有：

DDL 觸發器　　在模式中執行 DDL 語句時執行數據庫級觸發器　　在發生打開、關閉、登錄和退出數據庫等系統事件時執行 DML 觸發器　　在對表或視圖執行DML語句時執行語句級觸發器　　無論受影響的行數是多少，都只執行一次行級觸發器　　對DML語句修改的每個行執行一次 INSTEAD OF 觸發器　　用於用戶不能直接使用 DML 語句修改的視圖

綜述：

觸發器組成: l 觸發事件：引起觸發器被觸發的事件。例如：DML語句(INSERT, UPDATE, DELETE語句對表或視圖執行數據處理操作)、DDL語句（如CREATE、ALTER、DROP語句在數據庫中創建、修改、刪除模式對象）、數據庫系統事件（如系統啟動或退出、異常錯誤）、用戶事件（如登錄或退出數據庫）。 l 觸發時間：即該TRIGGER 是在觸發事件發生之前（BEFORE）還是之後(AFTER)觸發，也就是觸發事件和該TRIGGER 的操作順序。 l 觸發操作：即該TRIGGER 被觸發之後的目的和意圖，正是觸發器本身要做的事情。例如：PL/SQL 塊。 l 觸發對象：包括表、視圖、模式、數據庫。只有在這些對象上發生了符合觸發條件的觸發事件，才會執行觸發操作。 l 觸發條件：由WHEN子句指定一個邏輯表達式。只有當該表達式的值為TRUE時，遇到觸發事件才會自動執行觸發器，使其執行觸發操作。 l 觸發頻率：說明觸發器內定義的動作被執行的次數。即語句級(STATEMENT)觸發器和行級(ROW)觸發器。

語句級(STATEMENT)觸發器：是指當某觸發事件發生時，該觸發器只執行一次；行級(ROW)觸發器：是指當某觸發事件發生時，對受到該操作影響的每一行數據，觸發器都單獨執行一次。

觸發器不接受參數。 l 一個表上最多可有12個觸發器，但同一時間、同一事件、同一類型的觸發器只能有一個。並各觸發器之間不能有矛盾。 l 在一個表上的觸發器越多，對在該表上的DML操作的性能影響就越大。 l 觸發器最大為32KB。若確實需要，可以先建立過程，然後在觸發器中用CALL語句進行調用。 l 在觸發器的執行部分只能用DML語句（SELECT、INSERT、UPDATE、DELETE），不能使用DDL語句（CREATE、ALTER、DROP）。 l 觸發器中不能包含事務控制語句(COMMIT，ROLLBACK，SAVEPOINT)。因為觸發器是觸發語句的一部分，觸發語句被提交、回退時，觸發器也被提交、回退了。 l 在觸發器主體中調用的任何過程、函數，都不能使用事務控制語句。 l 在觸發器主體中不能申明任何Long和blob變量。新值new和舊值old也不能向表中的任何long和blob列。

l 不同類型的觸發器(如DML觸發器、INSTEAD OF觸發器、系統觸發器)的語法格式和作用有較大區別。

行級觸發器

CREATE TABLE TEST_TRG
          (ID NUMBER, NAME VARCHAR2(20));
CREATE SEQUENCE SEQ_TEST;

CREATE OR REPLACE TRIGGER BI_TEST_TRG
　　BEFORE INSERT OR UPDATE OF ID
　　ON TEST_TRG
　　FOR EACH ROW
BEGIN
--函數UPDATING, DELETING判斷觸發器是由哪個操作觸發的 
     IF INSERTING THEN SELECT SEQ_TEST.NEXTVAL INTO :NEW.ID FROM DUAL;
     ELSE
       RAISE_APPLICATION_ERROR(-20020, '不允許更新ID值！');
     END IF;
END;

修改數據前備份員工工資

CREATE OR REPLACE TIGGER TR_SAL_BACK AFFTER UPDATE OF SAL ON EMP2 FOR EACH ROW 
DECLARE 
　　V_TEM INT ;
BEGIN 
　　SELECT COUNT(*) INTO V_TEMP FROM NEW_BACK WHERE ENAME =:OLD.ENAME;
　　IF V_TEMP =0 THEN 
　　　　INSERT INTO NEW_BACK VALUES (:OLD.ENAME,:OLD.SAL, :NEW.SAL,SYSDATE);
　　ELSE 
　　　　UPDATE NEW_BACK VALUES  SET OLDSAL=:OLDSAL, NEWSAL=:NEW.SAL,TIME=SYSDATE WHERE NAME=:OLD.ENAME;
　　END IF; 
END;

建立一個觸發器, 當職工表 emp 表被刪除一條記錄時，把被刪除記錄寫到職工表刪除日志表中去。

CREATE TABLE emp_his AS SELECT * FROM EMP WHERE 1=2; 

CREATE OR REPLACE TRIGGER tr_del_emp BEFORE 
DELETE --指定觸發時機為刪除操作前觸發
   ON scott.emp 
   FOR EACH ROW   --說明創建的是行級觸發器 
BEGIN
   --將修改前數據插入到日志記錄表 del_emp ,以供監督使用。
   INSERT INTO emp_his(deptno , empno, ename , job ,mgr , sal , comm , hiredate )
       VALUES( :old.deptno, :old.empno, :old.ename , :old.job,:old.mgr, :old.sal, :old.comm, :old.hiredate );
END;

DELETE emp WHERE empno=7788;
DROP TABLE emp_his;
DROP TRIGGER del_emp;

語句級觸發器

CREATE OR REPLACE TRIGGER trgdemo
   AFTER INSERT OR UPDATE OR DELETE
   ON order_master
BEGIN
   IF UPDATING THEN
     DBMS_OUTPUT.PUT_LINE('已更新 ORDER_MASTER 中的數據');
   ELSIF DELETING THEN
     DBMS_OUTPUT.PUT_LINE('已刪除 ORDER_MASTER 中的數據');
   ELSIF INSERTING THEN
     DBMS_OUTPUT.PUT_LINE('已在 ORDER_MASTER 中插入數據');
   END IF;
END;

行級與語句區別

行觸發器和語句觸發器的區別表現在：行觸發器要求當一個DML語句操走影響數據庫中的多行數據時，對於其中的每個數據行，只要它們符合觸發約束條件，均激活一次觸發器；而語句觸發器將整個語句操作作為觸發事件，當它符合約束條件時，激活一次觸發器。當省略FOR EACH ROW 選項時，BEFORE 和AFTER 觸發器為語句觸發器，而INSTEAD OF 觸發器則只能為行觸發器。

INSTEAD OF 觸發器

CREATE OR REPLACE TRIGGER upd_ord_view
　　INSTEAD OF UPDATE ON ord_view
　　FOR EACH ROW
BEGIN
　　UPDATE order_master
　　SET vencode=:NEW.vencode 
　　　　WHERE orderno = :NEW.orderno;
　　DBMS_OUTPUT.PUT_LINE('已激活觸發器');
END;

模式觸發器

CREATE TABLE dropped_obj 
(
　　obj_name VARCHAR2(30),
　　obj_type VARCHAR2(20),
　　drop_date DATE
);


CREATE OR REPLACE TRIGGER log_drop_obj
　　AFTER DROP ON SCHEMA
BEGIN
　　INSERT INTO dropped_obj
　　VALUES( ORA_DICT_OBJ_NAME, ORA_DICT_OBJ_TYPE, SYSDATE);
END;

啟用、禁用和刪除觸發器

啟用和禁用觸發器

ALTER TRIGGER aiu_itemfile DISABLE;

ALTER TRIGGER aiu_itemfile ENABLE;

刪除觸發器

DROP TRIGGER aiu_itemfile;

查看有關觸發器的信息

USER_TRIGGERS 數據字典視圖包含有關觸發器的信息

SELECT TRIGGER_NAME FROM USER_TRIGGERS
WHERE TABLE_NAME='EMP';



SELECT TRIGGER_TYPE, TRIGGERING_EVENT, WHEN_CLAUSE
FROM USER_TRIGGERS
WHERE TRIGGER_NAME = 'BIU_EMP_DEPTNO';