搜尋此網誌
2013年1月3日 星期四
Stored Procedure 的 Transaction 處理基本寫法
本範例: 欲刪除Option item 時, 也同時刪除 Option 所包含的下載附件.
----------------------------------------------------------------------------------------------------
CREATE PROC DeleteOption
@OptionId int
AS
BEGIN
DECLARE @TranStarted bit
SET @TranStarted = 0
DECLARE @ErrorCode int
SET @ErrorCode = 0
IF(@@TRANCOUNT = 0)
BEGIN
BEGIN TRANSACTION
SET @TranStarted = 1
END
DELETE FROM
DNNIT_MS_ProductOptionDownloads
WHERE
OptionId = @OptionId
IF(@@ERROR <> 0)
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
DELETE FROM
DNNIT_MS_ProductOptions
WHERE
OptionId = @OptionId
IF(@@ERROR <> 0)
BEGIN
SET @ErrorCode = -1
GOTO Cleanup
END
IF(@TranStarted = 1)
BEGIN
SET @TranStarted = 0
COMMIT TRANSACTION
END
RETURN 0
Cleanup:
IF(@TranStarted = 1)
BEGIN
SET @TranStarted = 0
ROLLBACK TRANSACTION
END
RETURN @ErrorCode
END
訂閱:
張貼留言 (Atom)
沒有留言:
張貼留言