所以我想檢查是否可以更新表 RegEnfermeiro（如果不能更新則回滾），如果它可以更新然后檢查是否可以更新表 RegEnfAtiv，如果它無法回滾這個和 RegEnfemeiro，并且該程序繼續到下一個。我認為這會起作用，但它沒有。有人可以解釋我怎樣才能使它起作用嗎？
'''
CREATE PROCEDURE dbo.CheckRegEnf @id_Enf int, @id int, @Data datetime, @Hora datetime, @noUtente int , @idAtiv int, @ID_Medicamento int
AS
BEGIN TRAN
DECLARE @count as int;
SET @count = 0;
BEGIN TRY
UPDATE RegEnfermeiro SET Data = @Data, Hora = @Hora WHERE id = @id and noUtente = @noUtente;
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH
BEGIN TRY
UPDATE RegEnfAtiv SET idAtiv = @idAtiv where idReg = @id
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH
BEGIN TRY
UPDATE RegEnfMEd SET ID_Medicamento = @ID_Medicamento where ID_RegEnf = @id
END TRY
BEGIN CATCH
ROLLBACK TRAN;
END CATCH
COMMIT TRAN
uj5u.com熱心網友回復：
你最好在XACT_ABORT這里使用。從檔案中：
當 SET XACT_ABORT 為 ON 時，如果 Transact-SQL 陳述句引發運行時錯誤，則整個事務將終止并回滾。
這意味著如果程序中的一條陳述句失敗，所有陳述句都將回滾：
CREATE PROCEDURE dbo.CheckRegEnf @id_Enf int,
@id int,
@Data datetime,
@Hora datetime,
@noUtente int,
@idAtiv int,
@ID_Medicamento int
AS
BEGIN;
SET NOCOUNT ON;
SET XACT_ABORT ON;
/*
You don't use @Count in your batch, so what purpose is this for?
DECLARE @count AS int;
SET @count = 0;
*/
BEGIN TRY
BEGIN TRAN;
UPDATE RegEnfermeiro
SET Data = @Data,
Hora = @Hora
WHERE id = @id
AND noUtente = @noUtente;
UPDATE RegEnfAtiv
SET idAtiv = @idAtiv
WHERE idReg = @id;
UPDATE RegEnfMEd
SET ID_Medicamento = @ID_Medicamento
WHERE ID_RegEnf = @id;
COMMIT;
END TRY
BEGIN CATCH
THROW;
END CATCH;
END;
db<>小提琴示例
轉載請註明出處，本文鏈接：https://www.uj5u.com/qukuanlian/492714.html
上一篇：T-SQL中的XML決議
下一篇：如何將憑據傳遞給RESTAPI