編寫過存儲過程的人，再編寫觸發(fā)器時會發(fā)現(xiàn)：他們的語法、格式是非常類似的。其實(shí)觸發(fā)器就是一種特殊類型的存儲過程。他們都是預(yù)編譯的，在程序正式編譯前就由編譯器進(jìn)行編譯，存儲在服務(wù)器端。
不過，觸發(fā)器與一般的存儲過程也有些區(qū)別。觸發(fā)器主要是通過對數(shù)據(jù)庫的增刪改的操作，或者是一個觸發(fā)動作的觸發(fā)作用等事件觸發(fā)而被執(zhí)行；而存儲過程則是通過像傳遞SQL語句一樣，傳遞存儲過程的名字來被程序調(diào)用，實(shí)現(xiàn)功能。
觸發(fā)器一共分為五種類型：Update觸發(fā)器，Insert觸發(fā)器、Delete觸發(fā)器、Instead of觸發(fā)器和After觸發(fā)器。前三個分別是相應(yīng)表上進(jìn)行更新、插入、刪除操作時觸發(fā)。Instead of觸發(fā)器在不執(zhí)行插入、更新或刪除操作時觸發(fā)。
在觸發(fā)器中存在兩個虛擬表：Inserted表和Deleted表。Inserted表保存的是Insert或Update之后所影響的記錄形成的表，Deleted保存的是Delete或update之前所影響的記錄形成的表。這兩個表是邏輯表，這兩個表是動態(tài)駐留在內(nèi)存中的，當(dāng)觸發(fā)器工作完成，這兩個表也被刪除。
觸發(fā)器的創(chuàng)建代碼格式：
CREATE TRIGGER trigger_name --觸發(fā)器的名字
ON table|view [WITH ENCRYPTION] --在哪個表上創(chuàng)建觸發(fā)器
{ FOR | AFTER | INSTEAD OF }
{[INSERT][,][UPDATE][,][DELETE]} --激活觸發(fā)器的類型
AS sql_statements […n] |
代碼中關(guān)鍵字for、after、Insteadof分別代表不同的使用范圍：
for表示為AFTER觸發(fā)器，且該類型觸發(fā)器僅能在表上創(chuàng)建；
after表示只有在執(zhí)行了指定的操作INSERT、DELETE、UPDATE之后觸發(fā)器才被激活，執(zhí)行觸發(fā)器中的SQL語句；
instead of當(dāng)為表或視圖定義了針對某一操作INSERT、DELETE、UPDATE的INSTEAD OF 類型觸發(fā)器，且執(zhí)行了相應(yīng)的操作時，盡管觸發(fā)器被觸發(fā)，但相應(yīng)的操作并不被執(zhí)行，而運(yùn)行的僅是觸發(fā)器SQL語句本身。
下面說下觸發(fā)器的作用：
1、級聯(lián)修改數(shù)據(jù)庫中的相關(guān)的表；
看下面的牛腩新聞發(fā)布系統(tǒng)的例子：其中一個新聞類別（Category）對應(yīng)多個或者0條新聞；一條新聞（news）對應(yīng)著多個或者0個新聞評論（comment）。
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author: 劉正權(quán)
-- Create date: 2008-11-15 11：13
-- Description: 刪除新聞類別觸發(fā)器
-- =============================================
CREATE TRIGGER trigCategoryDelete
ON category
instead of DELETE
AS
BEGIN
--刪除新聞，再在類別表中刪除--觸發(fā)器實(shí)現(xiàn)
declare @caId int
select @caId=id from deleted
--刪除評論(選出多條用in,一條用=號)
delete comment where newsId in (select newsId from news wherecaId=@caId)
--刪除新聞
delete news wherecaId=@caId
--刪除類別
delete category whereid=@caId
END |
2、執(zhí)行比核查約束更為復(fù)雜的約束操作；在觸發(fā)器中可以書寫更為復(fù)雜的SQL語句，例如可以引用多個表，并使用if……else等語句做更復(fù)雜的檢查。
下面看下例子：如果更改了學(xué)生的學(xué)號，則他的借書記錄表中記錄也同時更新。
這時候，我們可以建立一下觸發(fā)器：
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 劉正權(quán)
-- Create date:2012-4-22
-- Description: 更改了學(xué)生的學(xué)號，則他的借書記錄表中記錄也同時更新
-- =============================================
Create Trigger truStudent
On Student --在Student表中創(chuàng)建觸發(fā)器
for Update --為什么事件觸發(fā)
As --事件觸發(fā)后所要做的事情
if Update(StudentID)
begin Update BorrowRecord
Set StudentID=i.StudentID
From BorrowRecord br , Deleted d ,Inserted i --Deleted和Inserted臨時表
Where br.StudentID=d.StudentID
end |
3、拒絕或回滾違反引用完整性的操作。檢查對數(shù)據(jù)庫的操作是否違反引用完整性，并選擇相應(yīng)的操作；
看下面的例子：不允許刪除任何銷售記錄大于等于20條的商店。
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: 劉正權(quán)
-- Create date: 2012-4-22
-- Description: 不允許刪除任何銷售記錄大于等于20條的商店
-- =============================================
CREATE TRIGGER trDelSales
ON tblSales
for Delete
AS
if(select Count(*) from Deleted
where Deleted.qty>=20)>0
BEGIN
print'您不能刪除任何記錄'
rollback transaction --事務(wù)回滾
END
GO |
4、比較表修改前后數(shù)據(jù)之間的差別，并根據(jù)差別才去相應(yīng)的操作。
例如：若想規(guī)定每次工資的變動幅度不能超過40％，使用觸發(fā)器可以將修改后的表數(shù)據(jù)和修改前的表數(shù)據(jù)進(jìn)行比較，若超出40%，可以回滾該修改操作。
觸發(fā)器是自動觸發(fā)的，一旦對表中的數(shù)據(jù)做了修改，該觸發(fā)器將立即被激活，充分體現(xiàn)了觸發(fā)的優(yōu)勢，保持了數(shù)據(jù)的完整性；然而，觸發(fā)器性能通常是比較低的。
當(dāng)運(yùn)行觸發(fā)器時，系統(tǒng)處理的大部分時間花費(fèi)在參照它表達(dá)的這一處理上，因?yàn)檫@些表達(dá)既不在內(nèi)存中，也不在數(shù)據(jù)庫設(shè)備上，而邏輯表（刪除表和插入表）總是位于內(nèi)存中。所以觸發(fā)器參照的其他表的位置決定了操作花費(fèi)時間的長短。