国产一级a片免费看高清,亚洲熟女中文字幕在线视频,黄三级高清在线播放,免费黄色视频在线看

觸發(fā)器的種類和觸發(fā)事件
觸發(fā)器必須由事件才能觸發(fā)。觸發(fā)器的觸發(fā)事件分可為3類，分別是DML事件、DDL事件和數(shù)據(jù)庫事件。
每類事件包含若干個事件，如下所示。數(shù)據(jù)庫的事件是具體的，在創(chuàng)建觸發(fā)器時要指明觸發(fā)的事件。
種  類 關(guān) 鍵 字 含    義

DML事件(3種)	INSERT	在表或視圖中插入數(shù)據(jù)時觸發(fā)UPDATE	修改表或視圖中的數(shù)據(jù)時觸發(fā)DELETE	在刪除表或視圖中的數(shù)據(jù)時觸發(fā)DDL事件(3種)	CREATE	在創(chuàng)建新對象時觸發(fā)ALTER	修改數(shù)據(jù)庫或數(shù)據(jù)庫對象時觸發(fā)DROP	刪除對象時觸發(fā)數(shù)據(jù)庫事件(5種)	STARTUP	數(shù)據(jù)打開時觸發(fā)SHUTDOWN	在使用NORMAL或IMMEDIATE選項關(guān)閉數(shù)據(jù)庫時觸發(fā)LOGON	當(dāng)用戶連接到數(shù)據(jù)庫并建立會話時觸發(fā)LOGOFF	當(dāng)一個會話從數(shù)據(jù)庫中斷開時觸發(fā)SERVERERROR	發(fā)生服務(wù)器錯誤時觸發(fā)

觸發(fā)器的類型可劃分為4種：數(shù)據(jù)操縱語言(DML)觸發(fā)器、替代(INSTEAD OF)觸發(fā)器、數(shù)據(jù)定義語言(DDL)觸發(fā)器和數(shù)據(jù)庫事件觸發(fā)器。
各類觸發(fā)器的作用如下所示。

種  類	簡  稱	作    用數(shù)據(jù)操縱語言觸發(fā)器	DML觸發(fā)器	創(chuàng)建在表上，由DML事件引發(fā)的觸發(fā)器替代觸發(fā)器	INSTEAD OF觸發(fā)器	創(chuàng)建在視圖上，用來替換對視圖進行的插入、刪除和修改操作數(shù)據(jù)定義語言觸發(fā)器	DDL觸發(fā)器	定義在模式上，觸發(fā)事件是數(shù)據(jù)庫對象的創(chuàng)建和修改數(shù)據(jù)庫事件觸發(fā)器	—	定義在整個數(shù)據(jù)庫或模式上，觸發(fā)事件是數(shù)據(jù)庫事件

DML觸發(fā)器的要點
DML觸發(fā)器是定義在表上的觸發(fā)器，由DML事件引發(fā)。編寫DML觸發(fā)器的要素是：
* 確定觸發(fā)的表，即在其上定義觸發(fā)器的表。
* 確定觸發(fā)的事件，DML觸發(fā)器的觸發(fā)事件有INSERT、UPDATE和DELETE三種，說明見下。
* 確定觸發(fā)時間。觸發(fā)的時間有BEFORE和AFTER兩種，分別表示觸發(fā)動作發(fā)生在DML語句執(zhí)行之前和語句執(zhí)行之后。
* 確定觸發(fā)級別，有語句級觸發(fā)器和行級觸發(fā)器兩種。語句級觸發(fā)器表示SQL語句只觸發(fā)一次觸發(fā)器，行級觸發(fā)器表示SQL語句影響的每一行都要觸發(fā)一次。
由于在同一個表上可以定義多個DML觸發(fā)器，因此觸發(fā)器本身和引發(fā)觸發(fā)器的SQL語句在執(zhí)行的順序上有先后的關(guān)系。它們的順序是：
* 如果存在語句級BEFORE觸發(fā)器，則先執(zhí)行一次語句級BEFORE觸發(fā)器。
* 在SQL語句的執(zhí)行過程中，如果存在行級BEFORE觸發(fā)器，則SQL語句在對每一行操作之前，都要先執(zhí)行一次行級BEFORE觸發(fā)器，然后才對行進行操作。如果存在行級AFTER觸發(fā)器，則SQL語句在對每一行操作之后，都要再執(zhí)行一次行級AFTER觸發(fā)器。
* 如果存在語句級AFTER觸發(fā)器，則在SQL語句執(zhí)行完畢后，要最后執(zhí)行一次語句級AFTER觸發(fā)器。
DML觸發(fā)器還有一些具體的問題，說明如下：
* 如果有多個觸發(fā)器被定義成為相同時間、相同事件觸發(fā)，且最后定義的觸發(fā)器是有效的，則最后定義的觸發(fā)器被觸發(fā)，其他觸發(fā)器不執(zhí)行。
* 一個觸發(fā)器可由多個不同的DML操作觸發(fā)。在觸發(fā)器中，可用INSERTING、DELETING、UPDATING謂詞來區(qū)別不同的DML操作。這些謂詞可以在IF分支條件語句中作為判斷條件來使用。
* 在行級觸發(fā)器中，用:new 和:old(稱為偽記錄)來訪問數(shù)據(jù)變更前后的值。但要注意，INSERT語句插入一條新記錄，所以沒有:old記錄，而DELETE語句刪除掉一條已經(jīng)存在的記錄，所以沒有:new記錄。UPDATE語句既有:old記錄，也有:new記錄，分別代表修改前后的記錄。引用具體的某一列的值的方法是：
       ld.字段名或:new.字段名
