前言

一、觸發(fā)器的介紹

1.1 觸發(fā)器 的概念以及定義:

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

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

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

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

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

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

③．級聯(lián)式

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

④．調(diào)用存儲過程

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

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

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

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

AFTER 觸發(fā)器

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

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

三、使用SQL語句創(chuàng)建觸發(fā)器實(shí)例

1.創(chuàng)建after融發(fā)器

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

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

create trigger sc_insert
on sc
after insert
as
if not exists (select * from student,inserted
			where student.sno=inserted.sno)
	begin
		print '插入信息的學(xué)號不在學(xué)生表中! '
		if not exists (select * from course,inserted where
		course.cno=inserted. cno)
		print '插入信息的課程號不在課程表中!'
		rollback
	end
  else
		begin
			if not exists (select * from course,inserted where
		Course.cno=inserted.cno)
			begin
			print '插入信息的課程號不在課程表中! '
			rollback
		end
	end

執(zhí)行:

insert into sc
values ( '20110112','001','78')

刪除外鍵約束;

alter table sc
drop constraint FK_sc__sno__332C9D34

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

create trigger course_del
on course
after delete
as
if exists(select * from sc, deleted
where sc.cno=deleted.cno)
begin
delete from sc
where sc.cno in (select cno from deleted)
end
delete from Course
where Cno='003'

select * from sc

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

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

alter table Course
add avg_Grade smallint

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

update course
set avg_Grade= (select AVG(Grade) from sc
where sc.Cno=Course.Cno)

查看Course表:

select *from Course

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

create trigger Grade_modify
on sc
after update
as
if update(grade)
begin
update course
set avg_grade=(select avg (grade)
from sc where course.cno=sc.cno
group by cno)
end
update sc
set Grade='90 '
where sno='20050001' and cno='001'

2.創(chuàng)建instead of觸發(fā)器

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

創(chuàng)建視圖:

create view student_view
as
select s.Sno,Sname , c.Cno , Cname , Grade
from student s , course c, sc
where s.Sno=sc.sno and c.Cno=sc.cno

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

create trigger Grade_moidfy 
on student_view
instead of update
as
if UPDATE (Grade)
begin
update sc
set Grade= (select Grade from inserted) where
Sno= (select sno from inserted) and 
Cno= (select Cno from inserted)
End
update student_viewset Grade=40
where Sno='20110001'and Cno='002'

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

select *
from student_view

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

添加新字段getcredit :

alter table sc
add getcredit smallint

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

create trigger sc_up
on sc
after insert,update
as
declare @xf int,@kch char(3),@xh char(8),@fs int
select @fs=grade,@kch=cno,@xh=sno from inserted
if @fs>=60
update sc set @xf=(select credit from course where
sc.Cno=course.cno) where sno=@xh and cno=@kch
else
update sc set @xf=0 where sno=@xh and cno=@kch
修改數(shù)據(jù):
update sc
set Grade='90'
where Sno='20050001' and cno='001'

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

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

最新評論