共計 5205 個字符，預(yù)計需要花費 14 分鐘才能閱讀完成。

自動寫代碼機器人，免費開通

如何在 Oracle 中使用觸發(fā)器？針對這個問題，這篇文章詳細介紹了相對應(yīng)的分析和解答，希望可以幫助更多想解決這個問題的小伙伴找到更簡單易行的方法。

觸發(fā)器類型

觸發(fā)器在數(shù)據(jù)庫里以獨立的對象存儲，它與存儲過程和函數(shù)不同的是，存儲過程與函數(shù)需要用戶顯示調(diào)用才執(zhí)行，而觸發(fā)器是由一個事件來啟動運行。即觸發(fā)器是當某個事件發(fā)生時自動地隱式運行。并且，觸發(fā)器不能接收參數(shù)。所以運行觸發(fā)器就叫觸發(fā)或點火（firing）。ORACLE 事件指的是對數(shù)據(jù)庫的表進行的 INSERT、UPDATE 及 DELETE 操作或?qū)σ晥D進行類似的操作。ORACLE 將觸發(fā)器的功能擴展到了觸發(fā) ORACLE，如數(shù)據(jù)庫的啟動與關(guān)閉等。所以觸發(fā)器常用來完成由數(shù)據(jù)庫的完整性約束難以完成的復(fù)雜業(yè)務(wù)規(guī)則的約束，或用來監(jiān)視對數(shù)據(jù)庫的各種操作，實現(xiàn)審計的功能。

開發(fā)中肯定會用到 Oracle 的觸發(fā)器，本文進行詳細講解。

這里實例中用到的主要是 Oracle 中 scott 用戶下的 emp 以及 dept 表，數(shù)據(jù)如下

一、觸發(fā)器概念

1、概念：

觸發(fā)器的本質(zhì)是一個存儲過程，顧名思義發(fā)生特定事件時 Oracle 會執(zhí)行觸發(fā)器中的代碼。細分它的組成可以分為 3 個部分：第一部分在什么條件下觸發(fā)器會執(zhí)行，即觸發(fā)器被觸發(fā)的事件。第二部分在什么時間點執(zhí)行觸發(fā)器即觸發(fā)器的發(fā)生事件例如 before,after。第三部分觸發(fā)器自身所要做的事情，就是觸發(fā)器被觸發(fā)以后具體想表達的事件，在 begin 和 end 之間的 sql。

二、觸發(fā)器的分類：

1、ddl 觸發(fā)器：即執(zhí)行 ddl 操作后所觸發(fā)的事件。

常用的 ddl 操作有：grant（授權(quán)），revoke（撤銷授權(quán)），create（創(chuàng)建），drop（刪除），alter（修改），comment（注釋），audit（審核），rename（重命名）在進行具體實例以前先來講解另一個概念：oracle 中的 user 和 schema：

user：oracle 中的用戶，擁有數(shù)據(jù)庫的對象以及對數(shù)據(jù)庫對象增刪改查的權(quán)限。schema：該用戶下所有數(shù)據(jù)庫對象的集合 Collection. 類似于生活中房子 schema 和房子的擁有者 user 之間的關(guān)系，你是一個用戶 user 你可以通過 alter session 查看別人的房子，但是你是否可以改變房子中的家具，要看這個房子的擁有者是否 grant 你這個權(quán)限，除非你是所有房子的最高權(quán)限人 dba。

ddl Example：禁止 scott 用戶的所有 ddl 操作

