iT邦幫忙

3

[筆記][MSSQL]關於SQL的交易概念

在T-SQL語法中我們可以用到「新增(INSERT)」、「更新(UPDATE)」、「刪除(DELETE)」來做到處理資料的作業和功能,但是在實務上常常會有許多事情是無法由一段T-SQL處理完成的,例如在進銷存系統中的進貨作業,一項作業就必須更新「進貨單主檔」、「進貨單明細」、「產品庫存」三個資料表。

面對上述狀況有一部分人會用「觸發程序(Trigger)」,但是在無法使用「觸發程序(Trigger)」的狀態下,就必須執行三段T-SQL才能完成一項進貨作業,但每一段T-SQL都是獨立執行的,執行完第一段就會接著執行第二段、第三段,也就是說就算其中第一段或第二段的T-SQL錯誤,第三段SQL一樣會執行,這種狀況會直接造成庫存數量錯誤,引起資料錯亂,當然也可以靠程式去處理這些問題,但這些事其實可以直接在SQL中用「交易(Transaction)」做判斷處理。

交易管理的語法:
1.BEGIN TRANSACTION:開啟交易,打開交易功能。
2.COMMIT TRANSACTION:確認交易,在交易結束時確認交易,在確認時資料才會真的寫入資料表。
3.ROLLBACK TRANSACTION:回復交易,執行這行時,會回復在交易內所有T-SQL所更動的內容。

以下使用交易:


--先開啟交易
BEGIN TRANSACTION

--定義變數,這邊用來放值型的T-SQL是否有錯誤
DECLARE @M int,@D int ,@S int ,@R int 

--新增進貨單
INSERT INTO purchase_m
(purchase_id)
VALUES
('PUR1')

--擷取上一次(進貨單)執行T-SQL的錯誤錯誤代碼
SELECT @M = @@ERROR

--新增進貨明細
INSERT INTO purchase_d
(purchase_id,seq,prod_id,QTY)
VALUES
('PUR1','1','PROD1',100)

--擷取上一次(進貨明細)執行T-SQL的錯誤錯誤代碼
SELECT @D = @@ERROR

--異動庫存
UPDATE stock
SET QTY = QTY + 100
WHERE prod_id = 'PROD1'

--擷取上一次(產品庫存)執行T-SQL的錯誤錯誤代碼
SELECT @S = @@ERROR

--擷取上一次(產品庫存)執行T-SQL的異動筆數
SELECT @R = @@ROWCOUNT

--之後來做判斷,看是否三次執行T-SQL的錯誤都為0
--最後一個@R是判斷產品的庫存量是否有被異動成功
IF @M = 0 AND @D = 0 AND @S = 0 AND @R = 1

--沒問題就確認交易完成,資料也會在這時候確認新增和修改
COMMIT TRANSACTION

ELSE

--若是有問題就取消交易,讓資料不被這三段T-SQL所影響
ROLLBACK TRANSACTION

Function:
1.@@ERROR
擷取最後一次執行T-SQL的錯誤代碼,成功執行會回傳0。

2.@@ROWCOUNT
擷取最後一次執行T-SQL的異動筆數。


1 則留言

0
utopia
iT邦新手 4 級 ‧ 2017-10-11 12:18:55

感謝大大無私分享

不會啦!之前看書看到的,想說把他整理出來,不然我自己也沒有什麼能分享的技術XD

我要留言

立即登入留言