Oracle觸發器詳解

来源:https://www.cnblogs.com/loveleaf/archive/2018/08/27/9524986.html
-Advertisement-
Play Games

開發中肯定會用到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

 


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • ubuntu18.04:配置/etc/network/interfaces已無用,需修改/etc/netplan/50-cloud-init.yaml ...
  • ORA_DBA:是ORACLE 的特有用戶,是超級管理員許可權,建成DBA 它具有管理資料庫的最高許可權。 註明:需要以管理員身份運行cmd,不然第4步會失敗(點開始,輸入cmd,右鍵以管理員身份運行) 1.利用命令查看當前系統用戶:net user 這是當前系統中所有的用戶。 2.查看用戶組 net ...
  • 索引 1、概述 MySQL索引的建立對於MySQL的高效運行是很重要的,索引可以大大提高MySQL的檢索速度。 雖然索引大大提高了查詢速度,同時卻會降低更新表的速度,如對錶進行INSERT、UPDATE和DELETE。因為更新表時,MySQL不僅要保存數據,還要保存一下索引文件。 建立索引會占用磁碟 ...
  • 主要區別: 1).MyISAM是非事務安全型的,而InnoDB是事務安全型的。 2).MyISAM鎖的粒度是表級,而InnoDB支持行級鎖定。 3).MyISAM支持全文類型索引,而InnoDB不支持全文索引。 4).MyISAM相對簡單,所以在效率上要優於InnoDB,小型應用可以考慮使用MyIS ...
  • InnoDB:支持事務處理等不加鎖讀取支持外鍵支持行鎖不支持FULLTEXT類型的索引不保存表的具體行數,掃描表來計算有多少行DELETE 表時,是一行一行的刪除InnoDB 把數據和索引存放在表空間裡面跨平臺可直接拷貝使用InnoDB中必須包含AUTO_INCREMENT類型欄位的索引表格很難被壓 ...
  • 一. 指定路徑 在上一篇里,二進位包解壓後,全部放入在/usr/local/mysql目錄下,在安裝時指定了基礎目錄和數據目錄, --basedir=/usr/local/mysql --datadir=/usr/local/mysql/data 。 Linux是建議第三方軟體放在/opt下。官網文 ...
  • Preface Oracle claimed that 11g RAC is supported on Redhat Linux 7 and above version,but there're still some incompatible issues in inplementing 11g G ...
  • Oracle 11g行欄位拼接WMSYS.WM_CONCAT問題Not A LOB 一、問題出現 項目中的某個查詢需要將表中某個欄位不重覆地拼接起來,百度得到該函數 ,以及 函數,前者只能使用逗號' '連接,後者可以定製連接符。 但由於listagg不能直接在參數中使用 去重,因此採用 函數。 SQ ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...