觸發(fā)器的創(chuàng)建與使用.ppt

1、數(shù)據(jù)庫基礎(chǔ)與應(yīng)用第10章 觸發(fā)器的創(chuàng)建和使用,2020/12/7,相關(guān)知識回顧,1、什么是存儲過程？ 2、創(chuàng)建存儲過程的基本語法如何？ 3、如何調(diào)用存儲過程？ 4、數(shù)據(jù)庫中有哪6種約束？,2020/12/7,觸發(fā)器與存儲過程、約束的關(guān)系,觸發(fā)器是建立在表上的特殊的存儲過程。 當(dāng)對表進(jìn)行插入、刪除、更新操作時，觸發(fā)器將會自動執(zhí)行。 利用觸發(fā)器可以保證表中數(shù)據(jù)的一致性和完整性，功能比約束更強。,2020/12/7,本章學(xué)習(xí)目標(biāo),了解觸發(fā)器的概念和優(yōu)點 理解觸發(fā)器的工作原理 熟練掌握如何創(chuàng)建INSERT觸發(fā)器、UPDATE觸發(fā)器、DELETE觸發(fā)器 掌握如何查看、修改和刪除觸發(fā)器,2020/12/7

2、,第10章 觸發(fā)器的創(chuàng)建和使用,10.1 概述 10.2 創(chuàng)建觸發(fā)器 10.3 查看、修改和刪除觸發(fā)器 10.4 綜合實例 10.5 上機實訓(xùn) 本章小結(jié),2020/12/7,10.1 概述,觸發(fā)器是一種特殊類型的存儲過程，它是與特定的表相關(guān)聯(lián)的。 當(dāng)使用UPDATE、INSERT或DELETE中的一種或多種操作在指定表中對數(shù)據(jù)進(jìn)行修改時，觸發(fā)器會生效并自動執(zhí)行。 一個表可以有多個觸發(fā)器。,2020/12/7,觸發(fā)器的主要功能：,使用觸發(fā)器有助于強制保持?jǐn)?shù)據(jù)庫的數(shù)據(jù)完整性。例如，在觸發(fā)器中可以完成如下功能： 不允許刪除或更新特定的記錄。 不允許插入不符合邏輯關(guān)系的記錄。 在刪除一條記錄的同時刪除

3、其他表中與其相關(guān)的記錄。 在修改一條記錄的同時修改其他表中與其相關(guān)的記錄。,2020/12/7,觸發(fā)器的主要優(yōu)點：,觸發(fā)器是自動執(zhí)行的，不需要管理員手動維護數(shù)據(jù)庫的數(shù)據(jù)完整性。 觸發(fā)器可以對數(shù)據(jù)庫中的相關(guān)表進(jìn)行級聯(lián)更改。例如，可以在 “院系”表中定義觸發(fā)器，當(dāng)用戶刪除表“院系”中的記錄時，觸發(fā)器將刪除 “學(xué)生”表和“教師”表中對應(yīng)院系的記錄。 觸發(fā)器可以限制向表中插入無效的數(shù)據(jù)，與CHECK約束的功能相似。但在CHECK約束中不能使用到其他表中的字段，而在觸發(fā)器中則沒有此限制。,2020/12/7,思考？,觸發(fā)器如何保證數(shù)據(jù)的完整性和一致性？ 觸發(fā)器有哪些類型？ 如何創(chuàng)建觸發(fā)器？ 觸發(fā)器是怎么

4、自動執(zhí)行的？,2020/12/7,示例數(shù)據(jù)庫Student的結(jié)構(gòu),學(xué)生表S(Snum,Sname,Ssex,Sage,Sphone,Dnum) 教師表T(Tnum,Tname,Tsex,Tbirth,Ttitle,Tsalary,Tphone,Dnum) 院系表D(Dnum,Dname,Ddirector) 課程表C(Cnum,Cname,Cfreq) 選課表SC(Snum,Cnum,Score) 授課表TC(Tnum,Cnum) 學(xué)生統(tǒng)計表N（Dnum,Num,Man,Woman） 課程平均成績CPJ(Cnum,Cname,Pjcj),2020/12/7,項目：學(xué)籍管理系統(tǒng),任務(wù)1：新生入學(xué)

