Oracle?DML觸發器和DDL觸發器怎么使用

今天小編給大家分享一下Oracle DML觸發器和DDL觸發器怎么使用的相關知識點，內容詳細，邏輯清晰，相信大部分人都還太了解這方面的知識，所以分享這篇文章給大家參考一下，希望大家閱讀完這篇文章后有所收獲，下面我們一起來了解一下吧。

一、概念

觸發器是Oracle數據庫的對象，類似存儲過程和函數。存儲過程和函數需要用戶顯示調用才執行，而觸發器是由一個事件來觸發運行，當某個事件發生時會自動地隱式運行，不能被顯示的調用。

觸發器的本質是存儲過程，發生特定事件時Oracle會執行觸發器中的代碼，它的組成可以分為三個部分：

1、觸發器執行的條件，即觸發器被觸發的事件

2、執行觸發器的時間，發生事件之前（before）或發生事件之后（after）

3、觸發器要做的事情，就是觸發器被觸發以后具體想執行的任務（PL/SQL語句塊）

Oracle的觸發器分為 DML觸發器、DDL觸發器、替代觸發器 和 系統觸發器。

二、DML觸發器

基于DML操作的觸發器，細分又可以分為行觸發器和語句觸發器。

2.1、語句觸發器

DML操作（insert、delete、update），不管SQL語句影響的記錄是多少行，觸發器只觸發一次。

2.2、行級觸發器

DML操作（insert、delete、update），SQL語句影響了多少行記錄，觸發器就觸發多少次。

行級觸發器用for each row關鍵字。

2.3、DML觸發器語法：

create [or replace] trigger 用戶名.觸發器名
       {before|after}      
       {delete|insert|update|[of列名]}
       on 表名
       [for each row [when 條件]]
declare
  定義變量。
begin
  PL/SQL語句塊。                         
end;

參數說明：

{before|after}： 指定觸發器是在對表的操作發生之前觸發還是之后觸發。

{delete|insert|update|[of列名]}： 觸發在動作，可以指定多個動作，例如：insert or update。如果是update，update of 指定一個或多個字段，僅在這些字段被更新時才會觸發。update of 的應用場景極少。

[for each row]： 表示是行級觸發器。

[when 條件]： 只有滿足when指定的條件，才會執行觸發體中的代碼，應用場景極少。

2.4、觸發器謂詞：

創建超女基本信息表T_GIRL，插入5條測試數據。

old謂詞：執行前的字段的值的名稱，比如update一個表時，使用:old.columnname是指執行update操作之前的列的值。

new謂詞：執行后的字段的值的名稱，比如update一個表時，使用:new.columnname是指執行 update操作之后的列的值。

可以在觸發器體的語句塊中使用 inserting、updating、deleting謂詞，這些謂詞會返回相應的DML操作的布爾值，如果為true，則表示執行了相應的insert、update、delete操作。

2.5、實例說明

1）準備測試數據，創建超女基本信息表T_GIRL，插入5條測試數據。

drop table T_GIRL;
create table T_GIRL
(
  id        char(4)         not null,   -- 編號
  name      varchar2(30)    not null,   -- 姓名
  primary key(id)                       -- 指定id為表的主鍵
);
insert into T_GIRL(id,name) values('0101','西施');
insert into T_GIRL(id,name) values('0102','貂禪');
insert into T_GIRL(id,name) values('0103','妲已');
insert into T_GIRL(id,name) values('0104','芙蓉姐姐');
insert into T_GIRL(id,name) values('0105','神密貓女');

2）創建SQL日志表。

drop table T_SQL_LOG;
create table T_SQL_LOG
(
  tname    varchar2(10),        -- 原表的表名。
  srcrowid rowid,               -- 原表rowid。
  sqltype  number(1),           -- SQL語句的類型：1-insert、2-update、3-delete。
  trname  varchar2(10)          -- 觸發器名。
);

3）創建語觸發器TR_GIRL_1，如果對T_GIRL表做了insert、update和delete操作，把操作記錄在T_SQL_LOG表中。