* 觸發(fā)器體內(nèi)禁止使用COMMIT、ROLLBACK、SAVEPOINT語句，也禁止直接或間接地調(diào)用含有上述語句的存儲過程。
定義一個觸發(fā)器時要考慮上述多種情況，并根據(jù)具體的需要來決定觸發(fā)器的種類。
DML觸發(fā)器的創(chuàng)建
創(chuàng)建DML觸發(fā)器需要CREATE TRIGGER系統(tǒng)權(quán)限。創(chuàng)建DML觸發(fā)器的語法如下：
CREATE [OR REPLACE] TRIGGER 觸發(fā)器名
{BEFORE|AFTER|INSTEAD OF} 觸發(fā)事件1 [OR 觸發(fā)事件2...]
ON 表名
WHEN 觸發(fā)條件
[FOR EACH ROW]
DECLARE
聲明部分
BEGIN
主體部分
END;
其中：
OR REPLACE：表示如果存在同名觸發(fā)器，則覆蓋原有同名觸發(fā)器。
BEFORE、AFTER和INSTEAD OF：說明觸發(fā)器的類型。
WHEN 觸發(fā)條件：表示當(dāng)該條件滿足時，觸發(fā)器才能執(zhí)行。
觸發(fā)事件：指INSERT、DELETE或UPDATE事件，事件可以并行出現(xiàn)，中間用OR連接。
對于UPDATE事件，還可以用以下形式表示對某些列的修改會引起觸發(fā)器的動作：
UPDATE OF 列名1，列名2...
ON 表名：表示為哪一個表創(chuàng)建觸發(fā)器。
FOR EACH ROW：表示觸發(fā)器為行級觸發(fā)器，省略則為語句級觸發(fā)器。
觸發(fā)器的創(chuàng)建者或具有DROP ANY TIRGGER系統(tǒng)權(quán)限的人才能刪除觸發(fā)器。刪除觸發(fā)器的語法如下：
DROP TIRGGER 觸發(fā)器名
可以通過命令設(shè)置觸發(fā)器的可用狀態(tài)，使其暫時關(guān)閉或重新打開，即當(dāng)觸發(fā)器暫時不用時，可以將其置成無效狀態(tài)，在使用時重新打開。該命令語法如下：
ALTER TRIGGER 觸發(fā)器名 {DISABLE|ENABLE}
其中，DISABLE表示使觸發(fā)器失效，ENABLE表示使觸發(fā)器生效。
同存儲過程類似，觸發(fā)器可以用SHOW ERRORS 檢查編譯錯誤。
行級觸發(fā)器的應(yīng)用
在行級觸發(fā)器中，SQL語句影響的每一行都會觸發(fā)一次觸發(fā)器，所以行級觸發(fā)器往往用在對表的每一行的操作進行控制的場合。若在觸發(fā)器定義中出現(xiàn)FOR EACH ROW子句，則為語句級觸發(fā)器。
【訓(xùn)練1】  創(chuàng)建包含插入、刪除、修改多種觸發(fā)事件的觸發(fā)器DML_LOG，對EMP表的操作進行記錄。用INSERTING、DELETING、UPDATING謂詞來區(qū)別不同的DML操作。
在創(chuàng)建觸發(fā)器之前，需要先創(chuàng)建事件記錄表LOGS，該表用來對操作進行記錄。該表的字段含義解釋如下：
LOG_ID：操作記錄的編號，數(shù)值型，它是該表的主鍵，由序列自動生成。
LOG_TABLE：進行操作的表名，字符型，非空，該表設(shè)計成可以由多個觸發(fā)器共享使用。比如我們可以為dept表創(chuàng)建類似的觸發(fā)器，同樣將操作記錄到該表。
LOG_DML：操作的動作，即INSERT、DELETE或UPDATE三種之一。
LOG_KEY_ID：操作時表的主鍵值，數(shù)值型。之所以記錄表的主鍵，是因為主鍵是表的記錄的惟一標(biāo)識，可以識別是對哪一條記錄進行了操作。對于emp表，主鍵是empno。
LOG_DATE：操作的日期，日期型，取當(dāng)前的系統(tǒng)時間。
LOG_USER：操作者，字符型，取當(dāng)時的操作者賬戶名。比如登錄SCOTT賬戶進行操作，在該字段中，記錄賬戶名為SCOTT。
步驟1：在SQL*Plus中登錄STUDENT賬戶，創(chuàng)建如下的記錄表LOGS：

CREATE TABLE logs(LOG_ID NUMBER(10) PRIMARY KEY,LOG_TABLE VARCHAR2(10) NOT NULL,LOG_DML VARCHAR2(10),LOG_KEY_ID NUMBER(10),LOG_DATE DATE,LOG_USER VARCHAR2(15));

執(zhí)行結(jié)果：

表已創(chuàng)建。

步驟2：創(chuàng)建一個LOGS表的主鍵序列LOGS_ID_SEQ：

CREATE SEQUENCE logs_id_squ INCREMENT BY 1START WITH 1 MAXVALUE 9999999 NOCYCLE NOCACHE;

執(zhí)行結(jié)果：

