SQL Server2000實(shí)驗(yàn)指導(dǎo)NEW實(shí)驗(yàn)9觸發(fā)器

實(shí)驗(yàn)9觸發(fā)器

在SQLServer中，觸發(fā)器同存儲過程一樣重要，它們都是用Tranact-SQL語言所編寫的程序。通過本實(shí)驗(yàn)來學(xué)習(xí)、掌握觸發(fā)器的創(chuàng)建、修改、刪除、執(zhí)行的方法?！局R要點(diǎn)】1．觸發(fā)器（Trigger）觸發(fā)器（trigger）是一種特殊類型的存儲過程，它也是用Tranact-SQL語言編寫的程序。存儲過程是由用戶利用命令EXECUTE執(zhí)行它，而觸發(fā)器是在用戶要對某一表內(nèi)的數(shù)據(jù)做插入INSERT、更新UPDATE、刪除DELETE時(shí)被觸發(fā)執(zhí)行。通常我們使用觸發(fā)器來檢查用戶對數(shù)據(jù)庫表的更新是否合乎整個(gè)應(yīng)用系統(tǒng)的需求和合乎商業(yè)規(guī)則以維持表內(nèi)數(shù)據(jù)的完整性和正確性。2．觸發(fā)器的作用（1）觸發(fā)器可通過數(shù)據(jù)庫中的相關(guān)表實(shí)現(xiàn)級聯(lián)更改。通過級聯(lián)引用完整性約束可以更有效地執(zhí)行這些更改。（2）觸發(fā)器可以強(qiáng)制比用CHECK約束定義的約束更為復(fù)雜的約束。與CHECK約束不同，觸發(fā)器可以引用其它表中的列。例如，觸發(fā)器可以使用另一個(gè)表中的SELECT比較插入或更新的數(shù)據(jù)，以及執(zhí)行其它操作，如修改數(shù)據(jù)或顯示用戶定義錯誤信息。實(shí)驗(yàn)9觸發(fā)器觸發(fā)器的主要好處在于它們可以包含使用Transact-SQL代碼的復(fù)雜處理邏輯。因此，觸發(fā)器可以支持約束的所有功能（3）觸發(fā)器也可以評估數(shù)據(jù)修改前后的表狀態(tài)，并根據(jù)其差異采取對策。（4）一個(gè)表中的多個(gè)同類觸發(fā)器（INSERT、UPDATE或DELETE）允許采取多個(gè)不同的對策以響應(yīng)同一個(gè)修改語句。3．觸發(fā)器類型UPDATEINSERTDELETE4．創(chuàng)建觸發(fā)器的SQL語句CREATETRIGGER觸發(fā)器名ON{表名|視圖名

}

{

{{FOR|AFTER|INSTEADOF}{[INSERT][,UPDATE][,DELETE]}

AS

[{IFUPDATE(列名)[{AND|OR}UPDATE(列名

)]

[...n

]

}]

SQL語句[...n

]

}

}

實(shí)驗(yàn)9觸發(fā)器

5．修改觸發(fā)器的SQL語句ALTERTRIGGER觸發(fā)器名

ON{表名|視圖名

}

{

{{FOR|AFTER|INSTEADOF}{[INSERT][,UPDATE][,DELETE]}

AS

[{IFUPDATE(列名)[{AND|OR}UPDATE(列名)]

[...n]

}]

SQL語句[...n

]

}

}6．刪除觸發(fā)器的SQL語句

DROPTRIGGER{觸發(fā)器名

}[,...n

]7．觸發(fā)器執(zhí)行的時(shí)間（1）在UPDATE、INSERT、DELETE語句執(zhí)行后自動觸發(fā)執(zhí)行。（2）對“FOR|AFTER”選項(xiàng)，觸發(fā)器表中若定義了約束，則先處理約束，后執(zhí)行觸發(fā)器程序?！緦?shí)驗(yàn)?zāi)康摹空莆沼|發(fā)器的創(chuàng)建、修改、刪除及其使用方法。掌握觸發(fā)器的功能。

實(shí)驗(yàn)9.1創(chuàng)建和執(zhí)行觸發(fā)器

