目錄前言一、觸發(fā)器的介紹1.1 觸發(fā)器 的概念以及定義:1.2 、 觸發(fā)器 的作用二、 觸發(fā)器 的種類(lèi)三、使用SQL語(yǔ)句創(chuàng)建觸發(fā)器實(shí)例1.創(chuàng)建after融發(fā)器2.創(chuàng)建instead of觸發(fā)器

前言一、觸發(fā)器的介紹1.1 觸發(fā)器 的概念以及定義:

觸發(fā)器 是一種特殊類(lèi)型的存儲(chǔ)過(guò)程，它不同于我們前面介紹過(guò)的存儲(chǔ)過(guò)程。存儲(chǔ)過(guò)程可以通過(guò)語(yǔ)句直接調(diào)用,而 觸發(fā)器主要是通過(guò)事件進(jìn)行觸發(fā)而被執(zhí)行的.例如當(dāng)對(duì)某一表進(jìn)行諸如UPDATE(修改)、INSERT(插入)、DELETE(刪除)這些操作時(shí)，SQL Server 就會(huì)自動(dòng)執(zhí)行觸發(fā)器所定義的SQL語(yǔ)句，從而確保對(duì)數(shù)據(jù)之間的相互關(guān)系,實(shí)時(shí)更新.

1.2 、 觸發(fā)器 的作用

觸發(fā)器的主要作用就是其能夠?qū)崿F(xiàn)由 主鍵 和 外鍵 所不能保證的復(fù)雜的參照完整性和數(shù)據(jù)的一致性。除此之外， 觸發(fā)器 還有其它許多不同的功能：

①、復(fù)雜的約束條件觸發(fā)器 能夠?qū)崿F(xiàn)比CHECK 語(yǔ)句更為復(fù)雜的約束。

②、保證數(shù)據(jù)的安全

觸發(fā)器 因?yàn)?觸發(fā)器是在對(duì)數(shù)據(jù)庫(kù)進(jìn)行相應(yīng)的操作而自動(dòng)被觸發(fā)的SQL語(yǔ)句可以通過(guò)數(shù)據(jù)庫(kù)內(nèi)的操作從而不允許數(shù)據(jù)庫(kù)中未經(jīng)許可的指定更新和變化。

③．級(jí)聯(lián)式

觸發(fā)器 可以根據(jù)數(shù)據(jù)庫(kù)內(nèi)的操作，并自動(dòng)地級(jí)聯(lián)影響整個(gè)數(shù)據(jù)庫(kù)的各項(xiàng)內(nèi)容。例如：對(duì)A表進(jìn)行操作時(shí),導(dǎo)致A表上的 觸發(fā)器被觸發(fā),A中的 觸發(fā)器中包含有對(duì)B表的數(shù)據(jù)操作（UPDATE(修改)、INSERT(插入)、DELETE(刪除)），而該操作又導(dǎo)致B表上 觸發(fā)器被觸發(fā)。

④．調(diào)用存儲(chǔ)過(guò)程

為了響應(yīng)數(shù)據(jù)庫(kù)更新， 觸發(fā)器 可以調(diào)用一個(gè)或多個(gè)存儲(chǔ)過(guò)程.

但是,總體而言， 觸發(fā)器性能通常比較低。

二、 觸發(fā)器 的種類(lèi)

SQL Server 中一般支持以下兩種類(lèi)型的觸發(fā)器:

AFTER 觸發(fā)器

  AFTER 觸發(fā)器 要求只有執(zhí)行某一操作(INSERT、UPDATE、DELETE)之后， 觸發(fā)器 才被觸發(fā)，且只能在表上定義。可以為針對(duì)表的同一操作定義多個(gè) 觸發(fā)器 。2. INSTEAD OF 觸發(fā)器 。

  INSTEAD OF 觸發(fā)器 表示并不執(zhí)行其所定義的操作(INSERT、UPDATE、DELETE)，而僅是執(zhí)行 觸發(fā)器 本身。既可在表上定義INSTEAD OF 觸發(fā)器 ，也可以在視圖上定義INSTEAD OF 觸發(fā)器 ，但對(duì)同一操作只能定義一個(gè)INSTEAD OF 觸發(fā)器 。

三、使用SQL語(yǔ)句創(chuàng)建觸發(fā)器實(shí)例1.創(chuàng)建after融發(fā)器

(1）創(chuàng)建一個(gè)在插入時(shí)觸發(fā)的觸發(fā)器sc_insert,當(dāng)向sc表插入數(shù)據(jù)時(shí)，須確保插入的學(xué)號(hào)已在student表中存在，并且還須確保插入的課程號(hào)在Course表中存在﹔若不存在，則給出相應(yīng)的提示信息，并取消插入操作，提示信息要求指明插入信息是學(xué)號(hào)不滿足條件還是課程號(hào)不滿足條件（注:Student表與sc表的外鍵約束要先取消)。

