Oracle資料庫——觸發器的建立與應用

標籤：after   返回   select   add   replace   解決   acl   user   ddl   

一、涉及內容

1.理解觸發器的概念、作用和類型。

2.練習觸發器的建立和使用。

二、具體操作

（實驗）

  1.利用觸發器對在scott.emp表上執行的DML操作進行安全性檢查，只有scott使用者登入資料庫後才能向該表中執行DML操作。（第1題中，user是系統函數，返回目前使用者。字串中使用兩個單引號表示一個單引號。）

要求：分別以system使用者和scott使用者對emp 表執行DML操作，實驗觸發器的運行效果。

（1）在scott使用者下建立觸發器

語句：

create or replace trigger tri_dm1 before insert or update or delete on scott.emp begin   if user <>‘SCOTT‘ then     raise_application_error(-20001,‘You don‘‘t have access to modify this table.‘);   end if; end;

（2）以system 使用者串連，並對emp表執行DML操作

語句：

conn system/orcl1234;insert into scott.emp(empno,ename)  values(8888,‘shenxiao‘);

（3）以scott使用者串連，並對emp表執行DML操作

語句：

conn scott/tiger;insert into scott.emp(empno,ename)  values(8888,‘shenxiao‘);

2.利用觸發器進行表和備份表之間的同步複製。

（1）在scott 使用者下建立scott.emp 表的複本 employee。

語句：

 conn scott/tiger;

create table employee as select * from scott.emp;

（2）在scott使用者下建立能實現scott.emp和employee 兩表之間同步複製的DML觸發器。

語句：

create or replace trigger duplicate_emp after update or insert or delete on scott.emp for each row begin   if inserting then     insert into  employee values (:new.empno,:new.ename,:new.job,:new.mgr,                                   :new.hiredate,:new.sal,:new.comm,:new.deptno);   elsif deleting then     delete from employee where empno=:old.empno;   else     update employee set empno=:new.empno,ename=:new.ename,job=:new.job,                         mgr=:new.mgr,hiredate=:new.hiredate,sal=:new.sal,comm=:new.comm,                          deptno=:new.deptno     where empno=:old.empno;   end if;  end; /

（3）對scott.emp表進行插入、刪除和更新操作。

刪除：delete from scott.emp where empno=7934;

插入：

Insert into scott.emp(empno,ename,job,sal) values(1111,‘zhangsan‘,‘ANALYST‘,2900)

更新：

 update scott.emp set sal=3900 where empno=1111;

（4）查詢scott.emp表和employee表中插入、刪除和更新的記錄。

語句：select * from scott.emp;

        select * from employee；

3.建立觸發器，對scott.emp 表進行DML操作時的時間、使用者進行日誌記錄。

 （1）建立日誌表emp_log。

語句：create table emp_log(who varchar2(30),when date,oper varchar2(10));

 （2）在emp表上建立語句級觸發器，將對emp表執行的操作記錄到emp_log表中。

語句：

create or replace trigger dm1_log after insert or update or delete on scott.emp declare    oper emp_log.oper%type;  begin     if inserting then        oper:=‘insert‘;    elsif deleting then        oper:=‘delete‘;    else        oper:=‘update‘;    end if;    insert into emp_log  values(user,sysdate,oper); end; /

 （3）對scott.emp 執行DML操作，查看emp_log 表中的資料。

語句：

insert into scott.emp(empno)  values(1112);select * from emp_log;

（習題）

1.簡述Oracle 資料庫中觸發器的類型及觸發條件。

答：觸發器的類型及觸發條件如下表所示：

 

按劃分類型	觸發器的類型	觸發條件
按照觸發的時間	BEFORE觸發器，指事前觸發器	在觸發語句執行前觸發器被觸發
	AFTER觸發器，指事後觸發器	在觸發語句執行以後觸發器被觸發
	INSTEAD OF觸發器，指替代觸發器	觸發語句被觸發器操作替代
按照觸發的事件	DML觸發器	對錶或視圖執行DML操作時觸發的觸發器
	DDL觸發器	在資料庫中執行DDL操作時觸發的觸發器
	使用者事件觸發程序	與使用者執行的DCL操作或LOGON/LOGOFF操作相關的觸發器
	系統事件觸發器	是指由資料庫系統事件觸發的觸發器

其中，DML觸發器，按照觸發時DML操作影響的記錄多少，又可分為：

行級觸發器 ：DML語句每操作一行，行級觸發器就會被調用一次

語句級觸發器 ：DML語句不論影響多少行資料，語句級觸發器只被調用一次

 

DDL觸發器又可以分為：

資料庫級DDL觸發器 ：資料庫中任何使用者執行了相應的DDL操作該類觸發器都被觸發。

使用者級DDL觸發器 ：只有在建立觸發器時指定方案的使用者執行相應的DDL操作時觸發器才被觸發，其他使用者執行該DDL操作時觸發器不會被觸發。

 

2.描述一個觸發器的組成部分及其作用。

答：在Oracle系統中，觸發器包括以下幾個組成部分：

組成部分	作用
1.觸發器名稱	觸發器名是在建立觸發器為觸發器起的名稱。一般包括：觸發器執行的時間、執行的操作、涉及的表、涉及的列等。
2.觸發語句	觸發語句是導致Oracle執行觸發器操作的誘因，它包括對觸發時間、觸發事件和觸發對象的定義。只有使用者對資料庫執行的操作滿足觸發語句中定義的所有內容後，觸發器才有可能被系統自動調用。
3.觸發限制條件	觸發限制條件是決定觸發器是否被系統自動調用的另一個因素。當使用者的操作滿足觸發語句時，觸發器不一定被調用，此時，系統還要檢查觸發器中是否定義了觸發限制條件，如果存在，還要檢查當前的操作是否滿足限制條件。
4.觸發器操作	觸發器操作是觸發器的主體，是被系統自動執行的PL/SQL 程式塊。當觸發語句和觸發限制條件都滿足時，系統將自動執行觸發器操作部分的代碼。

 

3.簡述替代觸發器的作用。

答：

建立觸發器時若選擇了INSTEAD OF子句，那麼該觸發器就是替代觸發器。 替代觸發器只能建立在視圖上不能建立在表上。使用者在視圖上執行的DML操作將被替代觸發器中的操作代替。

替代觸發器主要解決對不可更新視圖執行更新操作時帶來的問題。在定義視圖時，如果視圖中沒有選擇基礎資料表的主鍵咧，或者視圖中的資料來自多個基礎資料表，那麼使用者將無法對這樣的視圖直接執行插入、修改、刪除操作。這種情況下，使用者可以針對是視圖建立一個替代觸發器，將對視圖的更新操作轉換為對基礎資料表的操作。

Oracle資料庫——觸發器的建立與應用