善知堂()第十三章觸發(fā)器善知堂()知識(shí)回顧Refcursor

派生refcursor類型數(shù)據(jù)包 數(shù)據(jù)包說(shuō)明數(shù)據(jù)包主體調(diào)用數(shù)據(jù)包中的方法和類型善知堂()觸發(fā)器類型

DML觸發(fā)器Insteadof觸發(fā)器系統(tǒng)觸發(fā)器善知堂()內(nèi)容摘要觸發(fā)器的類型創(chuàng)建觸發(fā)器善知堂()創(chuàng)建觸發(fā)器的語(yǔ)法CREATE[ORREPLACE]TRIGGERtrigger_name{BEFORE|AFTER|INSTEADOF}triggering_eventreferencing_clause[WHENtrigger_condition][FOREACHROW]trigger_body;

其中，trigger_name是觸發(fā)器的名稱，triggering_event說(shuō)明了激發(fā)觸發(fā)器的事件（也可能包括特殊的表或視圖），trigger_body是觸發(fā)器的代碼。referencing_clause用來(lái)引用正在處于修改狀態(tài)下的行中的數(shù)據(jù)，如果在WHEN子句中指定trigger_condition的話，則首先對(duì)該條件求值。觸發(fā)器主體只有在該條件為真值時(shí)才運(yùn)行。我們?cè)谙旅鎺坠?jié)中將演示不同類型的觸發(fā)器案例。善知堂()Dml觸發(fā)器分類1、根據(jù)觸發(fā)事件劃分insert/update/delete2、根據(jù)觸發(fā)時(shí)間劃分before——先觸發(fā)后執(zhí)行after——先執(zhí)行后觸發(fā)3、根據(jù)觸發(fā)級(jí)別劃分語(yǔ)句級(jí)：每個(gè)dml語(yǔ)句不論影響了多少行都只觸發(fā)1次記錄級(jí)：針對(duì)dml語(yǔ)句影響的每個(gè)行都會(huì)觸發(fā)一次善知堂()Dml觸發(fā)器案例一要求：用戶下班時(shí)間和周末不能更改emp表分析思路：觸發(fā)事件：對(duì)emp表的update操作觸發(fā)時(shí)間：使用前觸發(fā)可以避免不必要的回滾所以選擇before觸發(fā)觸發(fā)級(jí)別：每個(gè)語(yǔ)句觸發(fā)一次就可以代碼描述：當(dāng)用戶對(duì)表進(jìn)行更改操作的時(shí)候，判斷當(dāng)前的系統(tǒng)時(shí)間，如果是周末或者下班時(shí)間就回滾或拋出異常善知堂()Dml觸發(fā)器案例二要求：用戶對(duì)emp表的sal更改量不能超過(guò)10%分析思路：觸發(fā)事件：對(duì)emp表的update操作觸發(fā)時(shí)間：使用前觸發(fā)可以避免不必要的回滾所以選擇before觸發(fā)觸發(fā)級(jí)別：因?yàn)闀?huì)影響到多行因此使用行觸發(fā)，針對(duì)被更改的每一行都觸發(fā)一次代碼描述：當(dāng)用戶對(duì)表進(jìn)行更改操作的時(shí)候，根據(jù)更改后的sal(使用:new.sal)和更改前的sal(使用:old.sal)，判斷更改量是否超過(guò)10%，如果超出了就拋出異常。注意：如果要使用:old和:new一定是行觸發(fā)(foreachrow)善知堂()Dml觸發(fā)器案例三要求：現(xiàn)在有兩個(gè)表，商品庫(kù)存表，商品交易表，如果交易表中插入記錄a商品進(jìn)貨10個(gè)，那么a商品的庫(kù)存就增加10個(gè)，如果銷(xiāo)售10個(gè)，a的庫(kù)存就減少10個(gè)，如果請(qǐng)求的數(shù)量超出庫(kù)存，我們就提示出錯(cuò)分析思路：觸發(fā)事件：對(duì)商品交易表的insert操作觸發(fā)時(shí)間：使用前觸發(fā)可以避免不必要的回滾所以選擇before觸發(fā)觸發(fā)級(jí)別：因?yàn)橐褂?old和:new一定是行觸發(fā)(foreachrow)代碼描述：根據(jù)插入的記錄首先判斷交易類型，如果是進(jìn)貨就更改商品庫(kù)存表把相應(yīng)商品的庫(kù)存加上進(jìn)貨數(shù)量，如果是出貨首先判斷該商品是否有足夠的庫(kù)存，如果有就更改該庫(kù)存，如果庫(kù)存不足就拋出異常。善知堂()可變更與不可變更視圖要求：現(xiàn)在有兩個(gè)表，商品庫(kù)存表，商品交易表，如果交易表中插入記錄a商品進(jìn)貨10個(gè)，那么a商品的庫(kù)存就增加10個(gè)，如果銷(xiāo)售10個(gè)，a的庫(kù)存就減少10個(gè)，如果請(qǐng)求的數(shù)量超出庫(kù)存，我們就提示出錯(cuò)分析思路：觸發(fā)事件：對(duì)商品交易表的insert操作觸發(fā)時(shí)間：使用前觸發(fā)可以避免不必要的回滾所以選擇before觸發(fā)觸發(fā)級(jí)別：因?yàn)橐褂?old和:new一定是行觸發(fā)(foreachrow)代碼描述：根據(jù)插入的記錄首先判斷交易類型，如果是進(jìn)貨就更改商品庫(kù)存表把相應(yīng)商品的庫(kù)存加上進(jìn)貨數(shù)量，如果是出貨首先判斷該商品是否有足夠的庫(kù)存，如果有就更改該庫(kù)存，如果庫(kù)存不足就拋出異常。善知堂()善知堂()善知堂()善知堂()善知堂()善知堂()善知堂()善知堂()善知堂()善知堂()替代觸發(fā)器善知堂()CreateOrReplaceTriggertrgInsertViewInsteadorInertonStuSubScoreReferencingNewNForEachRowdeclareiCountint;BeginSelectCount(*)IntoiCountFromStudentWhereStuid=:N.StuID;IfiCount=0thenInsertIntoStudent(StuID,stuName)Values(:N.StuID,:n.stuname);ElseUpdateStudentSetStudent.stuName=:N.stuNameWhereStuID=:N.StuID;Endif;SelectCount(*)IntoiCountFromSubjectWhereSubjectID=:n.SubjectID;ifiCount=0ThenInsetIntoSubjectValues(:N.SubjectID,:N.SubjectName);ElseUpdateSubjectSetSubject.SubjectName:=N.SubjectNameWhereSubjectID=:N.SubjectID;Endif;SelectCount(*)IntoiCountFromScoreWhereSubjectID=:n.SubjectIDAndstuid=n.tuid;ifiCount=0ThenInsetIntoscoreValues(:N.stuid,:N.SubjectID,:nscore);ElseUpdatescoreSetscore.score:=N.scoreWhereSubjectID=:N.SubjectIDandstuid=:n.stuid;Endif;end;/善知堂()替代觸發(fā)器可以替代我們對(duì)不可操作視圖的操作，也就是我們對(duì)不可操作試圖的dml操作都會(huì)激發(fā)觸發(fā)器而不去做其他的操作。替代觸發(fā)器案例：制作一個(gè)視圖：Createviewdeptsalasselectdeptno,sum(sal)totsalfromempgroupbydeptno;大家可以分析出來(lái)該視圖的totsal列是不能更改的，我們要寫(xiě)一個(gè)insteadof觸發(fā)器來(lái)實(shí)現(xiàn)對(duì)該視圖的更改，把用戶的更改量平均分配給每個(gè)該部門(mén)的員工。詳細(xì)代碼：參看ppt附件中的觸發(fā)器代碼善知堂()系統(tǒng)觸發(fā)器正如我們?cè)谇皫坠?jié)所看到的，DML和替代觸發(fā)器都在（或代替）DML事件，即INSERT、UPDATE、DELETE語(yǔ)句上激活。而系統(tǒng)觸發(fā)器可以在兩種不同的事件即DDL或數(shù)據(jù)庫(kù)上激活。DDL事件包括CREATE、ALTER或DROP語(yǔ)句，而數(shù)據(jù)庫(kù)事件包括服務(wù)器的啟動(dòng)或關(guān)閉，用戶的登錄或退出，以及服務(wù)器錯(cuò)誤。創(chuàng)建系統(tǒng)觸發(fā)器的語(yǔ)法如下：CREATE[ORREPLACE]TRIGGER[schema.]trigger_name{BEFORE|AFTER}{ddl_event_list|database_event_list}ON{DATABASE|[schema.]SCHEMA}[when_clause]trigger_body;

