Oracle 筆記（九）、觸發器

1、觸發器一旦建立就會立刻生效，有時可能需要臨時禁用觸發器，最常見的原因就是涉及資料加載。

ALTER TRIGGER trigger_name [ENABLE | DISABLE];

    2、Oracle 觸發器裡兩個重要的記憶體邏輯表 :old 和 :new。:old 表儲存的是在DML語句之前的資料，:new 表儲存的是在DML語句建立的資料。

        old    new

INSERT    -    √

DELETE    √    -

UPDATE    √    √

    3、在觸發器語句中不能顯式的送出/復原事務。

行級觸發器對 DML 語句影響的每個行執行一次。主要應用保持資料完整性。

例：兩表級聯更新，修改部門表編号的同時也修改員工表的部門編号： CREATE OR REPLACE TRIGGER update_dept   /* 行級觸發器，在更新部門表操作後觸發 */   AFTER UPDATE ON deptment   FOR EACH ROW BEGIN   /* new、old 表的有效利用，把舊表的id列值 更新為 新表的id列值 */   UPDATE emp SET id=:new.id WHERE id=:old.id; END; UPDATE deptment SET id='yy' WHERE id='01'; SELECT * FROM deptment; SELECT * FROM emp; 插入時利用觸發器+序列實作整型字段的自增： CREATE OR REPLACE TRIGGER set_no   BEFORE INSERT ON auto DECLARE    sn number(5);   /* 觸發器預處理序列的值 */   SELECT myseq.nextval INTO sn FROM dual;   :NEW.a := sn; INSERT INTO auto VALUES(21,'dtt'); SELECT * FROM auto; /* 插入時表a列由觸發器産生的值替代使用者的輸入 */

隻與語句有關，與行無關，不涉及資料完整性問題。

例如：利用觸發器記錄，記錄某表中使用者的操作（日志處理）。 CREATE OR REPLACE TRIGGER dm1_aa   AFTER INSERT OR DELETE OR UPDATE ON aa   IF INSERTING THEN     INSERT INTO mylog VALUES(user,sysdate,'I');   ELSEIF DELETING THEN     INSERT INTO mylog VALUES(user,sysdate,'D');   ELSE     INSERT INTO mylog VALUES(user,sysdate,'U');   END IF;

ps.能否記錄多個表？"after insert or delete or update on t1,t2" 出錯。

解決Oracle視圖中多表更新的限制，隻能在視圖中使用，屬于行級觸發器。

例如：在視圖中插入新的部門同時插入其所屬的新員工： CREATE OF REPLACE TRIGGER tr_v_e_d   /* 在視圖上建立替換觸發器 */   INSTEAD OF INSERT ON v_emp_dept   /* 替換觸發器先插入部門表的資訊，然後再插入其所屬員工表的資訊 */   INSERT INTO deptment VALUES(:new.id, :new.name);   INSERT INTO emp(eid, ename, sex, id) VALUES(:new.eid, :new.ename, :new.sex, :new.d); INSERT INTO v_emp_dept VALUES('456', 'test', 'f', '33', 'hg'); SELECT * FROM v_emp_dept;

可以在模式對象的操作上建立的觸發器，如 CREATE、ALTER、DROP、GRANT、REVOKE 和 TRUNCATE 等DDL語句。

[BEFORE | AFTER] trigger_event ON [schema.]SCHEMA

例如：對使用者所删除的所有對象進行日志記錄 CREATE OR REPLACE TRIGGER log_drop_obj   AFTER DROP ON SCHEMA   /* 記錄操作類型、操作對象、操作時間 */   INSERT INTO dropped_obj VALUES(ORA_DICT_OBJ_NAME, ORA_DICT_TYPE, SYSDATE); CREATE TABLE for_drop(x char); DROP TABLE for_drop; SELECT * FROM dropped_obj;

可以建立在資料庫事件上的觸發器，包括啟動、關閉、伺服器錯誤、登入和登出等。這些事件都是執行個體範圍内的，不與特定的表或視圖關聯。

CREATE OR REPLACE TRIGGER system_startup   AFTER STARTUP ON DATABASE   ...

本文轉自 qvodnet 51CTO部落格，原文連結:http://blog.51cto.com/bks2015/1982993