數(shù)據(jù)庫系統(tǒng)原理與開發(fā)-數(shù)據(jù)庫觸發(fā)器

六.四數(shù)據(jù)庫觸發(fā)器了解觸發(fā)器地基本概念掌握postgreSQL觸發(fā)器創(chuàng)建,修改,刪除地方法理解觸發(fā)器地執(zhí)行過程本節(jié)地主要內(nèi)容一,觸發(fā)器地基本概念觸發(fā)器是特殊類型地存儲(chǔ)過程,主要由操作(INSERT,UPDATE,DELETE)觸發(fā)而被自動(dòng)執(zhí)行。觸發(fā)器可以實(shí)現(xiàn)比約束更復(fù)雜地?cái)?shù)據(jù)完整,經(jīng)常用于加強(qiáng)數(shù)據(jù)地完整約束與業(yè)務(wù)規(guī)則。觸發(fā)器本身是一個(gè)特殊地事務(wù)單位。觸發(fā)器地特點(diǎn)與表有關(guān)聯(lián):需要定義在表或視圖上。自動(dòng)觸發(fā):由執(zhí)行INSERT,DELETE,UPDATE操作時(shí)觸發(fā)不能直接調(diào)用,也不能傳遞或接受參數(shù)是事務(wù)地一部分:觸發(fā)器與觸發(fā)語句作為可在觸發(fā)器內(nèi)回滾地單個(gè)事務(wù)。觸發(fā)器地分類按觸發(fā)地語句分為:INSERT觸發(fā)器,DELETE觸發(fā)器,UPDATE觸發(fā)器用戶動(dòng)作insertdeleteupdate應(yīng)用表inserttriggerdeletetriggerupdatetrigger數(shù)據(jù)庫觸發(fā)器地分類（續(xù)）觸發(fā)器執(zhí)行地次數(shù)可分:（一）語句級(jí)觸發(fā)器:由關(guān)鍵字FOREACHSTATEMENT聲明,在觸發(fā)器作用地表上執(zhí)行一條SQL語句時(shí),該觸發(fā)器只執(zhí)行一次,即使是修改了零行數(shù)據(jù)地SQL,也會(huì)導(dǎo)致相應(yīng)地觸發(fā)器執(zhí)行。如果都沒有被指定,FOREACHSTATEMENT會(huì)是默認(rèn)值。（二）行級(jí)觸發(fā)器:由關(guān)鍵字FOREACHROW標(biāo)記地觸發(fā)器,當(dāng)觸發(fā)器作用地表地?cái)?shù)據(jù)發(fā)生變化時(shí),每變化一行就會(huì)執(zhí)行一次觸發(fā)器。例如,假設(shè)學(xué)生成績(jī)表有DELETE觸發(fā)器,當(dāng)在該表執(zhí)行DELETE語句刪除記錄時(shí),如果刪除了二零條記錄,則將導(dǎo)致DELETE觸發(fā)器被執(zhí)行二零次。觸發(fā)器地分類（續(xù)）按觸發(fā)地時(shí)間分為三類:（一）BEFORE觸發(fā)器:在觸發(fā)之前執(zhí)行觸發(fā)器。（二）AFTER觸發(fā)器:在觸發(fā)之后執(zhí)行觸發(fā)器。（三）INSTEADOF觸發(fā)器:當(dāng)觸發(fā)發(fā)生后,執(zhí)行觸發(fā)器指定地函數(shù),而不是執(zhí)行產(chǎn)生觸發(fā)地SQL語句,從而替代產(chǎn)生觸發(fā)地SQL操作。在表或視圖上,對(duì)于INSERT,UPDATE或DELETE三種觸發(fā),每種最多可以定義一個(gè)INSTEADOF觸發(fā)器INSTEADOF觸發(fā)器觸發(fā)器有關(guān)地特殊變量（一）NEW數(shù)據(jù)類型是RECORD。對(duì)于行級(jí)觸發(fā)器,它存有INSERT或UPDATE操作產(chǎn)生地新地?cái)?shù)據(jù)行。對(duì)于語句級(jí)觸發(fā)器,它地值是NULL。（二）OLD數(shù)據(jù)類型是RECORD。對(duì)于行級(jí)觸發(fā)器,它存有被UPDATE或DELETE操作修改或刪除地舊地?cái)?shù)據(jù)行。對(duì)于語句級(jí)觸發(fā)器,它地值是NULL。（三）TG_OP數(shù)據(jù)類型是text;是值為INSERT,UPDATE,DELETE地一個(gè)字符串,它說明觸發(fā)器是為哪個(gè)操作引發(fā)。二,PostgreSQL創(chuàng)建觸發(fā)器地基本語法CREATETRIGGER觸發(fā)器名{BEFORE|AFTER|INSTEADOF}ON表名[FOR[EACH]{ROW|STATEMENT}]EXECUTEPROCEDURE存儲(chǔ)過程名(參數(shù)列表)（一）指明所定義地觸發(fā)器名（二）BEFORE|AFTER|INSTEADOF指明觸發(fā)器被觸發(fā)地時(shí)間（三）ON表名指明觸發(fā)器所依附地表（四）FOREACH{ROW|STATEMENT}指明觸發(fā)器被觸發(fā)地次數(shù)（五）EXECUTEPROCEDURE存儲(chǔ)過程名(參數(shù)列表)指明觸發(fā)時(shí)所執(zhí)行地存儲(chǔ)過程三,PostgreSQL創(chuàng)建觸發(fā)器地基本步驟:（一）檢查數(shù)據(jù)庫將要?jiǎng)?chuàng)建地觸發(fā)器所依附地表或視圖是否存在,如果不存在,需要首先創(chuàng)建該表或視圖。（二）創(chuàng)建觸發(fā)器被觸發(fā)時(shí)所要執(zhí)行地觸發(fā)器函數(shù),該函數(shù)地類型需要是TRINGER型,是觸發(fā)器地執(zhí)行函數(shù)。但要注意,有些數(shù)據(jù)庫不需要獨(dú)立定義觸發(fā)器函數(shù),而是在創(chuàng)建觸發(fā)器時(shí),定義觸發(fā)器地過程體。（三）創(chuàng)建觸發(fā)器,一般需要指明觸發(fā)器依附地表,觸發(fā)器被觸發(fā)執(zhí)行地時(shí)間,觸發(fā)器是行級(jí)觸發(fā)器還是語句級(jí)觸發(fā)器,觸發(fā)器執(zhí)行需要滿足地條件。四,創(chuàng)建觸發(fā)器地示例假設(shè)有stu_score表存儲(chǔ)學(xué)生地課程成績(jī),其表結(jié)構(gòu)如下,同時(shí)插入如下數(shù)據(jù):CREATETABLEstu_score(sidcharacter(一零)NOTNULL,cidcharacter(一零)NOTNULL,scorenumeric(五,一),CONSTRAINTstu_score_pkeyPRIMARYKEY(sid,cid))四,創(chuàng)建觸發(fā)器地例子（續(xù)）為了防止非法修改stu_score表地課程成績(jī),創(chuàng)建audit_score表記錄stu_score表地成績(jī)變化,其表結(jié)構(gòu)如下:CREATETABLEaudit_score(usernamecharacter(二零),--用戶名sidcharacter(一零),cidcharacter(一零),updatetimetext,--修改地時(shí)間oldscorenumeric(五,一),--修改前地成績(jī)newscorenumeric(五,一)--修改后地成績(jī))四,創(chuàng)建觸發(fā)器地例子（續(xù)）現(xiàn)在創(chuàng)建觸發(fā)器函數(shù)score_audit()四,創(chuàng)建觸發(fā)器地示例（續(xù)）接下來在stu_score表上創(chuàng)建觸發(fā)器score_audit_trigerCREATETRIGGERscore_audit_trigerAFTERINSERTORUPDATEORDELETEONstu_scoreFOREACHROWEXECUTEPROCEDUREscore_audit();五,驗(yàn)證觸發(fā)器地執(zhí)行將課程號(hào)為一二零一地課程成績(jī)?cè)黾右环謚pdatestu_scoresetscore=score+一wherecid='一二零一';stu_scoreaudit_score四,驗(yàn)證觸發(fā)器地執(zhí)行(續(xù))將刪除課程號(hào)為一五零一地課程成績(jī)deletefromstu_scorewherecid='一五零一';stu_scoreaudit_score四,驗(yàn)證觸發(fā)器地執(zhí)行(續(xù))錄入學(xué)號(hào)一四五零三地同學(xué)地一二零一號(hào)課程成績(jī)INSERTINTOstu_score(sid,cid,score)VALUES('一四五零三','一二零一',八三);stu_scoreaudit_score五,觸發(fā)器地修改ALTERTRIGGERnameONtable_nameRENAMETOnew_name主要參數(shù)說明:（一）name:需要修改地現(xiàn)有觸發(fā)器地名稱。（二）table_name:該觸發(fā)器作用地表地名字。（三）new_name:現(xiàn)有觸發(fā)器地新名字。

例如:將上述定義地觸發(fā)器改名為score_audit_trigALTERTRIGGERscore_audit_triggerONstu_scoreRENAMETOscore_audit_trig;六,觸發(fā)器地刪除DROPTRIGGER[IFEXISTS]nameONtable_name[CASCADE|RESTRICT]主要參數(shù)說明:（一）IFEXISTS:如果指定地觸發(fā)器不存在,那么發(fā)出提示而不是拋出錯(cuò)誤。（二）name:要?jiǎng)h除地觸發(fā)器名。（三）table_name:觸發(fā)器定義所依附地表地名稱。（五）CASC