Oracle?DML觸發(fā)器和DDL觸發(fā)器怎么使用

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

一、概念

觸發(fā)器是Oracle數(shù)據(jù)庫(kù)的對(duì)象，類似存儲(chǔ)過程和函數(shù)。存儲(chǔ)過程和函數(shù)需要用戶顯示調(diào)用才執(zhí)行，而觸發(fā)器是由一個(gè)事件來觸發(fā)運(yùn)行，當(dāng)某個(gè)事件發(fā)生時(shí)會(huì)自動(dòng)地隱式運(yùn)行，不能被顯示的調(diào)用。

觸發(fā)器的本質(zhì)是存儲(chǔ)過程，發(fā)生特定事件時(shí)Oracle會(huì)執(zhí)行觸發(fā)器中的代碼，它的組成可以分為三個(gè)部分：

1、觸發(fā)器執(zhí)行的條件，即觸發(fā)器被觸發(fā)的事件

2、執(zhí)行觸發(fā)器的時(shí)間，發(fā)生事件之前（before）或發(fā)生事件之后（after）

3、觸發(fā)器要做的事情，就是觸發(fā)器被觸發(fā)以后具體想執(zhí)行的任務(wù)（PL/SQL語句塊）

Oracle的觸發(fā)器分為 DML觸發(fā)器、DDL觸發(fā)器、替代觸發(fā)器 和 系統(tǒng)觸發(fā)器。

二、DML觸發(fā)器

基于DML操作的觸發(fā)器，細(xì)分又可以分為行觸發(fā)器和語句觸發(fā)器。

2.1、語句觸發(fā)器

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

2.2、行級(jí)觸發(fā)器

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

行級(jí)觸發(fā)器用for each row關(guān)鍵字。

2.3、DML觸發(fā)器語法：

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

參數(shù)說明：

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

{delete|insert|update|[of列名]}： 觸發(fā)在動(dòng)作，可以指定多個(gè)動(dòng)作，例如：insert or update。如果是update，update of 指定一個(gè)或多個(gè)字段，僅在這些字段被更新時(shí)才會(huì)觸發(fā)。update of 的應(yīng)用場(chǎng)景極少。

[for each row]： 表示是行級(jí)觸發(fā)器。

[when 條件]： 只有滿足when指定的條件，才會(huì)執(zhí)行觸發(fā)體中的代碼，應(yīng)用場(chǎng)景極少。

2.4、觸發(fā)器謂詞：

創(chuàng)建超女基本信息表T_GIRL，插入5條測(cè)試數(shù)據(jù)。

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

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

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

2.5、實(shí)例說明

1）準(zhǔn)備測(cè)試數(shù)據(jù)，創(chuàng)建超女基本信息表T_GIRL，插入5條測(cè)試數(shù)據(jù)。

drop table T_GIRL;
create table T_GIRL
(
  id        char(4)         not null,   -- 編號(hào)
  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）創(chuàng)建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)          -- 觸發(fā)器名。
);

3）創(chuàng)建語觸發(fā)器TR_GIRL_1，如果對(duì)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）創(chuàng)建行級(jí)語觸發(fā)器TR_GIRL_2，如果對(duì)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）執(zhí)行一條delete語句，從T_GIRL表中刪除兩行記錄。

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

6）查看T_SQL_LOG表中的結(jié)果。

三、DDL 觸發(fā)器

當(dāng)執(zhí)行DDL語句時(shí)會(huì)被觸發(fā)。按照作用范圍，分為schema trigger 和 database trigger。schema trigger 作用在一個(gè)用戶上，database trigger 作用在整個(gè)數(shù)據(jù)庫(kù)所有用戶上。

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

3.1、DDL觸發(fā)器語法

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

參數(shù)說明：

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

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

[when 條件]： 只有滿足when指定的條件，才會(huì)執(zhí)行觸發(fā)體中的代碼，應(yīng)用場(chǎng)景極少。

3.2、DDL 事件

3.3、可用屬性

3.4、實(shí)例說明

限制scott用戶的DLL操作，創(chuàng)建數(shù)據(jù)庫(kù)對(duì)象時(shí)發(fā)出警告，刪除數(shù)據(jù)庫(kù)對(duì)象時(shí)阻止。

1）創(chuàng)建觸發(fā)器

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）測(cè)試觸器（創(chuàng)建表），用scott用戶登錄。

在上面創(chuàng)建的觸發(fā)器中用到了dbms_output，在sqlplus中要先執(zhí)行set serveroutput on;才能輸出內(nèi)容。

3）測(cè)試刪除表，用scott用戶登錄。

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

以上就是“Oracle DML觸發(fā)器和DDL觸發(fā)器怎么使用”這篇文章的所有內(nèi)容，感謝各位的閱讀！相信大家閱讀完這篇文章都有很大的收獲，小編每天都會(huì)為大家更新不同的知識(shí)，如果還想學(xué)習(xí)更多的知識(shí)，請(qǐng)關(guān)注億速云行業(yè)資訊頻道。