接下來我們從代碼中認識下幾種觸發器。
--#Update型觸發器
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(‘系統提示：更新發生錯誤’,16,1)
Rollback tran
End
Go
--測試
Update student set student_ID=10002 where student_ID=10001 |
注意：在創建觸發器時，創建觸發器必須是批處理的第一行，存儲過程也是如此。
--# instead of 觸發器
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;
|
# 啟用、禁用觸發器
--禁用觸發器
disable trigger tgr_message on student;
--啟用觸發器
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觸發器被觸發', 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> |
二、業務規則
業務規則聽起來很難理解，當然它也是值得我們深究東西，通俗的講它其實是符合實際條件。如：某商店規定一個售貨員在一個月內售出10個以上的熱浴盆，那么獎勵2000元；某公司的訂單上必須含有客戶的姓名和聯系方式等等，這些都是簡單的業務規則。從數據庫的角度看，業務規則就是約束。
約束和觸發器
MS SQL Server提供了兩種主要的機制進行強制業務規則和數據的完整性：約束和觸發器。在作用上約束支持的觸發器都可以實現，它們兩者是相容的關系，如下圖。雖然兩者在作用關系上有重合的地方，但是相較兩者的執行效率和維護難易來說，觸發器是遠遠不如約束的。所以約束能實現的情況下編程人員是不會選擇觸發器的。
一、約束，上篇博客我已經著重講解了約束的概念，這里不再深究。
SQL Server中存在五種約束：
● 約束的目的：確保表中數據的完整型
● 常用的約束類型：
– 主鍵約束（Primary Key Constraint）：要求主鍵列數據唯一，并且不允許為空
– 唯一約束（Unique Constraint）：要求該列唯一，允許為空，但只能出現一個空值。
– 檢查約束（Check Constraint）：某列取值范圍限制、格式限制等，如有關年齡的約束
– 默認約束（Default Constraint）：某列的默認值，如我們的男性學員較多，性別默認為“男”
– 外鍵約束（Foreign Key Constraint）：用于兩表間建立關系，需要指定引用主表的那列
二、觸發器，首先在下表中來看觸發器的基本結構。
觸發器是一種對表進行插入、刪除、更改的時候自動運行的特殊的存儲過程。它一般用在比核查約束更為復雜的約束中。但能用約束實現的功能，一般不用觸發器。
觸發器的應用種類繁多上面的幾個示例都是比較常用的，當然最好的熟練方法就是多用，多練。
觸發器和存儲過程
觸發器是一種特殊的存儲過程，不是由用戶直接調用。而存儲過程是一組T-SQL語句，經過編譯后可以被多次調用。類似于其它編程語言中的過程。它可以接收輸入參數、輸出參數、返回單個或多個結果集以及返回值。
存儲過程分為三類：
1、系統存儲過程：以sp_開頭，用來進行系統的各項設定、取得信息。相關管理工作，如 sp_help就是取得指定對象的相關信息
2、擴展存儲過程 以XP_開頭，用來調用操作系統提供的功能
exec master..xp_cmdshell 'ping 10.8.16.1'
3、用戶自定義的存儲過程，這是我們所指的存儲過程
常用格式
Create PRocedure procedue_name
[@parameter data_type][output]
[with]{recompile|encryption}
as
sql_statement
--解釋:
--output：表示此參數是可傳回的
--with {recompile|encryption}
--recompile:表示每次執行此存儲過程時都重新編譯一次
--encryption:所創建的存儲過程的內容會被加密 |
舉例：
有如下表量表
result_Info：
Student_Info
#創建返回參數的存儲過程
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
--調用測試
Declare @sumresult int
Exec proc_return 12,’王剛’,80,@sumresult
Go |
存儲過程的3種傳回值：
1、以Return傳回整數
2、以output格式傳回參數
3、Recordset
傳回值的區別：
output和return都可在批次程式中用變量接收，而recordset則傳回到執行批次的客戶端中
#創建一個存儲過程，實現將表一和表二合并，該表只含Student_ID、Name、sex、result，將臨時表存放在存儲過程中。
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 |
存儲過程的應用類型還有很多，這里我只介紹了在編程時常用的兩種。
總結
在進行數據庫程序設計時，數據的完整性是編程人員必須要考慮的，但是有時候這些知識的細節卻讓我們糾結的很，搞不清改用哪個。總之吧：能用存儲過程實現的不用觸發器；能用約束實現的不用觸發器，約束和存儲過程用哪個都可以。
有些不懂得地方在SQL Server中按F1，在SQL Server聯機叢書的索引中查找可以解決我們的一切矛盾。