【實(shí)驗(yàn)?zāi)康摹空莆帐褂闷髽I(yè)管理器創(chuàng)建觸發(fā)器；掌握使用SQL創(chuàng)建觸發(fā)器；掌握觸發(fā)器的執(zhí)行。【實(shí)驗(yàn)內(nèi)容】用企業(yè)管理器為表S創(chuàng)建一級聯(lián)更新觸發(fā)器TRIGGER_S。要求：若修改S表中一學(xué)生的學(xué)號，則SC表中與該學(xué)生相關(guān)的學(xué)號自動修改。用企業(yè)管理器為表SC創(chuàng)建一限制更新觸發(fā)器TRIGGER_SC。要求：若修改SC表中一記錄的學(xué)號，則要檢查表S中是否存在與該學(xué)號相同的記錄，若有則不許修改，若沒有則可修改。用SQL為表SC創(chuàng)建一觸發(fā)器Score_sc_tri。要求：當(dāng)插入一個(gè)記錄或修改成績時(shí)，確保此記錄的成績在0～100之間。用SQL為表C創(chuàng)建一級聯(lián)刪除觸發(fā)器TRIGGER_DC：通過課程名從C表中刪除某課程信息，同時(shí)刪除SC表中與此課程相關(guān)的選課記錄。實(shí)驗(yàn)9.1創(chuàng)建和執(zhí)行觸發(fā)器

【實(shí)驗(yàn)步驟】1．用企業(yè)管理器為表S創(chuàng)建一級聯(lián)更新觸發(fā)器TRIGGER_S。（1）創(chuàng)建觸發(fā)器。

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

觸發(fā)器屬性窗口實(shí)驗(yàn)9.1創(chuàng)建和執(zhí)行觸發(fā)器

創(chuàng)建觸發(fā)器實(shí)驗(yàn)9.1創(chuàng)建和執(zhí)行觸發(fā)器（2）驗(yàn)證觸發(fā)器的作用

表S和表SC中的數(shù)據(jù)（a）數(shù)據(jù)表S（b）數(shù)據(jù)表SC觸發(fā)器級聯(lián)更新表S和表SC中的數(shù)據(jù)（a）數(shù)據(jù)表S（b）數(shù)據(jù)表SC實(shí)驗(yàn)9.1創(chuàng)建和執(zhí)行觸發(fā)器

2．企業(yè)管理器為表SC創(chuàng)建一限制更新觸發(fā)器TRIGGER_SC

要求：若修改SC表中一記錄的學(xué)號，則要檢查表S中是否存在與該學(xué)號相同的記錄，若有則不許修改，若沒有則可修改。（1）創(chuàng)建觸發(fā)器

（2）驗(yàn)證觸發(fā)器的作用創(chuàng)建觸發(fā)器TRIGGER_SC觸發(fā)器級聯(lián)更新表S和表SC中的數(shù)據(jù)（a）數(shù)據(jù)表S（b）數(shù)據(jù)表SC實(shí)驗(yàn)9.1創(chuàng)建和執(zhí)行觸發(fā)器

3．用SQL為表SC創(chuàng)建一觸發(fā)器Score_sc_tri

要求：當(dāng)插入一個(gè)記錄或修改成績時(shí)，確保此記錄的成績在0—100之間。（1）創(chuàng)建觸發(fā)器數(shù)據(jù)庫jxsk的觸發(fā)器對象創(chuàng)建觸發(fā)器的SQL實(shí)驗(yàn)9.1創(chuàng)建和執(zhí)行觸發(fā)器

3．用SQL為表SC創(chuàng)建一觸發(fā)器Score_sc_tri

要求：當(dāng)插入一個(gè)記錄或修改成績時(shí)，確保此記錄的成績在0～100之間。（2）驗(yàn)證觸發(fā)器的作用

驗(yàn)證觸發(fā)器的SQL操作操作后的表SC數(shù)據(jù)表SC的數(shù)據(jù)表實(shí)驗(yàn)9.1創(chuàng)建和執(zhí)行觸發(fā)器

4．用SQL為表C創(chuàng)建一個(gè)級聯(lián)刪除觸發(fā)器TRIGGER_DC：通過課程名從C表中刪除某課程信息，同時(shí)刪除SC表中與此課程相關(guān)的選課記錄（1）創(chuàng)建觸發(fā)器

執(zhí)行SQL創(chuàng)建觸發(fā)器

數(shù)據(jù)庫jxsk中的觸發(fā)器實(shí)驗(yàn)9觸發(fā)器

（2）驗(yàn)證觸發(fā)器的作用。

為執(zhí)行刪除SQL時(shí)表C和SC數(shù)據(jù)窗口（a）表C數(shù)據(jù)窗口（b）表SC數(shù)據(jù)窗口對表C執(zhí)行刪除SQL實(shí)驗(yàn)9觸發(fā)器

