觸發(fā)器是指被隱含執(zhí)行的存儲過程

一、創(chuàng)建DML觸發(fā)器（before/after）

1、行觸發(fā)器:

當(dāng)一個DML操作影響DB中的多行時，對于其中復(fù)合觸發(fā)條件的每行均觸發(fā)一次（for each row）

例1： 建立一個觸發(fā)器, 當(dāng)職工表 emp 表被刪除一條記錄時，把被刪除記錄寫到職工表刪除日志表中去。

CREATE OR REPLACE TRIGGER tr_del_emp   BEFORE DELETE --指定觸發(fā)時機(jī)為刪除操作前觸發(fā)   ON scott.emp   FOR EACH ROW   --說明創(chuàng)建的是行級觸發(fā)器BEGIN   --將修改前數(shù)據(jù)插入到日志記錄表 del_emp ,以供監(jiān)督使用。   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;

例2：級聯(lián)更新：利用行觸發(fā)器實(shí)現(xiàn)級聯(lián)更新。在修改了主表regions中的region_id之后（AFTER），級聯(lián)的、自動的更新子表countries表中原來在該地區(qū)的國家的region_id

CREATE OR REPLACE TRIGGER tr_reg_couAFTER update OF region_idON regionsFOR EACH ROWBEGIN DBMS_OUTPUT.PUT_LINE("舊的region_id值是"||:old.region_id  ||"、新的region_id值是"||:new.region_id); UPDATE countries SET region_id = :new.region_id WHERE region_id = :old.region_id;END;

例3：限定只對部門號為80的記錄進(jìn)行行觸發(fā)器操作。

CREATE OR REPLACE TRIGGER tr_emp_sal_commBEFORE UPDATE OF salary, commission_pct       OR DELETEON HR.employeesFOR EACH ROWWHEN (old.department_id = 80)BEGIN CASE     WHEN UPDATING ("salary") THENIF :NEW.salary < :old.salary THEN     RAISE_APPLICATION_ERROR(-20001, "部門80的人員的工資不能降");END IF;     WHEN UPDATING ("commission_pct") THEN  IF :NEW.commission_pct < :old.commission_pct THEN   RAISE_APPLICATION_ERROR(-20002, "部門80的人員的獎金不能降");END IF;     WHEN DELETING THEN  RAISE_APPLICATION_ERROR(-20003, "不能刪除部門80的人員記錄");     END CASE;END;  /*實(shí)例：UPDATE employees SET salary = 8000 WHERE employee_id = 177;DELETE FROM employees WHERE employee_id in (177,170);*/

2、語句觸發(fā)器：

將整個DML語句作為觸發(fā)條件，當(dāng)它符合約束條件時，激活一次觸發(fā)器。

限制對Departments表修改（包括INSERT,DELETE,UPDATE）的時間范圍，即不允許在非工作時間修改departments表。

CREATE OR REPLACE TRIGGER tr_dept_timeBEFORE INSERT OR DELETE OR UPDATEON departmentsBEGIN IF (TO_CHAR(sysdate,"DAY") IN ("星期六", "星期日")) OR (TO_CHAR(sysdate, "HH24:MI") NOT BETWEEN "08:30" AND "18:00") THEN     RAISE_APPLICATION_ERROR(-20001, "不是上班時間，不能修改departments表"); END IF;END;

二、創(chuàng)建替代（instead of ）觸發(fā)器

用于對視圖（沒有指定WITH CHECK OPTION選項）的DML觸發(fā)。

• 只能被創(chuàng)建在視圖上。
• 不能指定BEFORE 或 AFTER選項。
• FOR EACH ROW子可是可選的，即INSTEAD OF觸發(fā)器只能在行級上觸發(fā)、或只能是行級觸發(fā)器，沒有必要指定。

創(chuàng)建INSTEAD_OF觸發(fā)器來為 DELETE 操作執(zhí)行所需的處理，即刪除EMP表中所有基準(zhǔn)行：

CREATE OR REPLACE TRIGGER emp_view_delete   INSTEAD OF DELETE ON emp_view FOR EACH ROWBEGIN   DELETE FROM emp WHERE deptno= :old.deptno;END emp_view_delete;

三、創(chuàng)建系統(tǒng)事件觸發(fā)器（on schema/on database）

1、當(dāng)建立在模式(SCHEMA)之上時，只有模式所指定用戶的DDL操作和它們所導(dǎo)致的錯誤才激活觸發(fā)器, 默認(rèn)時為當(dāng)前用戶模式。

例1：創(chuàng)建觸發(fā)器，存放有關(guān)事件信息。

--創(chuàng)建觸犯發(fā)器CREATE OR REPLACE TRIGGER tr_ddlAFTER DDL ON SCHEMABEGIN   INSERT INTO ddl_event VALUES   (systimestamp,ora_sysevent, ora_login_user,    ora_dict_obj_type, ora_dict_obj_name);END tr_ddl;

2、當(dāng)建立在數(shù)據(jù)庫(DATABASE)之上時，該數(shù)據(jù)庫所有用戶的DDL操作和他們所導(dǎo)致的錯誤，以及數(shù)據(jù)庫的啟動和關(guān)閉均可激活觸發(fā)器。

要在數(shù)據(jù)庫之上建立觸發(fā)器時，要求用戶具有ADMINISTER DATABASE TRIGGER權(quán)限。

例1：創(chuàng)建登錄觸發(fā)器。

CREATE OR REPLACE TRIGGER tr_logonAFTER LOGON ON DATABASEBEGIN   INSERT INTO log_event (user_name, address, logon_date)   VALUES (ora_login_user, ora_client_ip_address, systimestamp);END tr_logon;

四、重新編譯觸發(fā)器

ALTER TRIGGER  trigger  COMPILE

刪除觸發(fā)器：當(dāng)刪除表或視圖時，建立在這些對象上的觸發(fā)器也隨之刪除。

DROP TRIGGER trigger_name;

禁用或啟用觸發(fā)器

ALTER TRIGGER emp_view_delete DISABLE| ENABLE;--使表EMP 上的所有TRIGGER 失效：ALTER TABLE emp DISABLE ALL TRIGGERS;

觸發(fā)器和數(shù)據(jù)字典

相關(guān)數(shù)據(jù)字典：USER_TRIGGERS、ALL_TRIGGERS、DBA_TRIGGERS

Oracle 字符集

到此這篇關(guān)于Oracle觸發(fā)器trigger的文章就介紹到這了。希望對大家的學(xué)習(xí)有所幫助，也希望大家多多支持。