5、時，如何自動調(diào)整表n中相應(yīng)系部的數(shù)據(jù)。 任務(wù)2：有學(xué)生退學(xué)時，如何自動調(diào)整表n中相應(yīng)系部的數(shù)據(jù)。 任務(wù)3：學(xué)生轉(zhuǎn)系時，如何自動調(diào)整表n中相應(yīng)班級的數(shù)據(jù)。 任務(wù)4：學(xué)生選修某門課程取得成績后，如何自動更新該課程的平均成績； 任務(wù)5：發(fā)現(xiàn)某學(xué)生登記的成績有誤，進(jìn)行更正后，如何自動調(diào)整該課程的平均成績； 任務(wù)6：某個系不存在了，要刪除D表中相應(yīng)記錄該如何進(jìn)行？,2020/12/7,理解觸發(fā)器的類型,（1）對應(yīng)數(shù)據(jù)操作的三種類型：INSERT、DELETE和UPDATE，分別有三種不同類型的觸發(fā)器，在執(zhí)行相應(yīng)的操作時自動觸發(fā)。一個表可以同時有多個觸發(fā)器，這些觸發(fā)器可以是不同類型，也可以是同一類型的。,

6、上述6個任務(wù)所需要的觸發(fā)器分別屬于哪種？,2020/12/7,（2）從執(zhí)行時間來看，SQL SERVER有兩類觸發(fā)器： AFTER觸發(fā)器： 觸發(fā)器在觸發(fā)它們的操作完成后執(zhí)行。一張表中可以為每個觸發(fā)操作(INSERT、DELETE和UPDATE)創(chuàng)建多個AFTER觸發(fā)器。 INSTEAD OF觸發(fā)器： 該觸發(fā)器代替觸發(fā)操作執(zhí)行，原來的操作不再執(zhí)行。一個表只能創(chuàng)建一個INSTEAD OF觸發(fā)器。,上述6個任務(wù)所需要的觸發(fā)器分別屬于哪種？,2020/12/7,2020/12/7,10.2 創(chuàng)建觸發(fā)器,創(chuàng)建觸發(fā)器的方法： 1使用Transact-SQL語句創(chuàng)建觸發(fā)器 2使用企業(yè)管理器創(chuàng)建觸發(fā)器,202

7、0/12/7,使用Transact-SQL語句創(chuàng)建觸發(fā)器,CREATE TRIGGER ON FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE AS 注意：默認(rèn)創(chuàng)建的觸發(fā)器是AFTER類型的。,2020/12/7,任務(wù)1 創(chuàng)建insert觸發(fā)器,例1 在表s上創(chuàng)建一個INSERT 觸發(fā)器，實現(xiàn)當(dāng)表s插入一個學(xué)生時，自動調(diào)整表n中相應(yīng)系部的統(tǒng)計數(shù)據(jù)，即實現(xiàn)表s插入數(shù)據(jù)時與表n間數(shù)據(jù)的一致性。 方法一：用SQL語句實現(xiàn) 方法二：用企業(yè)管理器實現(xiàn),2020/12/7,方法一：用SQL語句創(chuàng)建觸發(fā)器,CREATE TRIGGER s_insert ON

8、 s FOR INSERT AS begin declare dnum char(20),ssex char(2) -檢索插入的記錄值 select dnum=dnum,ssex=ssex from inserted -如果在N表中不存在該學(xué)生對應(yīng)的系，則在N表中添加該系的信息 if not exists(select dnum from n where dnum=dnum) insert into n(dnum,num) values (dnum,0) -根據(jù)該學(xué)生的性別更新相應(yīng)的統(tǒng)計信息 if ssex=男 update n set num=num+1,man=isnull(man,0)+

