ตอนที่ 11 : การใช้ Transaction เพื่อ Rollback และ Commit (SQL Server : Stored Procedure) |
ตอนที่ 11 : การใช้ Transaction เพื่อ Rollback และ Commit (SQL Server : Stored Procedure) อีกความสามารถหนึ่งที่น่าสนใจบน SQL Server ในการเขียน Stored Procedure คือการจัดการกับความถูกต้องในการทำงานของ SQL Statement และ Query ด้วย Transaction ความสามารถของ Transaction คือ หลังจากที่เรา Start Transaction จะสามารถควบคุมการทำงานของ Query ที่เกิดขึ้น ในกรณีที่มีการ Error หรือ เกิดเงื่อนไขที่ไม่ต้องการ เราจะสามารถทำการ Rollback ย้อนกลับข้อมูลที่เกิดขึ้นระหว่างการทำงาน เช่น INSERT, UPDATE และ DELETE ให้กลับมายังจุดก่อนที่ Stored Procedure ที่จะทำได้
ถ้าจะพูดถึงเรื่อง Transaction บน SQL Server เราสามารถใช้การทำงานผ่านโปรแกรมที่เรียกใช้งานได้ เช่นแทนที่จะใช้ Transaction บน Stored Procedure ก็ไปประกาศบน PHP, .NET หรืออื่น ๆ ซึ่งจะทำให้การควบคุม Transaction นั้นได้ดีกว่า เพราะสามารถ Rollback ได้ทั้งหมดของ Process ที่เกิดขึ้น
Table : CUSTOMER
Table : COUNTRY
Transaction Syntax
BEGIN TRANSACTION
-- Statement 1
-- Statement 2
COMMIT or ROLLBACK
Transaction and Try Catch Scope
BEGIN TRANSACTION
BEGIN TRY
-- Statement 1
-- Statement 2
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
END CATCH
Example : ตัวอย่างการใช้ TRANSACTION ในรูปแบบที่ง่าย ๆ บน SQL Server Store Procedure
USE [mydatabase]
GO
/****** Object: StoredProcedure [dbo].[insertCustomer] Script Date: 10/23/2015 22:41:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[insertCustomer]
@pCustomerID VARCHAR(4),
@pName VARCHAR(50),
@pEmail VARCHAR(50),
@pCountryCode VARCHAR(2),
@pBudget DECIMAL(18,2),
@pUsed DECIMAL(18,2),
@pStatus INT OUTPUT,
@pMessage VARCHAR(500) OUTPUT
AS
BEGIN
BEGIN TRANSACTION
BEGIN TRY
-- insert Statement 1
INSERT INTO CUSTOMER VALUES (@pCustomerID,@pName,@pEmail,@pCountryCode,@pBudget,@pUsed);
-- insert Statement 2
INSERT INTO CUSTOMER VALUES (@pCustomerID,@pName,@pEmail,@pCountryCode,@pBudget,@pUsed);
SET @pStatus = 1;
SET @pMessage = 'Insert Data Successfully';
COMMIT
END TRY
BEGIN CATCH
ROLLBACK
SET @pStatus = 0;
SELECT @pMessage = ERROR_MESSAGE();
END CATCH
END
จากตัวอย่างนี้จะมีการ Insert ข้อมูลลงในตารางจำนวน 2 ครั้ง ซึ่งจะ Error ของ TRY CATCH เกิดขึ้นแน่นอนเพราะ Key ซ้ำ และเมื่อ Error เกดขึ้นจะมีการทำงานในส่วนของ BEGIN CATCH ซึ่งจะมีคำสั่งในการ ROLLBACK ข้อมูล
DECLARE @return_value int,
@pStatus int,
@pMessage varchar(500)
EXEC @return_value = [dbo].[insertCustomer]
@pCustomerID = 'C005',
@pName = 'Fun Wipa',
@pEmail = '[email protected]',
@pCountryCode = 'TH',
@pBudget = '100000',
@pUsed = '0',
@pStatus = @pStatus OUTPUT,
@pMessage = @pMessage OUTPUT
SELECT @pStatus as '@pStatus',
@pMessage as '@pMessage'
Result
สามารถประยุกต์ใช้ได้กับ SAVEPOINT โดยเราจะใช้การ SAVEPOINT ในตำแหน่งที่ต้องการ แล้วสามารถที่จะ ROLLBACK มายัง SAVEPOINT นี้ได้
USE [mydatabase]
GO
/****** Object: StoredProcedure [dbo].[insertCustomer] Script Date: 10/23/2015 22:41:40 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[insertCustomer]
@pCustomerID VARCHAR(4),
@pName VARCHAR(50),
@pEmail VARCHAR(50),
@pCountryCode VARCHAR(2),
@pBudget DECIMAL(18,2),
@pUsed DECIMAL(18,2),
@pStatus INT OUTPUT,
@pMessage VARCHAR(500) OUTPUT
AS
BEGIN
BEGIN TRANSACTION
SAVE TRANSACTION SavePoint
BEGIN TRY
-- insert Statement 1
INSERT INTO CUSTOMER VALUES (@pCustomerID,@pName,@pEmail,@pCountryCode,@pBudget,@pUsed);
-- insert Statement 2
INSERT INTO CUSTOMER VALUES (@pUsed,@pName,@pEmail,@pCountryCode,@pBudget,@pUsed);
SET @pStatus = 1;
SET @pMessage = 'Insert Data Successfully';
COMMIT TRANSACTION SavePoint;
END TRY
BEGIN CATCH
ROLLBACK TRANSACTION SavePoint
SET @pStatus = 0;
SELECT @pMessage = ERROR_MESSAGE();
END CATCH
END
|
ช่วยกันสนับสนุนรักษาเว็บไซต์ความรู้แห่งนี้ไว้ด้วยการสนับสนุน 
Load balance : Server 02
