第四章數(shù)據(jù)庫(kù)完整性-----觸發(fā)器

1、第四章第四章 數(shù)據(jù)庫(kù)完整性數(shù)據(jù)庫(kù)完整性-觸發(fā)器觸發(fā)器學(xué)習(xí)重點(diǎn): 掌握觸發(fā)器的創(chuàng)建掌握觸發(fā)器的創(chuàng)建 掌握觸發(fā)器的刪除、修改、執(zhí)行方法掌握觸發(fā)器的刪除、修改、執(zhí)行方法 掌握管理和維護(hù)觸發(fā)器的方法掌握管理和維護(hù)觸發(fā)器的方法內(nèi)容提要內(nèi)容提要概述概述觸發(fā)器的創(chuàng)建與維護(hù)觸發(fā)器的創(chuàng)建與維護(hù)利用企業(yè)管理器管理和維護(hù)觸發(fā)器利用企業(yè)管理器管理和維護(hù)觸發(fā)器一、內(nèi)容回顧學(xué)號(hào)姓名性別出生年月系已修學(xué)分2006001200600220070102007020李 麗張小天錢 進(jìn)王皓為女男男男1988-5-231988-3-241989-10-21990-1-25計(jì)算機(jī)計(jì)算機(jī)電子計(jì)算機(jī)81004課程號(hào)課程名學(xué)分C001C00

2、2C003C004數(shù)據(jù)結(jié)構(gòu)離散數(shù)學(xué)C+JAVA5343學(xué)號(hào)課程號(hào)成績(jī)200600120060012006002200600220060022007020C001C002C002C003C004c003907080806570學(xué)生表課程表成績(jī)表1.表格內(nèi)部的完整性約束2.表和表之間的完整性約束3.用戶定義的完整性約束主鍵主鍵主鍵外鍵外鍵主鍵特性：唯一、不能為空外鍵特性：對(duì)應(yīng)的值在主鍵中必須存在問(wèn)題：1、王皓為同學(xué)退學(xué)，需刪除相應(yīng)記錄，如何操作2、張小天通過(guò)一門新課考試，如何修改他的已修學(xué)分？概述概述二、使用觸發(fā)器的優(yōu)點(diǎn)二、使用觸發(fā)器的優(yōu)點(diǎn)觸發(fā)器是一種觸發(fā)器是一種特殊類型的存儲(chǔ)過(guò)程。特殊類型的存儲(chǔ)

3、過(guò)程。自動(dòng)執(zhí)行自動(dòng)執(zhí)行：在對(duì)表的數(shù)據(jù)作了任何修改之后立：在對(duì)表的數(shù)據(jù)作了任何修改之后立即被激活。即被激活。 生效生效：UPDATE、INSERT 或或 DELETE。觸發(fā)器實(shí)現(xiàn)級(jí)聯(lián)刪除與級(jí)聯(lián)修改。觸發(fā)器實(shí)現(xiàn)級(jí)聯(lián)刪除與級(jí)聯(lián)修改。觸發(fā)器可強(qiáng)制實(shí)現(xiàn)觸發(fā)器可強(qiáng)制實(shí)現(xiàn)復(fù)雜的業(yè)務(wù)規(guī)則復(fù)雜的業(yè)務(wù)規(guī)則或或要求要求。觸發(fā)器還有助于觸發(fā)器還有助于強(qiáng)制數(shù)據(jù)完整性。強(qiáng)制數(shù)據(jù)完整性。三、創(chuàng)建觸發(fā)器語(yǔ)法：三、創(chuàng)建觸發(fā)器語(yǔ)法：CREATE TRIGGER trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE

4、 WITH APPEND NOT FOR REPLICATION AS IF UPDATE ( column ) AND | OR UPDATE ( column ) .n | IF ( COLUMNS_UPDATED ( ) bitwise_operator updated_bitmask ) comparison_operator column_bitmask .n sql_statement .n 觸發(fā)器實(shí)例分析：觸發(fā)器實(shí)例分析：實(shí)例實(shí)例1、創(chuàng)建一個(gè)簡(jiǎn)單觸發(fā)器，能在對(duì)數(shù)據(jù)表、創(chuàng)建一個(gè)簡(jiǎn)單觸發(fā)器，能在對(duì)數(shù)據(jù)表student進(jìn)行插入、修改或刪除后，輸出所影進(jìn)行插入、修改或刪除后，輸出所影響的

