使用SQL語句創建觸發器的實例

目錄前言一、觸發器的介紹1.1 觸發器 的概念以及定義:1.2 、 觸發器 的作用二、 觸發器 的種類三、使用SQL語句創建觸發器實例1.創建after融發器2.創建instead of觸發器

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

觸發器 是一種特殊類型的存儲過程，它不同于我們前面介紹過的存儲過程。存儲過程可以通過語句直接調用,而 觸發器主要是通過事件進行觸發而被執行的.例如當對某一表進行諸如UPDATE(修改)、INSERT(插入)、DELETE(刪除)這些操作時，SQL Server 就會自動執行觸發器所定義的SQL語句，從而確保對數據之間的相互關系,實時更新.

1.2 、 觸發器 的作用

觸發器的主要作用就是其能夠實現由 主鍵 和 外鍵 所不能保證的復雜的參照完整性和數據的一致性。除此之外， 觸發器 還有其它許多不同的功能：

①、復雜的約束條件觸發器 能夠實現比CHECK 語句更為復雜的約束。

②、保證數據的安全

觸發器 因為 觸發器是在對數據庫進行相應的操作而自動被觸發的SQL語句可以通過數據庫內的操作從而不允許數據庫中未經許可的指定更新和變化。

③．級聯式

觸發器 可以根據數據庫內的操作，并自動地級聯影響整個數據庫的各項內容。例如：對A表進行操作時,導致A表上的 觸發器被觸發,A中的 觸發器中包含有對B表的數據操作（UPDATE(修改)、INSERT(插入)、DELETE(刪除)），而該操作又導致B表上 觸發器被觸發。

④．調用存儲過程

為了響應數據庫更新， 觸發器 可以調用一個或多個存儲過程.

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

二、 觸發器 的種類

SQL Server 中一般支持以下兩種類型的觸發器:

AFTER 觸發器

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

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

三、使用SQL語句創建觸發器實例1.創建after融發器

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

語句實現:

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

執行:

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

刪除外鍵約束;

alter table scdrop constraint FK_sc__sno__332C9D34

(2）為Course表創建一個觸發器Course_del，當刪除了Course表中的一條課程信息時，同時將表sc表中相應的學生選課記錄刪除掉。

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表中添加一個平均成績avg_Grade字段（記錄每門課程的平均成績)，創建一個觸發器Grade_modify，當sc表中的某學生的成績發生變化時，則Course表中的平均成績也能及時相應的發生改變。

向Course表中添加一個平均成績avg_Grade字段

alter table Courseadd avg_Grade smallint

向avg_Grade中添加數據

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

查看Course表:

select *from Course

創建Grade_modify觸發器

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.創建instead of觸發器

(1）創建一視圖Student_view,包含學號、姓名、課程號、課程名、成績等屬性，在Student_view上創建一個觸發器Grade_moidfy,當對Student_view中的學生的成績進行修改時，實際修改的是sc中的相應記錄。

創建視圖:

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

創建觸發器:

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'

測試修改數據:

select *from student_view

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

添加新字段getcredit :

alter table scadd getcredit smallint

創建觸發器:

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修改數據:update scset Grade='90'where Sno='20050001' and cno='001'

以上就是對觸發器的基本認識了,希望對大家有所幫助.

到此這篇關于使用SQL語句創建觸發器的實例的文章就介紹到這了,更多相關SQL創建觸發器內容請搜索好吧啦網以前的文章或繼續瀏覽下面的相關文章希望大家以后多多支持好吧啦網！