// ------------- 代碼開(kāi)始 --------------------
CREATE TRIGGER [RemoveTopAndNodeText] ON [dbo].[Navtion_TopSubject]
INSTEAD OF DELETE
AS
/* 定義觸發(fā)器使用的變量 */
DECLARE
@fTopID Char(36),
@fNodeCount Int,
@fTextCount Int,
@fTopName VarChar

/* 把傳送的需要?jiǎng)h除的fTopID鍵值賦值給@fTopID變量 */
/* 開(kāi)始事務(wù) */
BEGIN TRAN Remove_TopSubject
Set @fTopID = (Select fTopID From deleted)
Set @fTopName = (Select fTopName From deleted)
/* 保存刪除前保存點(diǎn)，防止出錯(cuò) */
Save Tran my_Save1
/* 首先判斷子類表NodeSubject中是否有所屬內(nèi)容 */
Set @fNodeCount = (Select Count(*) From Navtion_NodeSubject Where Navtion_NodeSubject.fTopID = @fTopID)
If @fNodeCount > 0
Begin
/* 判斷內(nèi)容表tText是否有所屬內(nèi)容 */
Set @fTextCount = (Select Count(*) From tText Where tText.fTopID = @fTopID)
If @fTextCount > 0
Begin
Delete From tText Where tText.fTopID = @fTopID
Delete From Navtion_NodeSubject Where fTopID = @fTopID
Delete From Navtion_TopSubject Where fTopID = @fTopID
End
Else
Begin
Delete From Navtion_NodeSubject Where fTopID = @fTopID
Delete From Navtion_TopSubject Where fTopID = @fTopID
End
End
Else
Begin
Delete From Navtion_TopSubject Where fTopID = @fTopID
End
If @@Error = 0
Commit Transaction
Else
Begin
Rollback Transaction my_Save1
Raiserror('刪除出現(xiàn)錯(cuò)誤，記錄：%s及其所屬內(nèi)容沒(méi)有被刪除。',16,1,@fTopName)
End
//------------------代碼結(jié)束---------------------

If @@Error = 0
判斷，是否出現(xiàn)錯(cuò)誤
@@Error是Sql Server的一個(gè)全局變量，保存上一個(gè)Sql命令是否出錯(cuò)，如果出錯(cuò)，@@Error=1
如果是0，表示沒(méi)有出錯(cuò)。
-------------------------------------------
Commit Transaction
上面是沒(méi)有出錯(cuò)的語(yǔ)句，表示事務(wù)提交，（可以看作是結(jié)束事務(wù)）
如果前面有了Begin Tran開(kāi)始事務(wù)的代碼，必須在后面加上這個(gè)代碼，否則事務(wù)不結(jié)束，數(shù)據(jù)庫(kù)記錄將被鎖定，無(wú)法對(duì)數(shù)據(jù)庫(kù)記錄進(jìn)行操作！
--------------------------------------------
Else
@@Error變量大于0，表示出現(xiàn)錯(cuò)誤
----------------------------------
Begin
Rollback Transaction my_Save1
Raiserror('刪除出現(xiàn)錯(cuò)誤，記錄：%s及其所屬內(nèi)容沒(méi)有被刪除。',16,1,@fTopName)
End