接下來(lái)我們從代碼中認(rèn)識(shí)下幾種觸發(fā)器。
--#Update型觸發(fā)器
If exits(select name from sysobjects where name=’tgr_update’)
Drop trigger tgr_update
Go
Create trigger tgr_update on student
for update
As
If (Update(student_ID))
Print ‘更改成功！’
Else
Begin
Raiserror(‘系統(tǒng)提示：更新發(fā)生錯(cuò)誤’,16,1)
Rollback tran
End
Go
--測(cè)試
Update student set student_ID=10002 where student_ID=10001 |
注意：在創(chuàng)建觸發(fā)器時(shí)，創(chuàng)建觸發(fā)器必須是批處理的第一行，存儲(chǔ)過(guò)程也是如此。
--# instead of 觸發(fā)器
if (object_id('tgr_classes_inteadOf', 'TR') is not null)
drop trigger tgr_classes_inteadOf
go
create trigger tgr_classes_intead Of
on classes
instead of delete/*, update, insert*/
as
declare @id int, @name varchar(20);
--查詢被刪除的信息，病賦值
select @id = id, @name = name from deleted;
print 'id: ' + convert(varchar, @id) + ', name: ' + @name;
--先刪除student的信息
delete student where cid = @id;
--再刪除classes的信息
delete classes where id = @id;
print '刪除[ id: ' + convert(varchar, @id) + ', name: ' + @name + ' ] 的信息成功！';
go
--test
select * from student order by id;
select * from classes;
delete classes where id = 7;
|
# 啟用、禁用觸發(fā)器
--禁用觸發(fā)器
disable trigger tgr_message on student;
--啟用觸發(fā)器
enable trigger tgr_message on student;
<P style="BACKGROUND: white"><SPAN style="COLOR: #4b4b4b"> # </SPAN><SPAN style="COLOR: #4b4b4b">顯示自定義消息</SPAN><SPAN style="COLOR: #4b4b4b">raiserror</SPAN></P> |
if (object_id('tgr_message', 'TR') is not null)
drop trigger tgr_message
go
create trigger tgr_message
on student
after insert, update
as raisError('tgr_message觸發(fā)器被觸發(fā)', 16, 10);
go
--test
insert into student values('lily', 22, 1, 7);
update student set sex = 0 where name = 'lucy';
select * from student order by id;
<SPAN style="BACKGROUND-COLOR: #ffffff; FONT-FAMILY: Arial"></SPAN> |
二、業(yè)務(wù)規(guī)則
業(yè)務(wù)規(guī)則聽(tīng)起來(lái)很難理解，當(dāng)然它也是值得我們深究東西，通俗的講它其實(shí)是符合實(shí)際條件。如：某商店規(guī)定一個(gè)售貨員在一個(gè)月內(nèi)售出10個(gè)以上的熱浴盆，那么獎(jiǎng)勵(lì)2000元；某公司的訂單上必須含有客戶的姓名和聯(lián)系方式等等，這些都是簡(jiǎn)單的業(yè)務(wù)規(guī)則。從數(shù)據(jù)庫(kù)的角度看，業(yè)務(wù)規(guī)則就是約束。
約束和觸發(fā)器
MS SQL Server提供了兩種主要的機(jī)制進(jìn)行強(qiáng)制業(yè)務(wù)規(guī)則和數(shù)據(jù)的完整性：約束和觸發(fā)器。在作用上約束支持的觸發(fā)器都可以實(shí)現(xiàn)，它們兩者是相容的關(guān)系，如下圖。雖然兩者在作用關(guān)系上有重合的地方，但是相較兩者的執(zhí)行效率和維護(hù)難易來(lái)說(shuō)，觸發(fā)器是遠(yuǎn)遠(yuǎn)不如約束的。所以約束能實(shí)現(xiàn)的情況下編程人員是不會(huì)選擇觸發(fā)器的。
一、約束，上篇博客我已經(jīng)著重講解了約束的概念，這里不再深究。
SQL Server中存在五種約束：
● 約束的目的：確保表中數(shù)據(jù)的完整型
● 常用的約束類(lèi)型：
– 主鍵約束（Primary Key Constraint）：要求主鍵列數(shù)據(jù)唯一，并且不允許為空
– 唯一約束（Unique Constraint）：要求該列唯一，允許為空，但只能出現(xiàn)一個(gè)空值。
– 檢查約束（Check Constraint）：某列取值范圍限制、格式限制等，如有關(guān)年齡的約束
– 默認(rèn)約束（Default Constraint）：某列的默認(rèn)值，如我們的男性學(xué)員較多，性別默認(rèn)為“男”
– 外鍵約束（Foreign Key Constraint）：用于兩表間建立關(guān)系，需要指定引用主表的那列
二、觸發(fā)器，首先在下表中來(lái)看觸發(fā)器的基本結(jié)構(gòu)。
觸發(fā)器是一種對(duì)表進(jìn)行插入、刪除、更改的時(shí)候自動(dòng)運(yùn)行的特殊的存儲(chǔ)過(guò)程。它一般用在比核查約束更為復(fù)雜的約束中。但能用約束實(shí)現(xiàn)的功能，一般不用觸發(fā)器。
觸發(fā)器的應(yīng)用種類(lèi)繁多上面的幾個(gè)示例都是比較常用的，當(dāng)然最好的熟練方法就是多用，多練。
觸發(fā)器和存儲(chǔ)過(guò)程
觸發(fā)器是一種特殊的存儲(chǔ)過(guò)程，不是由用戶直接調(diào)用。而存儲(chǔ)過(guò)程是一組T-SQL語(yǔ)句，經(jīng)過(guò)編譯后可以被多次調(diào)用。類(lèi)似于其它編程語(yǔ)言中的過(guò)程。它可以接收輸入?yún)?shù)、輸出參數(shù)、返回單個(gè)或多個(gè)結(jié)果集以及返回值。
存儲(chǔ)過(guò)程分為三類(lèi)：
1、系統(tǒng)存儲(chǔ)過(guò)程：以sp_開(kāi)頭，用來(lái)進(jìn)行系統(tǒng)的各項(xiàng)設(shè)定、取得信息。相關(guān)管理工作，如 sp_help就是取得指定對(duì)象的相關(guān)信息
2、擴(kuò)展存儲(chǔ)過(guò)程 以XP_開(kāi)頭，用來(lái)調(diào)用操作系統(tǒng)提供的功能
exec master..xp_cmdshell 'ping 10.8.16.1'
3、用戶自定義的存儲(chǔ)過(guò)程，這是我們所指的存儲(chǔ)過(guò)程
常用格式
Create PRocedure procedue_name
[@parameter data_type][output]
[with]{recompile|encryption}
as
sql_statement
--解釋:
--output：表示此參數(shù)是可傳回的
--with {recompile|encryption}
--recompile:表示每次執(zhí)行此存儲(chǔ)過(guò)程時(shí)都重新編譯一次
--encryption:所創(chuàng)建的存儲(chǔ)過(guò)程的內(nèi)容會(huì)被加密 |
舉例：
有如下表量表
result_Info：
Student_Info
#創(chuàng)建返回參數(shù)的存儲(chǔ)過(guò)程
If exists(select name from sysobjects where name=’proc_return’ and type=’P’)
Drop proc proc_return
Go
Create proc proc_return
@param1 int,
@param2 char(10),
@param3 char(10)
@param4 int output
With encryption --加密
As
Insert into student_Info(student_ID,name,result) values(@param1,@param2,@param3)
Select @param4=sum(result) from student_Info
Print ‘總分為：’ & convert(char,@param)
Go
--調(diào)用測(cè)試
Declare @sumresult int
Exec proc_return 12,’王剛’,80,@sumresult
Go |
存儲(chǔ)過(guò)程的3種傳回值：
1、以Return傳回整數(shù)
2、以output格式傳回參數(shù)
3、Recordset
傳回值的區(qū)別：
output和return都可在批次程式中用變量接收，而recordset則傳回到執(zhí)行批次的客戶端中
#創(chuàng)建一個(gè)存儲(chǔ)過(guò)程，實(shí)現(xiàn)將表一和表二合并，該表只含Student_ID、Name、sex、result，將臨時(shí)表存放在存儲(chǔ)過(guò)程中。
If exists(select name from sysobjects where name=’proc_save’ and type=’P’)
Drop proc proc_return
Go
Create proc proc_save
As
Select r.student_ID,r.Name,r.result,s.sex into #temptable from result r inner join student s on r.student_ID=s.student_ID
If @@error=0
Print ‘Successed’
Else
Print ‘Failed’
Go |
存儲(chǔ)過(guò)程的應(yīng)用類(lèi)型還有很多，這里我只介紹了在編程時(shí)常用的兩種。
總結(jié)
在進(jìn)行數(shù)據(jù)庫(kù)程序設(shè)計(jì)時(shí)，數(shù)據(jù)的完整性是編程人員必須要考慮的，但是有時(shí)候這些知識(shí)的細(xì)節(jié)卻讓我們糾結(jié)的很，搞不清改用哪個(gè)。總之吧：能用存儲(chǔ)過(guò)程實(shí)現(xiàn)的不用觸發(fā)器；能用約束實(shí)現(xiàn)的不用觸發(fā)器，約束和存儲(chǔ)過(guò)程用哪個(gè)都可以。
有些不懂得地方在SQL Server中按F1，在SQL Server聯(lián)機(jī)叢書(shū)的索引中查找可以解決我們的一切矛盾。