9、1 where dnum=dnum else update n set num=num+1,woman=isnull(woman,0)+1 where dnum=dnum end,2020/12/7,方法一：用企業(yè)管理器創(chuàng)建觸發(fā)器,2020/12/7,如何驗證觸發(fā)器是否正常工作？,課堂練習(xí)： 1、利用SQL語句在S表上創(chuàng)建觸發(fā)器（例1.txt）； 2、觀察N表中的統(tǒng)計數(shù)據(jù)并進(jìn)行記錄； 3、利用企業(yè)管理器在S表中插入一條記錄； 4、觀察N表中的統(tǒng)計數(shù)據(jù)是否自動發(fā)生變化；,2020/12/7,任務(wù)2 創(chuàng)建Delete觸發(fā)器,例2 在表s上創(chuàng)建一個DELETE 觸發(fā)器，實現(xiàn)當(dāng)表s刪除一個學(xué)生時，自動

10、調(diào)整表n中相應(yīng)系部的數(shù)據(jù)，即實現(xiàn)表s刪除數(shù)據(jù)時與表n間數(shù)據(jù)的一致性。 方法一：用SQL語句實現(xiàn) 方法二：用企業(yè)管理器實現(xiàn),2020/12/7,提示:,-檢索插入的記錄值 select dnum=dnum,ssex=ssex from inserted -檢索刪除的記錄值 select dnum=dnum,ssex=ssex from deleted,2020/12/7,觸發(fā)器的執(zhí)行原理（P214）,insert(delete)觸發(fā)器：對表插入(刪除)記錄時，觸發(fā)器被自動執(zhí)行。首先將插入(刪除)的記錄放入inserted(deleted)表中，該表為一邏輯表(結(jié)構(gòu)與原表相同)，保存插入(刪除)的

11、記錄，然后執(zhí)行觸發(fā)器指定的操作。,update：相當(dāng)于先delete，再insert。先將原記錄保存在deleted表中，再將新記錄保存在inserted表中。,2020/12/7,任務(wù)3 創(chuàng)建Update觸發(fā)器,例3 在表s上創(chuàng)建一個UPDATE觸發(fā)器，實現(xiàn)當(dāng)表s修改一個學(xué)生信息時，自動調(diào)整表n中相應(yīng)班級的數(shù)據(jù)，即實現(xiàn)表s修改數(shù)據(jù)時與表n間數(shù)據(jù)的一致性。 方法一：用SQL語句實現(xiàn) 方法二：用企業(yè)管理器實現(xiàn),2020/12/7,分析,Update操作相當(dāng)于先Delete，再Insert。 Update觸發(fā)器中定義的SQL語句也應(yīng)該由兩部分所構(gòu)成： （1）Delete觸發(fā)器中定義的語句； （2）

12、Insert觸發(fā)器中定義的語句。,課堂練習(xí)：根據(jù)例1和例2，創(chuàng)建Update觸發(fā)器,2020/12/7,課后練習(xí)：創(chuàng)建觸發(fā)器實現(xiàn)以下功能,任務(wù)4：學(xué)生選修某門課程取得成績后，如何自動更新該課程的平均成績； 任務(wù)5：發(fā)現(xiàn)某學(xué)生登記的成績有誤，進(jìn)行更正后，如何自動調(diào)整該課程的平均成績； 任務(wù)6：某個系不存在了，要刪除D表中相應(yīng)記錄該如何進(jìn)行？（instead of觸發(fā)器）,2020/12/7,10.3 查看、修改和刪除觸發(fā)器,觸發(fā)器建立后，可查看、修改和刪除觸發(fā)器的定義。,2020/12/7,10.3.1 查看觸發(fā)器,可以通過企業(yè)管理器查看觸發(fā)器，也可通過系統(tǒng)存儲過程查看觸發(fā)器。 有4個系統(tǒng)存儲過

13、程，可以顯示有關(guān)觸發(fā)器的信息： sp_help 觸發(fā)器名: 顯示觸發(fā)器的所有者和創(chuàng)建時間 sp_helptext 觸發(fā)器名：顯示觸發(fā)器的源代碼 sp_helptrigger 表名：顯示某個表定義的觸發(fā)器清單 sp_depends 觸發(fā)器名：顯示該觸發(fā)器引用的對象清單,2020/12/7,10.3.2 修改觸發(fā)器,觸發(fā)器建立后可對其進(jìn)行修改，既可修改其名稱，也可以修改其定義、有效性等。 1修改觸發(fā)器名稱 語法格式： sp_rename 觸發(fā)器原名, 觸發(fā)器新名,2020/12/7,2修改觸發(fā)器內(nèi)容 （1）使用T-SQL修改觸發(fā)器內(nèi)容，語法格式如下： 語法格式： Alter TRIGGER ON