序列已創(chuàng)建。

步驟3：創(chuàng)建和編譯以下觸發(fā)器：

CREATE OR REPLACE TRIGGER DML_LOGBEFORE --觸發(fā)時間為操作前DELETE OR INSERT OR UPDATE -- 由三種事件觸發(fā)ON empFOR EACH ROW -- 行級觸發(fā)器BEGINIF INSERTING THENINSERT INTO logs 		VALUES(logs_id_squ.NEXTVAL,'EMP','INSERT',:new.empno,SYSDATE,USER);ELSIF DELETING THENINSERT INTO logs 		VALUES(logs_id_squ.NEXTVAL,'EMP','DELETE',:old.empno,SYSDATE,USER);ELSEINSERT INTO logs 		VALUES(logs_id_squ.NEXTVAL,'EMP','UPDATE',:new.empno,SYSDATE,USER);END IF;END;

執(zhí)行結(jié)果：
觸發(fā)器已創(chuàng)建
步驟4：在EMP表中插入記錄：

INSERT INTO emp(empno,ename,job,sal) VALUES(8001,'MARY','CLERK',1000);COMMIT;

執(zhí)行結(jié)果：

已創(chuàng)建1行。提交完成。

步驟5：檢查LOGS表中記錄的信息：

SELECT * FROM LOGS;

執(zhí)行結(jié)果為：
   

LOG_ID LOG_TABLE  LOG_DML    LOG_KEY_ID LOG_DATE   LOG_USER----------------- ----------------- ------------------ ----------------------- ---------------- -------------------1 EMP         INSERT            8001    29-3月 -04     STUDENT已選擇 1 行。

說明：本例中在emp表上創(chuàng)建了一個由INSERT或DELETE或UPDATE事件觸發(fā)的行級觸發(fā)器，觸發(fā)器的名稱是LOG_EMP。對于不同的操作，記錄的內(nèi)容不同。本例中只插入了一條記錄，如果用一條不帶WHERE條件的UPDATE語句來修改所有雇員的工資，則將逐行觸發(fā)觸發(fā)器。
INSERT、DELETE和UPDATE都能引發(fā)觸發(fā)器動作，在分支語句中使用INSERTING、DELETING和UPDATING來區(qū)別是由哪種操作引發(fā)的觸發(fā)器動作。
在本例的插入動作中，LOG_ID字段由序列LOG_ID_SQU自動填充為1；LOGS表LOG_KEY_ID字段記錄的是新插入記錄的主鍵8001；LOD_DML字段記錄的是插入動作INSERT；LOG_TABLE字段記錄當(dāng)前表名EMP；LOG_DATE字段記錄插入的時間04年3月1日；LOG_USER字段記錄插入者STUDENT。
【練習(xí)1】修改、刪除剛剛插入的雇員記錄，提交后檢查LOGS表的結(jié)果。
【練習(xí)2】為DEPT表創(chuàng)建同樣的觸發(fā)器，使用LOGS表進行記錄，并檢驗結(jié)果。
【訓(xùn)練2】  創(chuàng)建一個行級觸發(fā)器LOG_SAL，記錄對職務(wù)為CLERK的雇員工資的修改，且當(dāng)修改幅度超過200時才進行記錄。用WHEN條件限定觸發(fā)器。
在創(chuàng)建觸發(fā)器之前，需要先創(chuàng)建事件記錄表LOGERR，該表用來對操作進行記錄。該表的字段含義解釋如下：
NUM：數(shù)值型，用于記錄序號。
MESSAGE：字符型，用于記錄錯誤信息。
步驟1：在SQL*Plus中登錄STUDENT賬戶，創(chuàng)建如下的記錄表LOGERR：

CREATE TABLE logerr(NUM NUMBER(10) NOT NULL,MESSAGE VARCHAR2(50) NOT NULL);

執(zhí)行結(jié)果：

表已創(chuàng)建。

步驟2：創(chuàng)建和編譯以下觸發(fā)器：

CREATE OR REPLACE TRIGGER log_salBEFOREUPDATE OF salON empFOR EACH ROWWHEN (new.job='CLERK' AND (ABS(new.sal-old.sal)>200))DECLAREv_no NUMBER;BEGINSELECT COUNT(*) INTO v_no FROM logerr;INSERT INTO logerr VALUES(v_no+1,'雇員'||:new.ename||'的原工資：'||:old.sal||'新工資：'||:new.sal);END;

執(zhí)行結(jié)果：

觸發(fā)器已創(chuàng)建。

步驟3：在EMP表中更新記錄：

UPDATE emp SET sal=sal+550 WHERE empno=7788;UPDATE emp SET sal=sal+500 WHERE empno=7369;UPDATE emp SET sal=sal+50 WHERE empno=7876;COMMIT;

執(zhí)行結(jié)果：

已更新 1 行。已更新 1 行。已更新 1 行。提交完成。

步驟4：檢查LOGSAL表中記錄的信息：

SELECT * FROM logerr;

執(zhí)行結(jié)果為：
      

NUM MESSAGE------------------ --------------------------------------------------------1 雇員SMITH的原工資：800新工資：1300已選擇 1 行。

  說明：本例中，在emp表的sal列上創(chuàng)建了一個由UPDATE事件觸發(fā)的行級觸發(fā)器，觸發(fā)器的名稱是LOG_SAL。該觸發(fā)器由WHEN語句限定，只有當(dāng)被修改工資的雇員職務(wù)為CLERK，且修改的工資超過200時才進行觸發(fā)，否則不進行觸發(fā)。
