SQL Server2000實驗指導NEW實驗9觸發(fā)器

實驗9觸發(fā)器

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

}

{

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

AS

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

)]

[...n

]

}]

SQL語句[...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í)行的時間（1）在UPDATE、INSERT、DELETE語句執(zhí)行后自動觸發(fā)執(zhí)行。（2）對“FOR|AFTER”選項，觸發(fā)器表中若定義了約束，則先處理約束，后執(zhí)行觸發(fā)器程序。【實驗目的】掌握觸發(fā)器的創(chuàng)建、修改、刪除及其使用方法。掌握觸發(fā)器的功能。

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

數(shù)據庫jxsk中的觸發(fā)器實驗9觸發(fā)器

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

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

（a）C表數(shù)據

刪除記錄后的C和SC表數(shù)據（b）SC表數(shù)據實驗9.2修改觸發(fā)器【實驗目的】掌握使用企業(yè)管理器修改觸發(fā)器；掌握使用SQL修改觸發(fā)器。【實驗內容】用企業(yè)管理器修改觸發(fā)器TRIGGER_S。要求：若刪除表S中某一個學生的記錄信息，則自動刪除SC表中與學生相關的記錄信息。用SQL修改表C的觸發(fā)器TRIGGER_DC：通過課程名從C表中刪除某課程信息，同時刪除SC表和TC表中與此課程相關的記錄。

實驗9.2修改觸發(fā)器

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

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

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

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

【實驗目的】掌握使用企業(yè)管理器刪除觸發(fā)器；掌握使用SQL刪除觸發(fā)器?！緦嶒瀮热荨坑闷髽I(yè)管理器刪除表S的觸發(fā)器TRIGGER_S。；用SQL刪除表C的觸發(fā)器TRIGGER_DC?！緦嶒灢襟E】1．用企業(yè)管理器刪除表S的觸發(fā)器TRIGGER_S

刪除觸發(fā)器TRIGGER_S實驗9.3刪除觸發(fā)器

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

【實驗題報告】內容與要求基于實驗六中的“教學數(shù)據庫jxsk”，創(chuàng)建下面觸發(fā)器，并給出正反實例：1.為表SC創(chuàng)建一觸發(fā)器：當插入或修改一個記錄時，確保此記錄的成績在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ā)器：當職稱從“講師”晉升為“副教授”時，崗位津貼自動增加500元；從“副教授”晉升為“教授”時，崗位津貼自動增加8900元。5.為表SC創(chuàng)建一觸發(fā)器，將成績按下列對應關系由分數(shù)轉換成等級：小于60分：不及格；60—70分：及格；70—80：中；80—90分：良；90以上：優(yōu)。實驗9習題

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

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