5、行數(shù)信息。響的行數(shù)信息。create trigger simple_tri on studentafter insert,delete,updateasprint （所影響的行數(shù)（所影響的行數(shù)為為:+cast(rowcount as varchar(10)+行行)該觸發(fā)器何時(shí)被觸發(fā)？當(dāng)對(duì)student執(zhí)行insert delete update操作的時(shí)候?qū)嵗龑?shí)例2：創(chuàng)建級(jí)聯(lián)刪除觸發(fā)器：創(chuàng)建級(jí)聯(lián)刪除觸發(fā)器 為表為表student創(chuàng)建一個(gè)刪除觸發(fā)器，當(dāng)刪除創(chuàng)建一個(gè)刪除觸發(fā)器，當(dāng)刪除表表student中的一個(gè)學(xué)生信息時(shí)，同時(shí)將表中的一個(gè)學(xué)生信息時(shí)，同時(shí)將表sc中相關(guān)的成績(jī)信息一并刪除。中相關(guān)的成績(jī)信

6、息一并刪除。方法一方法一:使用外鍵使用外鍵Create table sc( sno char(8) foreign key references student(sno) on delete cascade, cno char(4) foreign key references course(cno) on delete cascade, grade int check(grade=0 and grade=100), primary key(sno,cno) n方法二方法二:使用觸發(fā)器使用觸發(fā)器,前提是前提是SC表沒(méi)有定義和表沒(méi)有定義和student表相關(guān)的外鍵表相關(guān)的外鍵ncreate tr

7、igger stu_del non student nfor deletenasnBeginnBegin transaction ndeclare stu_sno char(8)nselect stu_sno=deleted.sno from deletedndelete from sc where sc.sno=stu_snonCommit trannEnd 原子事務(wù)原子事務(wù)處理方法！處理方法！使用使用 inserted 和和 deleted 表（重要）表（重要）觸發(fā)器語(yǔ)句中使用了觸發(fā)器語(yǔ)句中使用了兩種特殊的表兩種特殊的表：deleted 表和表和 inserted 表。表。 由由DBMS自

8、動(dòng)自動(dòng)創(chuàng)建和管理創(chuàng)建和管理這些表?？梢允惯@些表?？梢允褂眠@兩個(gè)臨時(shí)的駐留內(nèi)存的表用這兩個(gè)臨時(shí)的駐留內(nèi)存的表測(cè)試某些數(shù)據(jù)修測(cè)試某些數(shù)據(jù)修改的效果及設(shè)置觸發(fā)器操作的條件改的效果及設(shè)置觸發(fā)器操作的條件；然而，；然而，不不能能直接對(duì)表中的數(shù)據(jù)進(jìn)行直接對(duì)表中的數(shù)據(jù)進(jìn)行更改更改。 Deleted 表表用于存儲(chǔ)用于存儲(chǔ) DELETE 和和 UPDATE 語(yǔ)句所影響的行的復(fù)本。語(yǔ)句所影響的行的復(fù)本。 在執(zhí)行在執(zhí)行 DELETE 或或 UPDATE 語(yǔ)句時(shí)，行語(yǔ)句時(shí)，行從觸發(fā)器表中刪除，并傳輸?shù)綇挠|發(fā)器表中刪除，并傳輸?shù)?deleted 表中。表中。Deleted 表和原數(shù)據(jù)表通常沒(méi)有相同的行。表和原數(shù)據(jù)表通常