所以在驗證過程中，雖然修改了3條記錄，但通過查詢語句發(fā)現(xiàn)：第一條修改語句修改編號為7788的SCOTT記錄，因為SCOTT的職務(wù)是ANALYST，不符合WHEN條件，沒有引起觸發(fā)器動作；第二條修改語句修改編號為7369的SMITH的記錄，職務(wù)為CLERK，因為增加的工資(500)超過了200，所以引起觸發(fā)器動作，并在LOGERR表中進行了記錄；第三條修改語句修改編號為7876的雇員ADAMS的記錄，雖然ADAMS的職務(wù)為CLERK，但修改的工資(50)沒有超過200，所以沒有引起觸發(fā)器動作。
注意：在WHEN條件中引用new和old不需要在前面加“: ”。
在以上實例中，記錄了對工資的修改超出范圍的信息，但沒有限制對工資的修改。那么當(dāng)對雇員工資的修改幅度不滿足條件時，能否直接限制對工資的修改呢？答案是肯定的。
【訓(xùn)練3】  創(chuàng)建觸發(fā)器CHECK_SAL，當(dāng)對職務(wù)為CLERK的雇員的工資修改超出500至2000的范圍時，進行限制。
步驟1：創(chuàng)建和編譯以下觸發(fā)器：

CREATE OR REPLACE TRIGGER CHECK_SALBEFOREUPDATEON empFOR EACH ROWBEGINIF :new.job='CLERK' AND (:new.sal<500 OR :new.sal>2000) THENRAISE_APPLICATION_ERROR(-20001, '工資修改超出范圍,操作取消！');END IF;END;

執(zhí)行結(jié)果：

觸發(fā)器已創(chuàng)建。

步驟2：在EMP表中插入記錄：

UPDATE emp SET sal=800 WHERE empno=7876;UPDATE emp SET sal=450 WHERE empno=7876;COMMIT;

執(zhí)行結(jié)果：

UPDATE emp SET sal=450 WHERE empno=7876*ERROR 位于第 1 行:ORA-20001: 工資修改超出范圍,操作取消！ORA-06512: 在"STUDENT.CHECK_SAL", line 3ORA-04088: 觸發(fā)器 'STUDENT.CHECK_SAL' 執(zhí)行過程中出錯提交完成。

步驟3：檢查工資的修改結(jié)果：

SELECT empno,ename,job,sal FROM emp WHERE empno=7876;

執(zhí)行結(jié)果為：
   

 EMPNO ENAME      JOB              SAL------------------ ------------- ------------- ------------------------7876  ADAMS      CLERK            800

說明：在觸發(fā)器中，當(dāng)IF語句的條件滿足時，即對職務(wù)為CLERK的雇員工資的修改超出指定范圍時，用RAISE_APPLICATION_ERROR語句來定義一個臨時定義的異常，并立即引發(fā)異常。由于觸發(fā)器是BEFORE類型，因此觸發(fā)器先執(zhí)行，觸發(fā)器因異常而終止，SQL語句的執(zhí)行就會取消。
通過步驟2的執(zhí)行信息可以看到，第一條語句修改編號為7876的雇員ADAMS的工資為800，成功執(zhí)行。第二條語句修改雇員ADAMS的工資為450，發(fā)生異常，執(zhí)行失敗。這樣就阻止了不符合條件的工資的修改。通過步驟3的查詢可以看到，雇員ADAMS最后的工資是800，即發(fā)生異常之前的修改結(jié)果。
【練習(xí)3】限定對emp表的修改，只能修改部門10的雇員工資。
   【訓(xùn)練4】  創(chuàng)建一個行級觸發(fā)器CASCADE_UPDATE，當(dāng)修改部門編號時，EMP表的相關(guān)行的部門編號也自動修改。該觸發(fā)器稱為級聯(lián)修改觸發(fā)器。
步驟1：創(chuàng)建和編譯以下觸發(fā)器：

CREATE TRIGGER CASCADE_UPDATEAFTERUPDATE OF deptnoON DEPTFOR EACH ROWBEGINUPDATEEMP SET EMP.DEPTNO=:NEW.DEPTNOWHERE EMP.DEPTNO=:OLD.DEPTNO;END;

執(zhí)行結(jié)果：

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

步驟2：驗證觸發(fā)器：

UPDATE dept SET deptno=11 WHERE deptno=10;COMMIT;

執(zhí)行結(jié)果：

已更新 1 行。 

執(zhí)行查詢：

SELECT empno,ename,deptno FROM emp;

執(zhí)行結(jié)果：
  

 EMPNO ENAME          DEPTNO----------------- ----------- -------------------------7369 SMITH             	207499 ALLEN              	307521 WARD               	307566 JONES              	207654 MARTIN             	307698 BLAKE              	307782 CLARK              	117839 KING               	117844 TURNER             	307876 ADAMS              	207900 JAMES              	307902 FORD               	207934 MILLER             	117788 SCOTT              	20

