網(wǎng)絡(luò)數(shù)據(jù)庫(kù)講稿(觸發(fā)器)

1、觸發(fā)器 P191一、觸發(fā)器概述1是一種特殊的存儲(chǔ)過程。2不能被顯式調(diào)用，只能由一些特定的操作(命令)引發(fā)(激活)，這些命令是：INSERT、DELETE、UPDATE、CREATE、ALTER、DROP、GRANT、DENY、REVOKE等。3每個(gè)觸發(fā)器由三部分組成觸發(fā)器名稱：命名規(guī)則與變量名、存儲(chǔ)過程名相同；激活該觸發(fā)器的操作(命令動(dòng)詞)。一個(gè)存儲(chǔ)過程。4使用觸發(fā)器可強(qiáng)制實(shí)現(xiàn)數(shù)據(jù)的一致性和完整性。二、觸發(fā)器的分類(按引發(fā)命令分類) 及其工作原理1DML觸發(fā)器(表級(jí))(1)觸發(fā)器必須依附于某一個(gè)表(觸發(fā)器必須被包含在某一個(gè)表中)。(2)激活該觸發(fā)器的操作：可以是INSERT、DELETE、UP

2、DATE之一。(3)當(dāng)上述操作發(fā)生在包含觸發(fā)器的表上時(shí)，觸發(fā)器中的過程被自動(dòng)執(zhí)行。(4)支持兩種類型的觸發(fā)器：AFTER可以為同一表的同一操作定義多個(gè)該類型的觸發(fā)器，并可定義執(zhí)行順序；INSTEAD OF同一表的同一操作只能定義一個(gè)該類型的觸發(fā)器。(5)工作原理 P193臨時(shí)邏輯表INSERTED和DELETED。2DDL觸發(fā)器(數(shù)據(jù)庫(kù)級(jí)和服務(wù)器級(jí))(1)觸發(fā)器必須依附于某一個(gè)數(shù)據(jù)庫(kù)(觸發(fā)器必須被包含在某一個(gè)數(shù)據(jù)庫(kù)中)。(2)激活該觸發(fā)器的操作：可以是CREATE、ALTER、DROP、GRANT、DENY、REVOKE等之一。(3)當(dāng)上述操作發(fā)生在包含觸發(fā)器的數(shù)據(jù)庫(kù)上時(shí)，觸發(fā)器中的過程被自動(dòng)

3、執(zhí)行。(4)只有AFTER型觸發(fā)器。三、創(chuàng)建DML觸發(fā)器 P194用命令CREATE TRIGGER創(chuàng)建例：1 CREATE TRIGGER 增加學(xué)生學(xué)分 ON 成績(jī) /只能增加一條記錄FOR INSERTASDECLARE XH CHAR(6),KCH CHAR(8),FS DECIMAL(4,1)SELECT XH=學(xué)號(hào),KCH=課程編號(hào),FS=分?jǐn)?shù) FROM INSERTEDIF FS>=60 UPDATE 學(xué)生 SET 已修學(xué)分=已修學(xué)分+(SELECT 學(xué)分 FROM 課程 WHERE 課程編號(hào)=KCH) WHERE 學(xué)號(hào)=XH2CREATE TRIGGER 減少學(xué)生學(xué)分 ON

