數據庫觸發(fā)器課件

1、觸發(fā)器觸發(fā)器觸發(fā)器觸發(fā)器概述問題的引入觸發(fā)器的概念觸發(fā)器的原理觸發(fā)器的分類觸發(fā)器的創(chuàng)建、修改及刪除應用示例觸發(fā)器觸發(fā)器概述觸發(fā)器概述問題的引入增加一個記錄刪除一個記錄(“95003”,”002”,56)95001 006 9041在學生管理數據庫中,某學生的選課門數是一個經常訪問的屬性,因此在student表中增加一個屬性sselnum (smallint）在實際應用實例展示觸發(fā)器概述問題的引入增加一個記錄刪除一個記錄(“95003”觸發(fā)器概述考慮該問題涉及兩個表之間的關系，可否用參照完整性來實現?可否用嵌套的SQL語句來實現?問題的解決：使用觸發(fā)器triggerupdate student

2、set sselnum=(select count(*) from s_c where s_c.sno=student.sno)存在的問題觸發(fā)器的主要作用就是其能夠實現由主鍵和外鍵所不能保證的復雜的參照完整性和數據的一致性 觸發(fā)器概述考慮該問題涉及兩個表之間的關系，可否用參照完整性來觸發(fā)器概述觸發(fā)器的概念觸發(fā)器是一種特殊類型的存儲過程。在SQL Server里面也就是對某一個表的一定的操作，觸發(fā)某種條件，從而執(zhí)行的一段程序。 觸發(fā)器觸發(fā)原因對表進行插入、更新或刪除操作觸發(fā)事件。 觸發(fā)結果：另一段程序的執(zhí)行。 思考 ：在上例中，觸發(fā)原因是什么？觸發(fā)結果是什么？觸發(fā)器概述觸發(fā)器的概念 觸發(fā)器觸發(fā)原

3、因對表進行插入、更新或刪觸發(fā)器概述 觸發(fā)器和存儲過程的區(qū)別：INSERT INTO s_c VALUES(95001,006,90) EXEC Getgrade 95001 觸發(fā)器可以用于 SQL Server 約束、默認值和規(guī)則的完整性檢查。 觸發(fā)器主要是通過事件觸發(fā)而執(zhí)行的，而存儲過程是通過調用存儲過程名稱而執(zhí)行的。觸發(fā)器概述 觸發(fā)器和存儲過程的區(qū)別：INSERT INTO 觸發(fā)器概述特殊性在于：l它與表緊密相連，不可以脫離表單獨存在。 l不允許使用參數，沒有返回值。l不允許用戶調用，當對表進行插入、刪除、修改操作時由系統(tǒng)自動調用并執(zhí)行（相當于事件方法）。 觸發(fā)器概述特殊性在于：觸發(fā)器概述

4、觸發(fā)器可以實現比較復雜的完整性約束：l 擴展約束、默認值和規(guī)則對象的完整性檢查。l 自動生成數據。l 檢查數據的修改，防止對數據不正確的修改，保證數據表之間數據的正確性和一致性。l 自定義復雜的安全權限。 觸發(fā)器作為一種數據庫對象，在syscomment系統(tǒng)表中存儲其完整的定義信息，在sysobject系統(tǒng)表中有該對象的記錄。觸發(fā)器概述觸發(fā)器可以實現比較復雜的完整性約束： 觸發(fā)器觸發(fā)器概述1）觸發(fā)器自動執(zhí)行 在對表的數據作了任何修改（比如手工輸入或者應用程序采取的操作）之后立即被激活。3）觸發(fā)器可以實現比CHECK約束更為復雜的數據完整性約束 在數據庫中為了實現數據完整性約束，可以使用CHEC

5、K約束或觸發(fā)器。CHECK約束不允許引用其它表中的列來完成檢查工作，而觸發(fā)器可以引用其它表中的列。 2）觸發(fā)器能夠對數據庫中的相關表實現級聯(lián)更改 觸發(fā)器是基于一個表創(chuàng)建的，但是可以針對多個表進行操作，實現數據庫中相關表的級聯(lián)更改。觸發(fā)器概述1）觸發(fā)器自動執(zhí)行3）觸發(fā)器可以實現比CHECK約觸發(fā)器概述AFTER類型觸發(fā)器:只有執(zhí)行某一操作（INSERT UPDATE DELETE） 之后，觸發(fā)器才被觸發(fā)。只能在表上定義。觸發(fā)器的分類INSTEAD OF 觸發(fā)器:并不執(zhí)行其所定義的操作（INSERT、 UPDATE、 DELETE），而僅是執(zhí)行觸發(fā)器本身。既可在表上定義INSTEAD OF 觸發(fā)器

6、，也可以在視圖上定義INSTEAD OF 觸發(fā)器。觸發(fā)器概述AFTER類型觸發(fā)器:觸發(fā)器的分類INSTEAD 觸發(fā)器概述觸發(fā)器的原理Inserted表和Deleted表的理解： Inserted和Deleted是邏輯（概念）表。當在定義了觸發(fā)器的表上發(fā)生了修改操作時會自動派生出這兩個視圖。 當在表上發(fā)生插入操作時，新插入的行將出現在插入視圖中。 當在表上發(fā)生刪除操作時，被刪除的行將出現在刪除視圖中。 當在表上發(fā)生更新操作時，舊行出現在刪除視圖中，新行出現在插入視圖中。觸發(fā)器概述觸發(fā)器的原理 Inserted和Delete觸發(fā)器概述觸發(fā)器的原理Inserted表和Deleted表的理解： （1）

7、如果基于S_C表的UPDATE操作定義了觸發(fā)器 （2）再執(zhí)行語句： UPDATE S_C SET grade=70 WHERE sno=95001 AND cno=001 在UPDATE語句執(zhí)行過程中 Deleted表的內容： Inserted表的內容：S_C表觸發(fā)器概述觸發(fā)器的原理S_C表觸發(fā)器的創(chuàng)建觸發(fā)器的基本操作創(chuàng)建在創(chuàng)建觸發(fā)器前，必須注意以下幾點：1、CREATE TRIGGER 語句必須是批處理中的第一條語句。2、只能在當前數據庫中創(chuàng)建觸發(fā)器，名稱必須遵循標識符的命名規(guī)則。3、表的所有者具有創(chuàng)建觸發(fā)器的默認權限，不能將該權限轉給其他用戶。4、不能在臨時表或系統(tǒng)表上創(chuàng)建觸發(fā)器，但是觸發(fā)

8、器可以引用臨時表，但是不能引用系統(tǒng)表。觸發(fā)器的創(chuàng)建觸發(fā)器的基本操作創(chuàng)建觸發(fā)器的創(chuàng)建創(chuàng)建觸發(fā)器命令格式：CREATE TRIGGER trigger_name ON tabel FOR | AFTER | INSTEAD OF INSERT,UPDATE,DELETE AS IF UPDATE(column) AND|OR UPDATE (column) sql_statement 一、企業(yè)管理器二、查詢分析器觸發(fā)器的創(chuàng)建創(chuàng)建觸發(fā)器命令格式：一、企業(yè)管理器觸發(fā)器的創(chuàng)建說明：FOR | AFTER|INSTEAD OF：FOR | AFTER：在數據變動以后觸發(fā)；INSTEAD OF: 在數據變動

9、以前觸發(fā)；INSERT|UPDATE|DELETE: 分別對應插入觸發(fā)器、修改觸發(fā)器、刪除觸發(fā)器觸發(fā)器的創(chuàng)建說明：FOR | AFTER|INSTEAD 觸發(fā)器的創(chuàng)建例：當在倉庫表上發(fā)生插入操作時會輸出“插入了一個倉庫元組”的信息。USE 倉庫管理GOCREATE TRIGGER WH_INON 倉庫 FOR INSERT AS PRINT 插入了一個倉庫元組INSERT 倉庫 VALUES(WH6,南京,700)觸發(fā)器的創(chuàng)建例：當在倉庫表上發(fā)生插入操作時會輸出“插入了一個觸發(fā)器的創(chuàng)建例：在倉庫表上定義一個刪除觸發(fā)器，使得當刪除倉庫記錄時，同時將所屬所有職工記錄的倉庫號字段值置為空值NULL。

10、CREATE TRIGGER WH_DELON 倉庫 FOR DELETEAS UPDATE 職工SET 倉庫號=NULLWHERE 倉庫號 IN (SELECT 倉庫號 FROM deleted)DELETE 倉庫 WHERE 倉庫號=WH6倉庫（倉庫號，城市，面積）職工（倉庫號，職工號，工資）觸發(fā)器的創(chuàng)建例：在倉庫表上定義一個刪除觸發(fā)器，使得當刪除倉庫觸發(fā)器的創(chuàng)建例：對S表定義一個刪除觸發(fā)器，使得當刪除學生記錄時，將S_C表中相應的選課記錄刪除。S（SNO,SNAME）S_C（SNO,CNO,SCORE）觸發(fā)器的創(chuàng)建例：對S表定義一個刪除觸發(fā)器，使得當刪除學生記錄觸發(fā)器的創(chuàng)建例：對職工表定

11、義一個插入觸發(fā)器，使得當插入職工記錄時，檢查所參照的倉庫元組是否存在，如果不存在，則撤消所做的插入操作。倉庫（倉庫號，城市，面積）職工（倉庫號，職工號，工資）觸發(fā)器的創(chuàng)建例：對職工表定義一個插入觸發(fā)器，使得當插入職工記觸發(fā)器的創(chuàng)建CREATE TRIGGER WH_INSON 職工 FOR INSERTAS IF NOT EXISTS (SELECT * FROM 倉庫 WHERE 倉庫號 = (SELECT 倉庫號 FROM INSERTED)BEGIN RAISERROR(非法倉庫號!,1,1) ROLLBACK TRANSACTIONENDINSERT 職工 VALUES(WH5,E10

12、,1600)IN觸發(fā)器的創(chuàng)建CREATE TRIGGER WH_INSINS觸發(fā)器的創(chuàng)建例：對S_C表定義一個插入觸發(fā)器，使得當插入選課記錄時，檢查所參照的學生學號和課程號是否存在，如果不存在，則撤消所做的插入操作。S（SNO,SNAME）C（CNO，CNAME）S_C（SNO,CNO,SCORE）觸發(fā)器的創(chuàng)建例：對S_C表定義一個插入觸發(fā)器，使得當插入選課觸發(fā)器的創(chuàng)建例：對職工表定義一個更新觸發(fā)器，使得當職工變換所屬倉庫時，檢查所參照的倉庫元組是否存在，如果不存在，則撤消所做的更新操作，如果新的倉庫號是WH2則將工資提高10%。觸發(fā)器的創(chuàng)建例：對職工表定義一個更新觸發(fā)器，使得當職工變換所觸發(fā)

13、器的創(chuàng)建CREATE TRIGGER TR_UPDATE ON 職工 FOR UPDATE AS DECLARE WHNO CHAR(4) IF UPDATE(倉庫號) BEGIN IF NOT EXISTS (SELECT * FROM 倉庫 WHERE 倉庫號=(SELECT 倉庫號 FROM INSERTED) BEGIN RAISERROR(非法倉庫號!,16,1) ROLLBACK TRANSACTION END ELSE BEGIN SELECT WHNO=倉庫號 FROM INSERTED IF WHNO=WH2 UPDATE 職工 SET 工資=工資*1.1 WHERE 職工號

14、=(SELECT 職工號 FROM INSERTED) END END觸發(fā)器的創(chuàng)建CREATE TRIGGER TR_UPDATE觸發(fā)器的創(chuàng)建例：當插入或更新學生成績時，觸發(fā)器檢查該課程是否為考查課，若是，則通過的成績只能以60分計，未通過的只能以40分計。涉及的表結構：C1(Cno,Cname,Ctype) S_C(Sno,Cno,Score) 觸發(fā)器的創(chuàng)建例：當插入或更新學生成績時，觸發(fā)器檢查該課程是否觸發(fā)器的創(chuàng)建CREATE TRIGGER SCORE_KCON S_C FOR INSERT,UPDATEAS DECLARE SCORE INT,CTYPE CHAR(4)SELECT S

15、CORE=SCORE,CTYPE=CTYPEFROM C1,INSERTEDWHERE INSERTED.CNO=C1.CNOIF(CTYPE=考查) AND (SCORE60 AND SCORE40)BEGIN RAISERROR(該課程為考查課，成績以60或40計!,16,1) ROLLBACK TRANSACTIONEND觸發(fā)器的創(chuàng)建CREATE TRIGGER SCORE_KC觸發(fā)器的創(chuàng)建CREATE TRIGGER SCORE_KCON S_C FOR INSERT,UPDATEAS DECLARE SCORE INT,CTYPE CHAR(4)SELECT SCORE=SCORE,

16、CTYPE=CTYPE FROM C1,INSERTEDWHERE INSERTED.CNO=C1.CNOIF(CTYPE=考查) BEGIN IF SCORE=60 UPDATE S_C SET SCORE=60 WHERE CNO=(SELECT CNO FROM INSERTED) AND SNO=(SELECT SNO FROM INSERTED) ELSE UPDATE S_C SET SCORE=40 WHERE CNO=(SELECT CNO FROM INSERTED) AND SNO=(SELECT SNO FROM INSERTED)END觸發(fā)器的創(chuàng)建CREATE TRIGGER SCORE_KC觸發(fā)器的修改修改觸發(fā)器命令格式：ALTER TRIGGER trigger_n