說明：通過檢查雇員的部門編號，發(fā)現(xiàn)原來編號為10的部門編號被修改為11。
本例中的UPDATE OF deptno表示只有在修改表的DEPTNO列時才引發(fā)觸發(fā)器，對其他列的修改不會引起觸發(fā)器的動作。在觸發(fā)器中，對雇員表的部門編號與修改之前的部門編號一樣的雇員，修改其部門編號為新的部門編號。注意，在語句中同時用到了:new和:old來引用修改部門編號前后的部門編號。
【練習(xí)4】建立級聯(lián)刪除觸發(fā)器CASCADE_DELETE，當(dāng)刪除部門時，級聯(lián)刪除EMP表的雇員記錄。
利用觸發(fā)器還可以修改數(shù)據(jù)。
【訓(xùn)練5】  將插入的雇員的名字變成以大寫字母開頭。
步驟1：創(chuàng)建和編譯以下觸發(fā)器：

CREATE OR REPLACE TRIGGER INITCAPBEFORE INSERTON EMPFOR EACH ROWBEGIN:new.ename:=INITCAP(:new.ename);END;

執(zhí)行結(jié)果：

觸發(fā)器已創(chuàng)建。

步驟2：驗證運行結(jié)果：

INSERT INTO emp(empno,ename,job,sal) VALUES(1000,'BILL','CLERK',1500);

執(zhí)行結(jié)果：

已創(chuàng)建 1 行。

執(zhí)行查詢：

SELECT ename,job,sal FROM emp WHERE empno=1000;

執(zhí)行結(jié)果：

ENAME      JOB              SAL------------- ------------- ------------------------Bill          CLERK           1500

說明：在本例中，通過直接為:new.ename進行賦值，修改了插入的值，但是這種用法只能在BEFORE型觸發(fā)器中使用。驗證結(jié)果為，在插入語句中雇員名稱為大寫的BILL，查詢結(jié)果中雇員名稱已經(jīng)轉(zhuǎn)換成以大寫開頭的Bill。
【練習(xí)5】限定一次對雇員的工資修改不超過原工資的10%。
語句級觸發(fā)器的應(yīng)用
同行級觸發(fā)器不同，語句級觸發(fā)器的每個操作語句不管操作的行數(shù)是多少，只觸發(fā)一次觸發(fā)器，所以語句級觸發(fā)器適合于對整個表的操作權(quán)限等進行控制。在觸發(fā)器定義中若省略FOR EACH ROW子句，則為語句級觸發(fā)器。
【訓(xùn)練1】  創(chuàng)建一個語句級觸發(fā)器CHECK_TIME，限定對表EMP的修改時間為周一至周五的早8點至晚5點。
步驟1：創(chuàng)建和編譯以下觸發(fā)器：

CREATE OR REPLACE TRIGGER CHECK_TIMEBEFOREUPDATE OR INSERT OR DELETEON EMPBEGINIF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN'))OR TO_CHAR(SYSDATE,'HH24')< '08'OR TO_CHAR(SYSDATE,'HH24')>='17' THENRAISE_APPLICATION_ERROR(-20500,'非法時間修改表錯誤！');END IF;END;

執(zhí)行結(jié)果：

觸發(fā)器已創(chuàng)建。

步驟2：當(dāng)前時間為18點50分，在EMP表中插入記錄：

UPDATE EMP SET SAL=3000 WHERE EMPNO=7369;

顯示結(jié)果為：

UPDATE EMP SET SAL=3000 WHERE EMPNO=7369*ERROR 位于第 1 行:ORA-20500: 非法時間修改表錯誤！ORA-06512: 在"STUDENT.CHECK_TIME", line 5ORA-04088: 觸發(fā)器 'STUDENT.CHECK_TIME' 執(zhí)行過程中出錯

  說明：通過引發(fā)異常限制對數(shù)據(jù)庫進行的插入、刪除和修改操作的時間。SYSDATE用來獲取系統(tǒng)當(dāng)前時間，并按不同的格式字符串進行轉(zhuǎn)換。“DY”表示獲取英文表示的星期簡寫，“HH24”表示獲取24小時制時間的小時。
當(dāng)在18點50分修改表中的數(shù)據(jù)時，由于時間在8點至17點(晚5點)之外，所以產(chǎn)生“非法時間修改表錯誤”的用戶自定義錯誤，修改操作終止。
【練習(xí)1】設(shè)計一個語句級觸發(fā)器，限定只能對數(shù)據(jù)庫進行修改操作，不能對數(shù)據(jù)庫進行插入和刪除操作。在需要進行插入和刪除時，將觸發(fā)器設(shè)置為無效狀態(tài)，完成后重新設(shè)置為生效狀態(tài)。
數(shù)據(jù)庫事件觸發(fā)器
數(shù)據(jù)庫事件觸發(fā)器有數(shù)據(jù)庫級和模式級兩種。前者定義在整個數(shù)據(jù)庫上，觸發(fā)事件是數(shù)據(jù)庫事件，如數(shù)據(jù)庫的啟動、關(guān)閉，對數(shù)據(jù)庫的登錄或退出。后者定義在模式上，觸發(fā)事件包括模式用戶的登錄或退出，或?qū)?shù)據(jù)庫對象的創(chuàng)建和修改(DDL事件)。
數(shù)據(jù)庫事件觸發(fā)器的觸發(fā)事件的種類和級別如表9-3所示。

