開發中肯定會用到Oracle的觸發器,本文進行詳細講解。 這裡實例中用到的主要是Oracle中scott用戶下的emp以及dept表,數據如下 一、觸發器概念 1、概念: 觸發器的本質是一個存儲過程,顧名思義發生特定事件時Oracle會執行觸發器中的代碼。 細分它的組成可以分為3個部分:第一部分在什 ...
開發中肯定會用到Oracle的觸發器,本文進行詳細講解。
這裡實例中用到的主要是Oracle中scott用戶下的emp以及dept表,數據如下
一、觸發器概念
1、概念:
觸發器的本質是一個存儲過程,顧名思義發生特定事件時Oracle會執行觸發器中的代碼。
細分它的組成可以分為3個部分:第一部分在什麼條件下觸發器會執行,即觸發器被觸發的事件。第二部分在什麼時間點執行觸發器
即觸發器的發生事件例如before,after。第三部分觸發器自身所要做的事情,就是觸發器被觸發以後具體想表達的事件,在begin和end
之間的sql。
二、觸發器的分類:
1、ddl觸發器:即執行ddl操作後所觸發的事件。
常用的ddl操作有:grant(授權),revoke(撤銷授權),create(創建),drop(刪除),alter(修改),comment(註釋),audit(審核),rename(重命名)
在進行具體實例以前先來講解另一個概念:oracle中的user和schema:
user:oracle中的用戶,擁有資料庫的對象以及對資料庫對象增刪改查的許可權。schema:該用戶下所有資料庫對象的集合Collection.類似於生活中
房子schema和房子的擁有者user之間的關係,你是一個用戶user你可以通過alter session查看別人的房子,但是你是否可以改變房子中的傢具,要看這個房子的擁有者是否grant你這個許可權,除非你是所有房子的最高許可權人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;
這裡看到在創建觸發器以後如果仍然使用ddl操作,便會報錯。
2、dml觸發器:基於dml操作的觸發器,細分又可以分為行觸發器和語句觸發器。
A、語句觸發器:dml操作可能會影響很多行,主要用於對數據的安全保護。
Example:禁止在周四,周五修改emp表數據
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;
這裡建立觸發器以後,當你想改變所有人的工資時就會出觸發器的錯誤,所有人的工資即表示會影響很多行。
B、行級觸發器:針對需要操作的那一行,有關鍵詞:for each row,用來
(1)實現數據的審計功能:
Example:做一個記錄刪除員工信息的表記錄被刪除員工的信息
這裡為了不改變oracle中emp表的數據,新建一個emp_new表
create table emp_new as 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;
這裡可以看到在創建觸發器時,用到了for each row關鍵詞,:old.***用來表示更改以前的表中的數據,:new.***用來表示更改以後的數據,在刪除數據以後在日誌表就有對應的記錄。
(2)實現數據完整性:
Example:要求員工漲工資後,不能低於原來的工資,所漲工資也不能高於原來的50%。
這裡為了不改變oracle中emp表的數據,新建一個emp_new表
create table emp_new as 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';
這裡可以看到當改變數據時會觸發觸發器錯誤,對錶中某一個欄位的修改用UPDATE OF即可,另外如果new和old在PLSQL塊的外部
即BEGIN外面不可以加冒號。
(3)參照完整性:
Example:主要用於級聯更新,如更新dept表中的deptno時,emp表的deptno也更新。
這裡仍然新建2個表分別和emp表dept表的數據相同。
create table emp_new as select * from emp;
create table dept_new as 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;
這裡參照完整新指具有主從關係的多個表,當更新主表主鍵時需要更新從表的相關數據。
3、替代觸發器:
這裡先講另一個概念:帶有with check option的視圖:
如果視圖的定義包括條件(如where子句)並且任何應用於該視圖的INSERT或UPDATE語句都應包括該條件,則必須使用WITH CHECK OPTION定義該視圖。
Example:
CREATE VIEW emp_view (ename,empno) AS SELECT ename,empno FROM emp WHERE deptno=20 WITH CHECK OPTION;
這裡有個條件部門號為20,則任何修改這個視圖的語句都必須針對的是20號部門的員工。
繼續替代觸發器的概念:關鍵字insteadof,主要針對一些複雜的視圖,因為級聯表所產生的視圖不可以使用update,insert,delete等關鍵字,沒有before,after等關鍵字,並且不可以建立在with check option選項的視圖上,比如新建一個emp表和dept表的級聯視圖,則不可以向其中添加數據,現在通過觸發器解決:
Example:
仍然新建2個表分別和emp表dept表的數據相同。
CREATE TABLE emp_new AS SELECT * FROM emp; CREATE TABLE dept_new AS SELECT * FROM dept;
CREATE VIEW emp_dept AS SELECT d.deptno,d.dname,e.empno,e.ename FROM dept_new d,emp_new e WHERE d.deptno=e.deptno;
這裡scott用戶需要先通過sysdba授權才能建立視圖:
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;
這裡觸發器中當對視圖進行insert時,會對相應的emp_new 和dept_new進行修改,也就做到了對複雜視圖的修改。
4、系統觸發器:
顧名思義,由系統觸發器所觸發的事件,常用的系統事件startup,shutdown,db_roll_change,server error等。
Example:記錄啟動資料庫時的事件以及時間。
此處因為是系統觸發器,所以需要用sysdba的許可權登陸。
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;
三、觸發器的綜合實例
Example:做一個日誌用來記錄scott用戶的一些操作:
首先在sysdba許可權下建立日誌表,序列,觸發器:
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用戶下隨便創建個東西:
CREATE SEQUENCE my_seq;
回到sysdba許可權下查看日誌表中是否有對應的記錄:
SELECT * FROM object_log;
發現有數據,說明一個日誌表成功做好,監視一些用戶操作的觸發器就做好了。
至此,觸發器全部說明完畢,不足之處還請評論說明,謝謝。 2018-08-27 16:50:28