其中，ddl_event_list是一個(gè)或多個(gè)DDL事件（事件之間用OR分隔），database_event_list是一個(gè)或多個(gè)數(shù)據(jù)庫(kù)事件（事件之間用‘OR’分隔）。善知堂()系統(tǒng)觸發(fā)器級(jí)別方案級(jí) 相當(dāng)于用戶級(jí)，對(duì)某一用戶的指定操作生效數(shù)據(jù)庫(kù)級(jí) 對(duì)所有用戶的操作生效善知堂()createorreplacetriggerlogtimeafterlogonondatabasebegininsertintoscott.userlogvalues(user,sysdate);endlogtime;/善知堂()系統(tǒng)觸發(fā)器案例例如，假設(shè)我們要記錄對(duì)象創(chuàng)建的時(shí)間，創(chuàng)建人，對(duì)象擁有者，對(duì)象類型和名稱，我們可以通過(guò)創(chuàng)建下面的表來(lái)實(shí)現(xiàn)上述記錄功能：CREATETABLEddl_creations(user_idVARCHAR2(30),object_typeVARCHAR2(20),object_nameVARCHAR2(30),object_ownerVARCHAR2(30),creation_dateDATE);

我們要求數(shù)據(jù)庫(kù)中的人和用戶發(fā)出create命令，我們都把這個(gè)命令創(chuàng)建的創(chuàng)建人，對(duì)象類型，對(duì)象的名稱，擁有者，創(chuàng)建日期記錄在該表中。善知堂()系統(tǒng)觸發(fā)器案例（續(xù)）分析思路：觸發(fā)事件：任何用戶的create事件觸發(fā)時(shí)間：一定是創(chuàng)建成功后我們才記錄該操作因此使用after觸發(fā)，先執(zhí)行創(chuàng)建語(yǔ)句后執(zhí)行觸發(fā)器觸發(fā)級(jí)別：因?yàn)橐槍?duì)任何用戶的create事件所以是數(shù)據(jù)庫(kù)級(jí)別觸發(fā)代碼描述：觸發(fā)器中只有一句insert語(yǔ)句，使用USER獲得用戶名，使用SYS.DICTIONARY_OBJ_TYPE獲得當(dāng)前創(chuàng)建