種  類	關(guān) 鍵 字	說    明模式級	CREATE	在創(chuàng)建新對象時觸發(fā)ALTER	修改數(shù)據(jù)庫或數(shù)據(jù)庫對象時觸發(fā)DROP	刪除對象時觸發(fā)數(shù)據(jù)庫級	STARTUP	數(shù)據(jù)庫打開時觸發(fā)SHUTDOWN	在使用NORMAL或IMMEDIATE選項關(guān)閉數(shù)據(jù)庫時觸發(fā)SERVERERROR 	發(fā)生服務(wù)器錯誤時觸發(fā)數(shù)據(jù)庫級與模式級	LOGON 	當(dāng)用戶連接到數(shù)據(jù)庫，建立會話時觸發(fā)LOGOFF	當(dāng)會話從數(shù)據(jù)庫中斷開時觸發(fā)

定義數(shù)據(jù)庫事件和模式事件觸發(fā)器
創(chuàng)建數(shù)據(jù)庫級觸發(fā)器需要ADMINISTER DATABASE TRIGGER系統(tǒng)權(quán)限，一般只有系統(tǒng)管理員擁有該權(quán)限。
對于模式級觸發(fā)器，為自己的模式創(chuàng)建觸發(fā)器需要CREATE TRIGGER權(quán)限，如果是為其他模式創(chuàng)建觸發(fā)器，需要CREATE ANY TRIGGER權(quán)限。
數(shù)據(jù)庫事件和模式事件觸發(fā)器的創(chuàng)建語法與DML觸發(fā)器的創(chuàng)建語法類似。數(shù)據(jù)庫事件或模式事件觸發(fā)器的創(chuàng)建語法如下：
CREATE [OR REPLACE] TRIGGER 觸發(fā)器名
{BEFORE|AFTER }
{DDL事件1 [DDL事件2...]| 數(shù)據(jù)庫事件1 [數(shù)據(jù)庫事件2...]}
ON {DATABASE| [模式名.]SCHEMA }
[WHEN (條件)]
DECLARE
聲明部分
BEGIN
主體部分
END;
其中：DATABASE表示創(chuàng)建數(shù)據(jù)庫級觸發(fā)器，數(shù)據(jù)庫級要給出數(shù)據(jù)庫事件；SCHEMA表示創(chuàng)建模式級觸發(fā)器，模式級要給出模式事件或DDL事件。
在數(shù)據(jù)庫事件觸發(fā)器中，可以使用如表9-4所示的一些事件屬性。不同類型的觸發(fā)器可以使用的事件屬性有所不同。

屬  性	適用觸發(fā)器類型	說    明Sys.sysevent	所有類型	返回觸發(fā)器觸發(fā)事件字符串Sys.instance_num	所有類型	返回Oracle實例號Sys.database_name	所有類型	返回數(shù)據(jù)庫名字Sys.server_error(stack_position)	SERVERERROR	從錯誤堆棧指定位置返回錯誤號，參數(shù)為1表示最近的錯誤Is_servererror(error_number)	SERVERERROR	判斷堆棧中是否有參數(shù)指定的錯誤號Sys.login_user	所有類型	返回導(dǎo)致觸發(fā)器觸發(fā)的用戶名Sys.dictionary_obj_type	CREATE、ALTER、DROP	返回DDL觸發(fā)器觸發(fā)時涉及的對象類型Sys. dictionary_obj_name	CREATE、ALTER、DROP	返回DDL觸發(fā)器觸發(fā)時涉及的對象名稱Sys.des_encrypted_password	CREATE、ALTER、DROP	創(chuàng)建或修改用戶時，返回加密后的用戶密碼

數(shù)據(jù)庫事件觸發(fā)器
下面是一個綜合的數(shù)據(jù)庫事件觸發(fā)器練習(xí)。先為STUDENT賬戶授予創(chuàng)建數(shù)據(jù)庫事件觸發(fā)器的權(quán)限，ADMINISTER DATABASE TRIGGER，然后創(chuàng)建有關(guān)的表和觸發(fā)器，最后予以驗證。
  【訓(xùn)練1】  創(chuàng)建觸發(fā)器，對本次數(shù)據(jù)庫啟動以來的用戶登錄時間進行記錄，每次數(shù)據(jù)庫啟動后，先清空該表。
步驟1：創(chuàng)建登錄事件記錄表：

CREATE TABLE userlog (USERNAME VARCHAR2(20),LOGON_TIME DATE);

執(zhí)行結(jié)果：

表已創(chuàng)建。

步驟2：創(chuàng)建數(shù)據(jù)庫STARTUP事件觸發(fā)器：

CREATE OR REPLACE TRIGGER INIT_LOGONAFTERSTARTUPON DATABASEBEGINDELETE FROM userlog;END;

執(zhí)行結(jié)果：

觸發(fā)器已創(chuàng)建。

步驟3：創(chuàng)建數(shù)據(jù)庫LOGON事件觸發(fā)器：

CREATE OR REPLACE TRIGGER DATABASE_LOGONAFTERLOGONON DATABASEBEGININSERT INTO userlogVALUES(sys.login_user,sysdate);END;

執(zhí)行結(jié)果：

觸發(fā)器已創(chuàng)建。

步驟4：驗證DATABASE_LOGON觸發(fā)器：

CONNECT SCOTT/TIGER@MYDB;CONNECT STUDENT/STUDENT@MYDB;

執(zhí)行結(jié)果：

已連接。已連接。

執(zhí)行查詢：

SELECT username,TO_CHAR(logon_time,'YYYY/MM/DD HH24:MI:SS') FROM userlog;

執(zhí)行結(jié)果：