create or replace trigger TR_GIRL_1
  before update or delete or insert
  on T_GIRL
begin
  if inserting then
    insert into T_SQL_LOG(tname,srcrowid,sqltype,trname) values('T_GIRL',null,1,'TR_GIRL_1');
  end if;
  if updating then
    insert into T_SQL_LOG(tname,srcrowid,sqltype,trname) values('T_GIRL',null,2,'TR_GIRL_1');
  end if;
  if deleting then
    insert into T_SQL_LOG(tname,srcrowid,sqltype,trname) values('T_GIRL',null,3,'TR_GIRL_1');
  end if;
end;

4）創建行級語觸發器TR_GIRL_2，如果對T_GIRL表做了insert、update和delete操作，把每一行的操作記錄在T_SQL_LOG表中。

create or replace trigger TR_GIRL_2
  before update or delete or insert
  on T_GIRL for each row
begin
  if inserting then
    insert into T_SQL_LOG(tname,srcrowid,sqltype,trname) values('T_GIRL',:new.rowid,1,'TR_GIRL_2');
  end if;
  if updating then
    insert into T_SQL_LOG(tname,srcrowid,sqltype,trname) values('T_GIRL',:new.rowid,2,'TR_GIRL_2');
  end if;
  if deleting then
    insert into T_SQL_LOG(tname,srcrowid,sqltype,trname) values('T_GIRL',:old.rowid,3,'TR_GIRL_2');
  end if;
end;

5）執行一條delete語句，從T_GIRL表中刪除兩行記錄。

delete from T_GIRL where id in ('0101','0102');

6）查看T_SQL_LOG表中的結果。

三、DDL 觸發器

當執行DDL語句時會被觸發。按照作用范圍，分為schema trigger 和 database trigger。schema trigger 作用在一個用戶上，database trigger 作用在整個數據庫所有用戶上。

常用的DDL操作有：grant（授權），revoke（撤銷授權），create（創建），drop（刪除），alter（修改），comment（注釋），audit（審核），rename（重命名）等。

3.1、DDL觸發器語法

create [or replace] trigger 用戶名.觸發器名
       {before|after} {DDL事件} on {database|schema}
       [when 條件]
declare
  定義變量。
begin
  PL/SQL語句塊。
end;

參數說明：

{before|after}： 觸發器是在DDL事件之前、之后觸發。

{database|schema}： 作用在一個用戶上，還是全部的用戶。

[when 條件]： 只有滿足when指定的條件，才會執行觸發體中的代碼，應用場景極少。

3.2、DDL 事件

3.3、可用屬性

3.4、實例說明

限制scott用戶的DLL操作，創建數據庫對象時發出警告，刪除數據庫對象時阻止。

1）創建觸發器

create or replace trigger scott.no_drop
  before ddl on schema
begin
  if ora_sysevent='CREATE' then
    dbms_output.put_line('Warning !!! You have created a '||
                         ORA_DICT_OBJ_TYPE||' called '||
                         ORA_DICT_OBJ_NAME|| '; UserName:'||
                         ORA_DICT_OBJ_OWNER||'; IP:'||
                         ORA_CLIENT_IP_ADDRESS||'; event:'||
                         ORA_SYSEVENT);
  elsif ora_sysevent='DROP' then
    RAISE_APPLICATION_ERROR(-20000,'Cannot drop the '||
                            ORA_DICT_OBJ_TYPE||' named '||
                            ORA_DICT_OBJ_NAME ||' as requested by '||
                            ORA_DICT_OBJ_OWNER);
  end if;
end;

2）測試觸器（創建表），用scott用戶登錄。

在上面創建的觸發器中用到了dbms_output，在sqlplus中要先執行set serveroutput on;才能輸出內容。

3）測試刪除表，用scott用戶登錄。

4）測試刪除表，用DBA用戶登錄，no_drop觸發器只限scott用戶，不限制其它用戶。

以上就是“Oracle DML觸發器和DDL觸發器怎么使用”這篇文章的所有內容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會為大家更新不同的知識，如果還想學習更多的知識，請關注億速云行業資訊頻道。