4、 成績(jī)FOR DELETEASUPDATE 學(xué)生 SET 已修學(xué)分=已修學(xué)分-(SELECT SUM(學(xué)分) FROM 課程WHERE 課程編號(hào) IN(SELECT DISTINCT 課程編號(hào) FROM DELETEDWHERE 學(xué)生.學(xué)號(hào)=DELETED.學(xué)號(hào) AND 分?jǐn)?shù)>=60) WHERE EXISTS(SELECT DISTINCT 課程編號(hào) FROM DELETEDWHERE 學(xué)生.學(xué)號(hào)=DELETED.學(xué)號(hào) AND 分?jǐn)?shù)>=60)3.CREATE TRIGGER 修改學(xué)生學(xué)分 ON 成績(jī)FOR UPDATEASUPDATE 學(xué)生 SET 已修學(xué)分=已修學(xué)分-(SELE

5、CT SUM(學(xué)分) FROM 課程WHERE 課程編號(hào) IN(SELECT DISTINCT 課程編號(hào) FROM DELETEDWHERE 學(xué)生.學(xué)號(hào)=DELETED.學(xué)號(hào) AND 分?jǐn)?shù)>=60)WHERE EXISTS(SELECT DISTINCT 課程編號(hào) FROM DELETEDWHERE 學(xué)生.學(xué)號(hào)=DELETED.學(xué)號(hào) AND 分?jǐn)?shù)>=60)UPDATE 學(xué)生 SET 已修學(xué)分=已修學(xué)分+(SELECT SUM(學(xué)分) FROM 課程WHERE 課程編號(hào) IN(SELECT DISTINCT 課程編號(hào) FROM INSERTEDWHERE 學(xué)生.學(xué)號(hào)=INSERTED

6、.學(xué)號(hào) AND 分?jǐn)?shù)>=60)WHERE EXISTS(SELECT DISTINCT 課程編號(hào) FROM INSERTEDWHERE 學(xué)生.學(xué)號(hào)=INSERTED.學(xué)號(hào) AND 分?jǐn)?shù)>=60)4.“修改學(xué)生學(xué)分”也可改為：CREATE TRIGGER 修改學(xué)生學(xué)分 ON 成績(jī)FOR UPDATEASUPDATE 學(xué)生 SET 已修學(xué)分=(SELECT SUM(學(xué)分) FROM 課程WHERE 課程編號(hào) IN(SELECT 課程編號(hào) FROM 成績(jī)WHERE 學(xué)生.學(xué)號(hào)=成績(jī).學(xué)號(hào) AND 分?jǐn)?shù)>=60)然后可執(zhí)行下列命令，并檢驗(yàn)執(zhí)行結(jié)果。-INSERT INTO 成績(jī) VA

7、LUES('200202','L2020308',83)-INSERT INTO 成績(jī) VALUES('200202','L2030501',80)-INSERT INTO 成績(jī) VALUES('200202','L2030506',75)-DELETE FROM 成績(jī) WHERE 學(xué)號(hào)='200202' AND LEFT(課程編號(hào),1)='L'-UPDATE 成績(jī) SET 分?jǐn)?shù)=80 WHERE 學(xué)號(hào)='200202' AND LEFT(課程編號(hào),1

8、)='L'四、創(chuàng)建DDL觸發(fā)器 P198例:1. P1992. CREATE TRIGGER 禁刪數(shù)據(jù)庫(kù)ON ALL SERVERFOR DROP_DATABASEASPRINT '請(qǐng)不要?jiǎng)h除數(shù)據(jù)庫(kù)!'ROLLBACK TRANSACTIONDROP DATABASE 訂貨管理五、嵌套觸發(fā)器和遞歸觸發(fā)器 P199, P200六、管理觸發(fā)器 P2011查看2修改3禁用4刪除-根據(jù)成績(jī)表計(jì)算每個(gè)學(xué)生的已修學(xué)分update 學(xué)生set 已修學(xué)分=(select sum(學(xué)分) from 課程where 課程編號(hào)in(select 課程編號(hào)from 成績(jī)where 學(xué)生

9、.學(xué)號(hào)=成績(jī).學(xué)號(hào) and 分?jǐn)?shù)>=60)-修改插入觸發(fā)器，使得向成績(jī)表中插入多條記錄，也能成功執(zhí)行CREATE TRIGGER 插入學(xué)生學(xué)分ON 成績(jī)FOR insertASUPDATE 學(xué)生SET 已修學(xué)分=已修學(xué)分+(SELECT SUM(學(xué)分) FROM 課程WHERE 課程編號(hào)IN(SELECT DISTINCT 課程編號(hào)FROM insertedWHERE 學(xué)生.學(xué)號(hào)=inserted.學(xué)號(hào)AND 分?jǐn)?shù)>=60) WHERE EXISTS(SELECT DISTINCT 課程編號(hào)FROM insertedWHERE 學(xué)生.學(xué)號(hào)=inserted.學(xué)號(hào)AND 分?jǐn)?shù)>

10、=60)select * from 成績(jī)select 學(xué)號(hào),已修學(xué)分from 學(xué)生INSERT INTO 成績(jī)VALUES('200202','L2020308',83)INSERT INTO 成績(jī)VALUES('200202','L2030501',80)INSERT INTO 成績(jī)VALUES('200202','L2030506',75)INSERT INTO 成績(jī)VALUES('200201','L2020308',83)INSERT INTO 成績(jī)VALUES('200201','L2030501',80)INSERT INTO 成績(jī)VALUES('200201','L2