9、沒(méi)有相同的行。思考：表結(jié)構(gòu)如何！思考：表結(jié)構(gòu)如何！ Inserted表表用于存儲(chǔ)用于存儲(chǔ)INSERT和和UPDATE 語(yǔ)語(yǔ)句所影響的行的副本。句所影響的行的副本。 在一個(gè)插入或更新事務(wù)處理中，新建行被在一個(gè)插入或更新事務(wù)處理中，新建行被同時(shí)添加到同時(shí)添加到 inserted 表和原數(shù)據(jù)表中。表和原數(shù)據(jù)表中。Inserted 表中的行是原數(shù)據(jù)表中新行的副本。表中的行是原數(shù)據(jù)表中新行的副本。思考：如何實(shí)現(xiàn)級(jí)聯(lián)修改？思考：如何實(shí)現(xiàn)級(jí)聯(lián)修改？思考：該觸發(fā)器，對(duì)同時(shí)插入或修改多條記思考：該觸發(fā)器，對(duì)同時(shí)插入或修改多條記錄時(shí)，有何操作結(jié)果。錄時(shí)，有何操作結(jié)果。使觸發(fā)器更具有通用性：使觸發(fā)器更具有通用性：C

10、reate trigger stu_del on studentfor deleteasdelete from sc where sc.sno in (select sno from deleted)實(shí)例3： insert into sc values(2006002,c001,80) ，如何修改他的已修學(xué)分？學(xué)號(hào)姓名性別出生年月系已修學(xué)分2006001200600220070102007020李 麗張小天錢 進(jìn)王皓為女男男男1988-5-231988-3-241989-10-21990-1-25計(jì)算機(jī)計(jì)算機(jī)電子計(jì)算機(jī)81004實(shí)例3： insert into sc values(200600

11、2,c001,80) ，如何修改他的已修學(xué)分？思路：1、觸發(fā)器被觸發(fā)的時(shí)候，內(nèi)存中是deleted表還是inserted表？該表的結(jié)構(gòu)如何？ 2、找到剛插入記錄的學(xué)號(hào)和課程號(hào) declare c_no char(4), s_no char(8) select c_no=cno,s_no=sno from inserted 3、找出該課程的學(xué)分 declare cdit int select cdit=credit from course where cno=c_no 4、修改該同學(xué)的已修學(xué)分 update student set credit =credit+cdit where sno=s_

12、no實(shí)例3： insert into sc values(2006002,c001,80) ，如何修改他的已修學(xué)分？nCreate triger update_credit nOn SCnAfter insertnAsnBeginn declare c_no char(4), s_no char(8),cdit intn select c_no=cno,s_no=sno from insertedn select cdit=credit from course where cno=c_non update student set credit =credit+cditn where sno=s

13、_nonend 實(shí)例實(shí)例4：定義一個(gè)數(shù)據(jù)表定義一個(gè)數(shù)據(jù)表modify_sc，用于存儲(chǔ)，用于存儲(chǔ)學(xué)生選課表中成績(jī)數(shù)據(jù)發(fā)生變化的情況。學(xué)生選課表中成績(jī)數(shù)據(jù)發(fā)生變化的情況。其定義其定義T-SQL腳本如下：腳本如下：create table modify_sc(student_no char(8) null,Course_no char(3) null,user_name char(6) null, time datetime null,grade_old int null,grade_new int null)student_noCourse_noUser_nametimeGrade_oldGrad

14、e_new如果希望：如果希望：當(dāng)執(zhí)行當(dāng)執(zhí)行update sc set grade=90 where sno=2006001 and cno= c002自動(dòng)在自動(dòng)在modify_sc表中添加一條記錄：表中添加一條記錄：利用利用user_name()利用利用Getdate()student_noCourse_noUser_nametimeGrade_oldGrade_new2006001c002admin2009-4-127090思路：1、找出被修改的同學(xué)的學(xué)號(hào)2、找出被修改的課程號(hào)3、找出修改前的成績(jī)4、找出修改后的成績(jī)5、在modify_sc表中插入記錄表中插入記錄可以通過(guò)下列觸發(fā)器實(shí)現(xiàn)：可以

15、通過(guò)下列觸發(fā)器實(shí)現(xiàn)：create trigger modify_sc1 on scAFTER UPDATEAs If update(grade)BeginDeclare grade_old tinyintDeclare grade_new tinyintDeclare student_no char(8)Declare course_no char(3)Select grade_old=(select grade from deleted) 修改前的成績(jī)修改前的成績(jī)Select grade_new=(select grade from inserted) -修改后的成績(jī)修改后的成績(jī)Select