USERNAME             TO_CHAR(LOGON_TIME,----------------------------- -----------------------------------------SCOTT                	2004/03/29 22:42:20STUDENT              	2004/03/29 22:42:20

步驟5：驗證INIT_LOGON觸發(fā)器。
重新啟動數(shù)據(jù)庫，登錄STUDENT賬戶：

SELECT username,TO_CHAR(logon_time,'YYYY/MM/DD HH24:MI:SS') FROM userlog;

執(zhí)行結(jié)果：

USERNAME             TO_CHAR(LOGON_TIME,-------------------------------- ---------------------------------------STUDENT              2004/03/29 22:43:59已選擇 1 行

說明：本例中共創(chuàng)建了兩個數(shù)據(jù)庫級事件觸發(fā)器。DATABASE_LOGON在用戶登錄時觸發(fā)，向表userlog中增加一條記錄，記錄登錄用戶名和登錄時間。INIT_LOGON在數(shù)據(jù)庫啟動時觸發(fā)，清除userlog表中記錄的數(shù)據(jù)。所以當(dāng)數(shù)據(jù)庫重新啟動后，重新登錄STUDENT賬戶，此時userlog表中只有一條記錄。
【訓(xùn)練2】  創(chuàng)建STUDENT_LOGON模式級觸發(fā)器，專門記錄STUDENT賬戶的登錄時間：

CREATE OR REPLACE TRIGGER STUDENT_LOGONAFTERLOGON ON STUDENT.SCHEMABEGININSERT INTO userlogVALUES(sys.login_user,sysdate);END;

執(zhí)行結(jié)果：

觸發(fā)器已創(chuàng)建。

說明：為當(dāng)前模式創(chuàng)建觸發(fā)器，可以省略SCHEMA前面的模式名。
【練習(xí)1】修改DATABASE_LOGON觸發(fā)器和userlog表，增加對退出時間的記錄。
DDL事件觸發(fā)器
【訓(xùn)練1】  通過觸發(fā)器阻止對emp表的刪除。
步驟1：創(chuàng)建DDL觸發(fā)器：

CREATE OR REPLACE TRIGGER NODROP_EMPBEFOREDROP ON SCHEMABEGINIF Sys.Dictionary_obj_name='EMP' THENRAISE_APPLICATION_ERROR(-20005,'錯誤信息：不能刪除emp表！');END IF;END;

執(zhí)行結(jié)果：

觸發(fā)器已創(chuàng)建。

步驟2：通過刪除emp表驗證觸發(fā)器：

DROP TABLE emp;

執(zhí)行結(jié)果：

DROP TABLE emp*ERROR 位于第 1 行:ORA-00604: 遞歸 SQL 層 1 出現(xiàn)錯誤ORA-20005: 錯誤信息：不能刪除emp表！ORA-06512: 在line 3

     說明：該觸發(fā)器阻止在當(dāng)前模式下對emp表的刪除，但不阻止刪除其他對象。Sys.Dictionary_obj_name屬性返回要刪除的對象名稱。
替代觸發(fā)器
【訓(xùn)練1】  在emp表的視圖上，通過觸發(fā)器修改emp表。
步驟1：創(chuàng)建視圖emp_name：

CREATE VIEW emp_name AS SELECT ename FROM emp;

執(zhí)行結(jié)果：

視圖已建立。

步驟1：創(chuàng)建替代觸發(fā)器：

CREATE OR REPLACE TRIGGER change_nameINSTEAD OF INSERT ON emp_nameDECLAREV_EMPNO NUMBER(4);BEGINSELECT MAX(EMPNO)+1 INTO V_EMPNO FROM EMP;INSERT INTO emp(empno,ename)VALUES(V_EMPNO,:new.ename);END;

執(zhí)行結(jié)果：

觸發(fā)器已創(chuàng)建。

步驟2：向emp_name視圖插入記錄：

INSERT INTO emp_name VALUES('BROWN');COMMIT;

執(zhí)行結(jié)果：

已創(chuàng)建 1 行。提交完成。

  說明：向視圖直接插入雇員名將會發(fā)生錯誤，因為emp表的雇員編號列不允許為空。通過創(chuàng)建替代觸發(fā)器，將向視圖插入雇員名稱轉(zhuǎn)換為向emp表插入雇員編號和雇員名稱，雇員編號取當(dāng)前的最大雇員編號加1。試檢查emp表的雇員列表。
【訓(xùn)練2】  在emp表的視圖emp_name上，通過觸發(fā)器阻止對emp表的刪除。
步驟1：阻止通過視圖刪除雇員，并顯示用戶自定義錯誤信息：

CREATE OR REPLACE TRIGGER delete_from_enameINSTEAD OF DELETE ON emp_nameBEGINRAISE_APPLICATION_ERROR(-20006,'錯誤信息：不能在視圖中刪除emp表的雇員！');END;

執(zhí)行結(jié)果：

觸發(fā)器已創(chuàng)建。

步驟2：通過對視圖進行刪除來驗證觸發(fā)器：

DELETE FROM emp_name;

執(zhí)行結(jié)果：

DELETE FROM emp_name*ERROR 位于第 1 行:ORA-20006: 錯誤信息：不能在視圖中刪除emp表的雇員！ORA-06512:在"STUDENT.DELETE_FROM_ENAME", line 2ORA-04088: 觸發(fā)器 'STUDENT.DELETE_FROM_ENAME' 執(zhí)行過程中出錯

說明：可以通過視圖emp_name對雇員進行刪除，比如執(zhí)行DELETE FROM emp_name語句將刪除雇員表的全部雇員。但是由于在emp_name視圖中只能看到一部分雇員信息，所以刪除可能會產(chǎn)生誤操作。通過定義一個替代觸發(fā)器，可阻止通過emp_name視圖對emp表雇員進行刪除，但不阻止直接對emp表進行刪除。
查看觸發(fā)器
【訓(xùn)練1】  顯示觸發(fā)器CHECK_TIME的體部分：

SELECT TRIGGER_BODY FROM USER_TRIGGERS WHERE TRIGGER_NAME='CHECK_TIME';

結(jié)果為：

TRIGGER_BODY----------------------------------------------------------------------------------------BEGINIF (TO_CHAR(SYSDATE,'DY') IN ('SAT','SUN'))OR TO_CHAR(SYSDATE,'HH24')< 		TRIGGER_BODY字段為LONG類型，只顯示出腳本的一部分內(nèi)容。

階段訓(xùn)練
【訓(xùn)練1】  創(chuàng)建觸發(fā)器，進行表的同步復(fù)制。
步驟1：創(chuàng)建emp表的復(fù)本employee:

CREATE TABLE employee AS SELECT * FROM emp;

執(zhí)行結(jié)果：

表已創(chuàng)建。 

步驟2：創(chuàng)建和編譯以下觸發(fā)器:

CREATE OR REPLACE TRIGGER DUPLICATE_EMPAFTERUPDATE OR INSERT OR DELETEON EMPFOR EACH ROWBEGINIF INSERTING THENINSERT INTO employeeVALUES(:new.empno,:new.ename,:new.job,:new.mgr,:new.hiredate,:new.sal,:new.comm,:new.deptno);ELSIF DELETING THENDELETE FROM employeeWHERE empno=:old.empno;ELSEUPDATE employee SETempno=:new.empno,ename=:new.ename,job=:new.job,mgr=:new.mgr,hiredate=:new.hiredate,sal=:new.sal,comm=:new.comm,deptno=:new.deptnoWHERE empno=:old.empno;END IF;END;

執(zhí)行結(jié)果：

觸發(fā)器已創(chuàng)建。

步驟3：對emp表進行插入、刪除和更新：

DELETE FROM emp WHERE empno=7934;INSERT INTO emp(empno,ename,job,sal) 		VALUES(8888,'ROBERT','ANALYST',2900);UPDATE emp SET sal=3900 WHERE empno=7788;COMMIT;

執(zhí)行結(jié)果：

已刪除 1 行。已創(chuàng)建 1 行。已更新 1 行。提交完成。 

步驟4：檢查emp表和employee表中被插入、刪除和更新的雇員。
運行結(jié)果略，請自行驗證。
  說明：在觸發(fā)器中判斷觸發(fā)事件，根據(jù)不同的事件對employee表進行不同的操作。
【練習(xí)1】創(chuàng)建一個emp表的觸發(fā)器EMP_TOTAL，每當(dāng)向雇員表插入、刪除或更新雇員信息時，將新的統(tǒng)計信息存入統(tǒng)計表EMPTOTAL，使統(tǒng)計表總能夠反映最新的統(tǒng)計信息。
統(tǒng)計表是記錄各部門雇員總?cè)藬?shù)、總工資的統(tǒng)計表，結(jié)構(gòu)如下：
部門編號 number(2)
總?cè)藬?shù) number(5)
總工資 number(10,2)
練習(xí)
1. 下列有關(guān)觸發(fā)器和存儲過程的描述，正確的是：
A. 兩者都可以傳遞參數(shù)
B. 兩者都可以被其他程序調(diào)用
C. 兩種模塊中都可以包含數(shù)據(jù)庫事務(wù)語句
D. 兩者創(chuàng)建的系統(tǒng)權(quán)限不同
2. 下列事件，屬于DDL事件的是：
    A.  INSERT B.  LOGON
    C.  DROP D.  SERVERERROR