（a）C表數(shù)據(jù)

刪除記錄后的C和SC表數(shù)據(jù)（b）SC表數(shù)據(jù)實(shí)驗(yàn)9.2修改觸發(fā)器【實(shí)驗(yàn)?zāi)康摹空莆帐褂闷髽I(yè)管理器修改觸發(fā)器；掌握使用SQL修改觸發(fā)器?！緦?shí)驗(yàn)內(nèi)容】用企業(yè)管理器修改觸發(fā)器TRIGGER_S。要求：若刪除表S中某一個(gè)學(xué)生的記錄信息，則自動刪除SC表中與學(xué)生相關(guān)的記錄信息。用SQL修改表C的觸發(fā)器TRIGGER_DC：通過課程名從C表中刪除某課程信息，同時(shí)刪除SC表和TC表中與此課程相關(guān)的記錄。

實(shí)驗(yàn)9.2修改觸發(fā)器

【實(shí)驗(yàn)步驟】1．用企業(yè)管理器修改表S的觸發(fā)器TRIGGER_S

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

觸發(fā)器屬性窗口把文本框中的SQL語句修改成下面的內(nèi)容：CreatetriggerTRIGGER_SOnSForDELETEAsdeclare@SNO_DELCHAR(2)Select@SNO_DEL=SNOfromDELETEDDELETEfromSCwhereSNO=@SNO_DEL實(shí)驗(yàn)9.2修改觸發(fā)器

2．用SQL修改表C的觸發(fā)器TRIGGER_DC：要求：通過課程名從C表中刪除某課程信息，同時(shí)刪除SC表和TC表中與此課程相關(guān)的記錄。在查詢窗口中輸入下面SQL語句：USEjxskGOAltertriggerTRIGGER_DCOnSCForDELETEAsdeclare@CNO_DELCHAR（2）Select@CNO_DEL=CNOfromDELETEDDELETEfromSCWHERECNO=@CNO_DELDELETEfromTCWHERECNO=@CNO_DELGO實(shí)驗(yàn)9.3刪除觸發(fā)器

【實(shí)驗(yàn)?zāi)康摹空莆帐褂闷髽I(yè)管理器刪除觸發(fā)器；掌握使用SQL刪除觸發(fā)器?！緦?shí)驗(yàn)內(nèi)容】用企業(yè)管理器刪除表S的觸發(fā)器TRIGGER_S。；用SQL刪除表C的觸發(fā)器TRIGGER_DC。【實(shí)驗(yàn)步驟】1．用企業(yè)管理器刪除表S的觸發(fā)器TRIGGER_S

刪除觸發(fā)器TRIGGER_S實(shí)驗(yàn)9.3刪除觸發(fā)器

2．用SQL刪除表C的觸發(fā)器TRIGGER_DC。在查詢窗口中輸入下面SQL語句：USEjxskDROPTRIGGERTRIGGER_DCGO實(shí)驗(yàn)9習(xí)題

【實(shí)驗(yàn)題報(bào)告】內(nèi)容與要求基于實(shí)驗(yàn)六中的“教學(xué)數(shù)據(jù)庫jxsk”，創(chuàng)建下面觸發(fā)器，并給出正反實(shí)例：1.為表SC創(chuàng)建一觸發(fā)器：當(dāng)插入或修改一個(gè)記錄時(shí)，確保此記錄的成績在0—100之間。2.為教師表T創(chuàng)建一觸發(fā)器：男職工年齡不能超過60周歲，女職工職稱是“教授”的年齡不超過60歲，其他女職工年齡不能超過55歲。3.為表C、表TC和表SC創(chuàng)建參照完整性：級聯(lián)刪除和級聯(lián)修改觸發(fā)器。4.為表T創(chuàng)建一觸發(fā)器：當(dāng)職稱從“講師”晉升為“副教授”時(shí)，崗位津貼自動增加500元；從“副教授”晉升為“教授”時(shí)，崗位津貼自動增加8900元。5.為表SC創(chuàng)建一觸發(fā)器，將成績按下列對應(yīng)關(guān)系由分?jǐn)?shù)轉(zhuǎn)換成等級：小于60分：不及格；60—70分：及格；70—80：中；80—90分：良；90以上：優(yōu)。實(shí)驗(yàn)9習(xí)題

【思考題】1．你認(rèn)為用觸發(fā)器都能定義哪些約束？答：

。2．都可以為哪些操作創(chuàng)建觸發(fā)器？可以為查詢操作創(chuàng)建觸發(fā)器嗎？答：