14、FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE AS ,2020/12/7,（2）使用企業(yè)管理器修改觸發(fā)器內(nèi)容,2020/12/7,10.3.3 刪除觸發(fā)器,當(dāng)不再需要某個觸發(fā)器時，可將其刪除?？梢酝ㄟ^除去觸發(fā)器或除去觸發(fā)器表刪除觸發(fā)器。除去表時，也將除去所有與表關(guān)聯(lián)的觸發(fā)器。 （1）使用T-SQL刪除觸發(fā)器，語法格式如下： 語法格式： DROP TRIGGER 觸發(fā)器名 ,.n ,2020/12/7,（2）使用企業(yè)管理器刪除觸發(fā)器 在圖所示的“觸發(fā)器屬性”對話框的“名稱”下拉菜單中選擇要刪除的觸發(fā)器名稱（默認(rèn)是“觸發(fā)器”），然后單擊【刪除】

15、按鈕并確認(rèn)。,2020/12/7,10.4 綜合實例,數(shù)據(jù)庫管理信息系統(tǒng)中，常常需要監(jiān)控敏感數(shù)據(jù)的更改（如用戶的計費信息、員工的工資信息等），可使用觸發(fā)器實現(xiàn)詳細(xì)、細(xì)致的日志記錄。,2020/12/7,【例10-9】用戶信息表Users中包含敏感的用戶積分和用戶等級信息，現(xiàn)在需要對其進(jìn)行監(jiān)控，如果更改了用戶積分和用戶等級，則需要生成審計記錄并插入到審核表，記錄更新前后的數(shù)據(jù)，并記錄修改者的登錄名和客戶機名，以及修改時間。 【分析】： （1）產(chǎn)生跟蹤敏感信息的審核表 （2）用戶信息表Users上創(chuàng)建UPDATE觸發(fā)器 （3）判斷是否修改用戶積分和用戶等級，是則執(zhí)行觸發(fā)器內(nèi)容；否則不做任何處理。

16、（4）從deleted臨時表中獲取修改前的數(shù)據(jù)，插入審核表。 （5）從inserted臨時表中獲取修改后的數(shù)據(jù)，插入審核表。 （6）使用 SUSER_SNAME()返回登錄名。 （7）使用 HOST_NAME（）返回工作站名稱。 （8）使用 GETDATE() 返回修改時間。,2020/12/7,代碼如下： （1）產(chǎn)生跟蹤敏感信息的審核表 CREATE TABLE auditUsers( audit_Id int identity(1,1) primary key, -審計編號，標(biāo)識列 -審計數(shù)據(jù)類型：OLD為修改前數(shù)據(jù)，NEW為修改后數(shù)據(jù) Auit_Type char(3)not null,

17、 Uidint not null, -記錄修改的用戶號 Upoint int null, -記錄用戶積分 UState int null, -記錄用戶等級 audit_user varchar(40) default SUSER_SNAME(),-記錄修改者的登錄號 audit_pc varchar(40) default HOST_NAME(), -記錄修改者的客戶機名 audit_time datetime default GETDATE() -記錄修改發(fā)生時間 ),2020/12/7,（2）在用戶信息表Users上創(chuàng)建UPDATE觸發(fā)器 CREATE TRIGGER users_Upda

18、te ON USERS FOR UPDATE as if update(Upoint) or update(ustate) begin insert into auditUsers(Auit_Type, Uid, Upoint, UState) SELECT OLD, del. Uid, del.Upoint, del.Ustate FROM DELETED del insert into auditUsers(Auit_Type, Uid, Upoint, UState) SELECT NEW, ins. Uid, ins.Upoint, ins.Ustate FROM INSERTED ins End GO,2020/12/7,（3）驗證此觸發(fā)器，修改第2號用戶的積分為20。 update Users set Upoint=20 where UID=2 （4）查看審計表