語(yǔ)句實(shí)現(xiàn):

create trigger sc_inserton scafter insertasif not exists (select * from student,insertedwhere student.sno=inserted.sno)beginprint '插入信息的學(xué)號(hào)不在學(xué)生表中! 'if not exists (select * from course,inserted wherecourse.cno=inserted. cno)print '插入信息的課程號(hào)不在課程表中!'rollbackend elsebeginif not exists (select * from course,inserted whereCourse.cno=inserted.cno)beginprint '插入信息的課程號(hào)不在課程表中! 'rollbackendend

執(zhí)行:

insert into scvalues ( '20110112','001','78')

刪除外鍵約束;

alter table scdrop constraint FK_sc__sno__332C9D34

(2）為Course表創(chuàng)建一個(gè)觸發(fā)器Course_del，當(dāng)刪除了Course表中的一條課程信息時(shí)，同時(shí)將表sc表中相應(yīng)的學(xué)生選課記錄刪除掉。

create trigger course_delon courseafter deleteasif exists(select * from sc, deletedwhere sc.cno=deleted.cno)begindelete from scwhere sc.cno in (select cno from deleted)enddelete from Coursewhere Cno='003'

select * from sc

(3）在Course表中添加一個(gè)平均成績(jī)avg_Grade字段（記錄每門(mén)課程的平均成績(jī))，創(chuàng)建一個(gè)觸發(fā)器Grade_modify，當(dāng)sc表中的某學(xué)生的成績(jī)發(fā)生變化時(shí)，則Course表中的平均成績(jī)也能及時(shí)相應(yīng)的發(fā)生改變。

向Course表中添加一個(gè)平均成績(jī)avg_Grade字段

alter table Courseadd avg_Grade smallint

向avg_Grade中添加數(shù)據(jù)

update courseset avg_Grade= (select AVG(Grade) from scwhere sc.Cno=Course.Cno)

查看Course表:

select *from Course

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

create trigger Grade_modifyon scafter updateasif update(grade)beginupdate courseset avg_grade=(select avg (grade)from sc where course.cno=sc.cnogroup by cno)endupdate scset Grade='90 'where sno='20050001' and cno='001'2.創(chuàng)建instead of觸發(fā)器

(1）創(chuàng)建一視圖Student_view,包含學(xué)號(hào)、姓名、課程號(hào)、課程名、成績(jī)等屬性，在Student_view上創(chuàng)建一個(gè)觸發(fā)器Grade_moidfy,當(dāng)對(duì)Student_view中的學(xué)生的成績(jī)進(jìn)行修改時(shí)，實(shí)際修改的是sc中的相應(yīng)記錄。

創(chuàng)建視圖:

create view student_viewasselect s.Sno,Sname , c.Cno , Cname , Gradefrom student s , course c, scwhere s.Sno=sc.sno and c.Cno=sc.cno

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

create trigger Grade_moidfy on student_viewinstead of updateasif UPDATE (Grade)beginupdate scset Grade= (select Grade from inserted) whereSno= (select sno from inserted) and Cno= (select Cno from inserted)Endupdate student_viewset Grade=40where Sno='20110001'and Cno='002'

測(cè)試修改數(shù)據(jù):

select *from student_view

(2）在sc表中插入一個(gè)getcredit字段(記錄某學(xué)生，所選課程所獲學(xué)分的情況)，創(chuàng)建一個(gè)觸發(fā)器ins_credit，當(dāng)更改（注:含插入時(shí)）sc表中的學(xué)生成績(jī)時(shí)，如果新成績(jī)大于等于60分，則該生可獲得這門(mén)課的學(xué)分，且該學(xué)分須與Course表中的值一致﹔如果新成績(jī)小于60分，則該生未能獲得學(xué)分，修改值為0。

添加新字段getcredit :

alter table scadd getcredit smallint

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

create trigger sc_upon scafter insert,updateasdeclare @xf int,@kch char(3),@xh char(8),@fs intselect @fs=grade,@kch=cno,@xh=sno from insertedif @fs>=60update sc set @xf=(select credit from course wheresc.Cno=course.cno) where sno=@xh and cno=@kchelseupdate sc set @xf=0 where sno=@xh and cno=@kch修改數(shù)據(jù):update scset Grade='90'where Sno='20050001' and cno='001'

以上就是對(duì)觸發(fā)器的基本認(rèn)識(shí)了,希望對(duì)大家有所幫助.

到此這篇關(guān)于使用SQL語(yǔ)句創(chuàng)建觸發(fā)器的實(shí)例的文章就介紹到這了,更多相關(guān)SQL創(chuàng)建觸發(fā)器內(nèi)容請(qǐng)搜索好吧啦網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持好吧啦網(wǎng)！