3. 假定在一個表上同時定義了行級和語句級觸發(fā)器，在一次觸發(fā)當(dāng)中，下列說法正確的是：
    A. 語句級觸發(fā)器只執(zhí)行一次
    B. 語句級觸發(fā)器先于行級觸發(fā)器執(zhí)行
           C. 行級觸發(fā)器先于語句級觸發(fā)器執(zhí)行
                D. 行級觸發(fā)器對表的每一行都會執(zhí)行一次
4. 有關(guān)行級觸發(fā)器的偽記錄，下列說法正確的是：
   A.  INSERT事件觸發(fā)器中，可以使用:old偽記錄。
   B.  DELETE事件觸發(fā)器中，可以使用:new偽記錄。
   C.  UPDATA事件觸發(fā)器中，只能使用:new偽記錄。
                D.  UPDATA事件觸發(fā)器中，可以使用:old偽記錄。
5. 下列有關(guān)替代觸發(fā)器的描述，正確的是：
    A. 替代觸發(fā)器創(chuàng)建在表上
                B. 替代觸發(fā)器創(chuàng)建在數(shù)據(jù)庫上
                C. 通過替代觸發(fā)器可以向基表插入數(shù)據(jù)
                D. 通過替代觸發(fā)器可以向視圖插入數(shù)據(jù)