CREATE OR REPLACE TRIGGER scott_trigger
BEFORE DDL
ON SCHEMA
BEGIN
 RAISE_APPLICATION_ERROR(-20008, 禁止 scott 用戶的所有 ddl 操作 
END;

create sequence myseq;

這里看到在創(chuàng)建觸發(fā)器以后如果仍然使用 ddl 操作，便會報錯。

2、dml 觸發(fā)器：基于 dml 操作的觸發(fā)器，細分又可以分為行觸發(fā)器和語句觸發(fā)器。

A、語句觸發(fā)器：dml 操作可能會影響很多行，主要用于對數(shù)據(jù)的安全保護。

Example：禁止在周四，周五修改 emp 表數(shù)據(jù)

CREATE OR REPLACE TRIGGER emp_trigger
BEFORE UPDATE OR DELETE OR INSERT
ON emp
BEGIN
 IF to_char(sysdate, day) IN (星期四 , 星期五) THEN
 RAISE_APPLICATION_ERROR(-20008, 不允許在周四周五修改 emp 表 
 END IF;
END;

update emp set sal=800;

這里建立觸發(fā)器以后，當你想改變所有人的工資時就會出觸發(fā)器的錯誤，所有人的工資即表示會影響很多行。

B、行級觸發(fā)器：針對需要操作的那一行，有關(guān)鍵詞：for each row, 用來

（1）實現(xiàn)數(shù)據(jù)的審計功能：

Example: 做一個記錄刪除員工信息的表記錄被刪除員工的信息

這里為了不改變 oracle 中 emp 表的數(shù)據(jù)，新建一個 emp_new 表

create table emp_new
select * from emp;

create table emp_audit(name varchar2(10),delete_time Date);

CREATE OR REPLACE TRIGGER delete_trigger
AFTER DELETE ON emp_new
FOR EACH ROW
BEGIN
 INSERT INTO emp_audit values(:old.ename,sysdate);
END;

delete from emp_new where empno= 7499

select * from emp_audit;

這里可以看到在創(chuàng)建觸發(fā)器時，用到了 for each row 關(guān)鍵詞，：old.*** 用來表示更改以前的表中的數(shù)據(jù)，:new.*** 用來表示更改以后的數(shù)據(jù)，在刪除數(shù)據(jù)以后在日志表就有對應(yīng)的記錄。

（2）實現(xiàn)數(shù)據(jù)完整性：

Example: 要求員工漲工資后，不能低于原來的工資，所漲工資也不能高于原來的 50%。

這里為了不改變 oracle 中 emp 表的數(shù)據(jù)，新建一個 emp_new 表

create table emp_new
select * from emp;

CREATE OR REPLACE TRIGGER emp_trigger
BEFORE UPDATE OF sal ON emp_new
FOR EACH ROW
WHEN (new.sal old.sal OR new.sal 1.5*old.sal)
BEGIN
 RAISE_APPLICATION_ERROR(-20008, 工資只增不降，且漲幅不可大于 50% 
END;

update emp_new set sal = 1.6*sal where empno= 7788

這里可以看到當改變數(shù)據(jù)時會觸發(fā)觸發(fā)器錯誤，對表中某一個字段的修改用 UPDATE OF 即可，另外如果 new 和 old 在 PLSQL 塊的外部即 BEGIN 外面不可以加冒號。

（3）參照完整性：

Example：主要用于級聯(lián)更新，如更新 dept 表中的 deptno 時，emp 表的 deptno 也更新。

這里仍然新建 2 個表分別和 emp 表 dept 表的數(shù)據(jù)相同。

create table emp_new
select * from emp;

create table dept_new
select * from dept;

CREATE OR REPLACE TRIGGER cascade_trigger
AFTER UPDATE OF deptno ON dept_new
FOR EACH ROW
BEGIN
 UPDATE emp_new SET deptno=:new.deptno WHERE deptno=:old.deptno;
END;

update dept_new set deptno=15 where deptno=20;

select * from dept_new;

select * from emp_new;

這里參照完整新指具有主從關(guān)系的多個表，當更新主表主鍵時需要更新從表的相關(guān)數(shù)據(jù)。

3、替代觸發(fā)器：

這里先講另一個概念：帶有 with check option 的視圖：

如果視圖的定義包括條件（如 where 子句）并且任何應(yīng)用于該視圖的 INSERT 或 UPDATE 語句都應(yīng)包括該條件，則必須使用 WITH CHECK OPTION 定義該視圖。

Example：

CREATE VIEW emp_view
(ename,empno)
AS SELECT ename,empno FROM emp 
WHERE deptno=20
WITH CHECK OPTION;

這里有個條件部門號為 20，則任何修改這個視圖的語句都必須針對的是 20 號部門的員工。

繼續(xù)替代觸發(fā)器的概念：關(guān)鍵字 insteadof，主要針對一些復(fù)雜的視圖，因為級聯(lián)表所產(chǎn)生的視圖不可以使用 update,insert,delete 等關(guān)鍵字，沒有 before，after 等關(guān)鍵字，并且不可以建立在 with check option 選項的視圖上，比如新建一個 emp 表和 dept 表的級聯(lián)視圖，則不可以向其中添加數(shù)據(jù)，現(xiàn)在通過觸發(fā)器解決：

Example:

仍然新建 2 個表分別和 emp 表 dept 表的數(shù)據(jù)相同。

CREATE TABLE emp_new
SELECT * FROM emp;
CREATE TABLE dept_new
SELECT * FROM dept;

CREATE VIEW emp_dept
SELECT d.deptno,d.dname,e.empno,e.ename
FROM dept_new d,emp_new e
WHERE d.deptno=e.deptno;

這里 scott 用戶需要先通過 sysdba 授權(quán)才能建立視圖：

grant create view to scott;

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

INSERT INTO emp_dept values(15, HUMANRESOURCE ,7999, LEAF

select * from emp_new;

select * from dept_new;

這里觸發(fā)器中當對視圖進行 insert 時，會對相應(yīng)的 emp_new 和 dept_new 進行修改，也就做到了對復(fù)雜視圖的修改。

4、系統(tǒng)觸發(fā)器：顧名思義，由系統(tǒng)觸發(fā)器所觸發(fā)的事件，常用的系統(tǒng)事件 startup,shutdown,db_roll_change,server error 等。

Example: 記錄啟動數(shù)據(jù)庫時的事件以及時間。

此處因為是系統(tǒng)觸發(fā)器，所以需要用 sysdba 的權(quán)限登陸。

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

CREATE OR REPLACE TRIGGER event_trigger
AFTER STARTUP ON DATABASE
BEGIN
 INSERT INTO event_table VALUES(ora_sysevent,sysdate);
END;

select * from event_table;

三、觸發(fā)器的綜合實例

Example：做一個日志用來記錄 scott 用戶的一些操作：

首先在 sysdba 權(quán)限下建立日志表，序列，觸發(fā)器：

CREATE TABLE object_log(
logid NUMBER CONSTRAINT pk_logid PRIMARY KEY,
operatedate DATE NOT NULL,
objecttype VARCHAR2(50) NOT NULL,
objectowner VARCHAR2(50) NOT NULL
);

CREATE SEQUENCE obj_log_seq;

CREATE OR REPLACE TRIGGER object_trigger
AFTER CREATE OR DROP OR ALTER ON DATABASE
BEGIN
 INSERT INTO object_log VALUES(obj_log_seq.nextval,sysdate,ora_dict_obj_type,ora_dict_obj_owner);
END;

在 scott 用戶下隨便創(chuàng)建個東西：

CREATE SEQUENCE my_seq;

回到 sysdba 權(quán)限下查看日志表中是否有對應(yīng)的記錄：

SELECT * FROM object_log;

關(guān)于如何在 Oracle 中使用觸發(fā)器問題的解答就分享到這里了，希望以上內(nèi)容可以對大家有一定的幫助，如果你還有很多疑惑沒有解開，可以關(guān)注丸趣 TV 行業(yè)資訊頻道了解更多相關(guān)知識。

向 AI 問一下細節(jié)