16、 student_no=(select sno from deleted) -被修改的同學(xué)的學(xué)號(hào)被修改的同學(xué)的學(xué)號(hào)Select course_no=(select cno from deleted) -被修改的同學(xué)的課程號(hào)被修改的同學(xué)的課程號(hào)Insert into modify_scvalues (student_no,course_no,USER_NAME(), GETDATE(),grade_old,grade_new)END測(cè)試方法測(cè)試方法如執(zhí)行：如執(zhí)行：update sc set grade=90 where sno=2006001 and cno= c002查看表查看表modify_

17、sc中的記錄：中的記錄：select * from modify_sc刪除觸發(fā)器：刪除觸發(fā)器：if exists(select * from sysobjects where name=modify_sc1 and type=tr)drop trigger modify_sc1實(shí)例實(shí)例5:5:當(dāng)在當(dāng)在SCSC表中插入記錄或修改學(xué)號(hào)時(shí)表中插入記錄或修改學(xué)號(hào)時(shí), ,驗(yàn)證學(xué)驗(yàn)證學(xué)號(hào)是否正確號(hào)是否正確, ,如果不正確如果不正確, ,取消插入或修改取消插入或修改方法一方法一: :將將SCSC表的表的snosno設(shè)置為外鍵設(shè)置為外鍵方法二方法二: : (在在SC表上必須沒(méi)有設(shè)置外鍵）用觸發(fā)器表上必須沒(méi)有設(shè)

18、置外鍵）用觸發(fā)器create trigger sc_sno_checkon sc after insert,updateAS if update(sno)begin if NOT EXISTS(select * from student s, inserted i where s.sno =i.sno) begin ROLLBACK TRANSACTION Print 學(xué)號(hào)信息不在學(xué)生表中，修改學(xué)號(hào)信息不在學(xué)生表中，修改/插入插入該行不成功！該行不成功！ end Else print 該行已被修改該行已被修改/插入！插入！end 將顯式事務(wù)或隱性將顯式事務(wù)或隱性事務(wù)回滾到事務(wù)的起事務(wù)回滾到事務(wù)

19、的起點(diǎn)或事務(wù)內(nèi)的某個(gè)保點(diǎn)或事務(wù)內(nèi)的某個(gè)保存點(diǎn)存點(diǎn)!實(shí)例實(shí)例4 4、使用帶有提醒消息的觸發(fā)器、使用帶有提醒消息的觸發(fā)器 當(dāng)有人試圖在當(dāng)有人試圖在 sc表中添加或更改數(shù)據(jù)時(shí)，表中添加或更改數(shù)據(jù)時(shí)，將向客戶端顯示一條消息。將向客戶端顯示一條消息。CREATE TRIGGER reminder ON scFOR INSERT, UPDATE AS RAISERROR (50009, 16, 10)GO請(qǐng)問(wèn)請(qǐng)問(wèn):(1)RAISERROR函數(shù)用途函數(shù)用途? 在遇到問(wèn)題時(shí)，根據(jù)嚴(yán)重級(jí)別，將把在遇到問(wèn)題時(shí)，根據(jù)嚴(yán)重級(jí)別，將把 sysmessages 系統(tǒng)表中的消息，發(fā)送到客戶系統(tǒng)表中的消息，發(fā)送到客戶端。端。

20、 (2)如何自定義系統(tǒng)錯(cuò)誤消息？如何自定義系統(tǒng)錯(cuò)誤消息？Sp_addmessage 50009, 16, 請(qǐng)注意：有人試請(qǐng)注意：有人試圖對(duì)數(shù)據(jù)表進(jìn)行數(shù)據(jù)的插入圖對(duì)數(shù)據(jù)表進(jìn)行數(shù)據(jù)的插入/修改操作！修改操作！ 自定義的消息自定義的消息IDID號(hào)！號(hào)！消息的嚴(yán)重級(jí)別！消息的嚴(yán)重級(jí)別！如嚴(yán)重級(jí)別如嚴(yán)重級(jí)別 11 11 到到 1616，這些，這些消息表明錯(cuò)誤可由用戶修正！消息表明錯(cuò)誤可由用戶修正！有關(guān)錯(cuò)誤調(diào)用狀態(tài)的信息：從有關(guān)錯(cuò)誤調(diào)用狀態(tài)的信息：從 1 1 到到 127 127 的任意整數(shù)，負(fù)值的任意整數(shù)，負(fù)值默認(rèn)為默認(rèn)為 1 1（是否成功）（是否成功）刪除自定義消息：刪除自定義消息：sp_dropme

21、ssage 50009補(bǔ)充實(shí)例：在補(bǔ)充實(shí)例：在student表中，添加一列為表中，添加一列為sumgrade，記錄每位學(xué)生的總分。寫一個(gè)觸，記錄每位學(xué)生的總分。寫一個(gè)觸發(fā)器，使得發(fā)器，使得SC表中，某學(xué)生的課程成績(jī)發(fā)生表中，某學(xué)生的課程成績(jī)發(fā)生變化時(shí)，變化時(shí)，student表中該學(xué)生的表中該學(xué)生的sumgrade字字段值能夠自動(dòng)更新為新值。段值能夠自動(dòng)更新為新值。思考：課程成績(jī)發(fā)生變化包括了哪些情況？思考：課程成績(jī)發(fā)生變化包括了哪些情況？（1）插入新課程成績(jī)）插入新課程成績(jī)（2）刪除了某課程成績(jī)）刪除了某課程成績(jī)（3）修改了某課程成績(jī)）修改了某課程成績(jī)（1）在）在student表中添加新列表中

22、添加新列use studentalter table student add sumgrade smallint給給sumgrade賦值：賦值：update studentset sumgrade=(select sum(grade) from sc where sc.sno=student.sno)（2）創(chuàng)建觸發(fā)器如下：）創(chuàng)建觸發(fā)器如下：create trigger sc_changeon scfor update,insert,deleteasbegin transactionif update(grade)begin update student set sumgrade=(select

23、 sum(grade) from sc where sc.sno=student.sno) where sno in (select sno from inserted)end刪除已創(chuàng)建的觸發(fā)器的另一種寫法：刪除已創(chuàng)建的觸發(fā)器的另一種寫法：if object_id(sc_change)is not nulldrop trigger sc_changeelse begin update student set sumgrade=(select sum(grade) from sc where sc.sno=student.sno) where sno in (select sno from de

24、leted)end commit transaction另一種方法另一種方法:create trigger sc_changeon scfor update,insert,deleteasbegin transactionif exists(select * from deleted)begin update student set sumgrade=(select sum(grade) from sc where sc.sno=student.sno) where sno in (select sno from deleted)endelse begin update student set

25、 sumgrade=(select sum(grade) from sc where sc.sno=student.sno) where sno in (select sno from inserted) end commit transaction查看觸發(fā)器的內(nèi)容：查看觸發(fā)器的內(nèi)容：- Here is the statement to actually see the text of the trigger.SELECT o.id, c.textFROM sysobjects o INNER JOIN syscomments c ON o.id = c.idWHERE o.type = TR

26、 and = trig1或或 Sp_helptext trig1（6）Instead of觸發(fā)器的實(shí)例分析觸發(fā)器的實(shí)例分析INSTEAD OF INSERT 觸發(fā)器觸發(fā)器 可以在視圖或表上定義可以在視圖或表上定義 INSTEAD OF INSERT 觸發(fā)器來(lái)代替觸發(fā)器來(lái)代替 INSERT 語(yǔ)句的標(biāo)準(zhǔn)操語(yǔ)句的標(biāo)準(zhǔn)操作。通常，在視圖上定義作。通常，在視圖上定義 INSTEAD OF INSERT 觸發(fā)器以在一個(gè)或多個(gè)基表中插入數(shù)觸發(fā)器以在一個(gè)或多個(gè)基表中插入數(shù)據(jù)。據(jù)。INSTEAD OF INSERT 觸發(fā)器觸發(fā)器執(zhí)行過(guò)程執(zhí)行過(guò)程：1、 用戶或系統(tǒng)運(yùn)行用戶或系統(tǒng)運(yùn)行insert語(yǔ)句。

27、語(yǔ)句。2、 如果記錄不違反任何限制，則只插入如果記錄不違反任何限制，則只插入inserted表中。表中。3、 觸發(fā)器觸發(fā)和運(yùn)行必要的操作。觸發(fā)器觸發(fā)和運(yùn)行必要的操作。注：先檢查后插入！注：先檢查后插入！實(shí)例實(shí)例1、對(duì)、對(duì)SC創(chuàng)建一個(gè)創(chuàng)建一個(gè)instead of觸發(fā)器，確保插觸發(fā)器，確保插入的學(xué)生的學(xué)號(hào)在入的學(xué)生的學(xué)號(hào)在student表中存在。（注：進(jìn)行表中存在。（注：進(jìn)行插入數(shù)據(jù)時(shí)，先插入到插入數(shù)據(jù)時(shí)，先插入到inserted表中，再由所建的表中，再由所建的instead of觸發(fā)器執(zhí)行實(shí)際的插入。）觸發(fā)器執(zhí)行實(shí)際的插入。）use studentcreate trigger sc_sno_ch

28、eck2 on scinstead of insertas declare newsno char(8) select newsno=sno from inserted if exists(select * from student where sno= newsno ) insert into sc select * from inserted else print sorry!學(xué)號(hào)不在學(xué)生表中！學(xué)號(hào)不在學(xué)生表中！問(wèn)：插入時(shí)為結(jié)果集時(shí)，該觸發(fā)器是否有問(wèn)：插入時(shí)為結(jié)果集時(shí)，該觸發(fā)器是否有效？效？當(dāng)其中有學(xué)號(hào)不滿足條件時(shí)，將無(wú)效！當(dāng)其中有學(xué)號(hào)不滿足條件時(shí)，將無(wú)效！思考思考1：如何改進(jìn)？：如何改進(jìn)

29、？create trigger sc_sno_check2 on scinstead of insertas if exists(select * from inserted where sno not in (select sno from student) print sorry!有學(xué)號(hào)不在學(xué)生表中！有學(xué)號(hào)不在學(xué)生表中！ else insert into sc select * from inserted實(shí)例實(shí)例3 3、在視圖上創(chuàng)建、在視圖上創(chuàng)建instead of instead of 觸發(fā)器。觸發(fā)器。 use tempdb CREATE TABLE BaseTable (ID int

30、IDENTITY(1,1),-自增長(zhǎng)自增長(zhǎng) Color nvarchar(10) NOT NULL, Material nvarchar(10) NOT NULL, ComputedCol AS (Color + Material) )CREATE VIEW InsteadViewAS SELECT ID, Color, Material, ComputedColFROM BaseTableGOCREATE TRIGGER InsteadTrigger on InsteadViewINSTEAD OF INSERTASBEGIN INSERT INTO BaseTable SELECT Col

31、or, material FROM inserted END測(cè)試：測(cè)試：INSERT INTO InsteadView (ID,Color, Material) VALUES (3,NRed, NCloth, ）注：直接引用注：直接引用BaseTable的的INSERT語(yǔ)句不能為語(yǔ)句不能為 ID和和ComputedCol 列提供值。列提供值。 -下列語(yǔ)句正確下列語(yǔ)句正確INSERT INTO BaseTable (Color, Material) VALUES (NRed, NCloth)-下列語(yǔ)句錯(cuò)誤下列語(yǔ)句錯(cuò)誤.INSERT INTO BaseTable VALUES (2, NGreen, NWood, NGreenWood)-查看數(shù)據(jù)表所擁有的觸發(fā)器的系統(tǒng)存儲(chǔ)過(guò)程：查看數(shù)據(jù)表所擁有的觸發(fā)器的系統(tǒng)存儲(chǔ)過(guò)程：sp_helptrigger insteadview然而，通過(guò)然而，通過(guò) InsteadView 的的 INSERT 語(yǔ)句必須為語(yǔ)句必須為 ID 和和 ComputedCol 列提供值：列提供值：INSERT INTO InsteadView (ID, Color, Material, ComputedCol)VALUES (999, NBlue, NPlastic, NXXXXXX)傳遞到傳遞到 InsteadTrigger 的的