Heute hatte ich wieder ein interessantes Problem im SQL-Server 2005. Ich musste eine Prozedure schreiben die Transactionssicher war, das heißt die Veränderungen im Fehlerfalle richtig zurücknimmt, aber auch ein Rollback durch äußere Transaktionen zuläßt.
Hier die Lösung die ich gefunden habe:
CREATE PROCEDURE [dbo].[UpdateOrInsertValue]
@Key int, --Primary Key
@Value money = 0 --Value
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
DECLARE @TranCounter INT;
SET @TranCounter = @@TRANCOUNT;
IF @TranCounter > 0
SAVE TRANSACTION VerteilungNachMonatenSpeichern;
ELSE
-- Procedure must start its own
-- transaction.
BEGIN TRANSACTION;
BEGIN TRY
-- Monat 1
UPDATE table1 SET Wert = @Value WHERE (ID = @Key)
if @@ROWCOUNT <1
Insert into Table1 (ID, Wert) values (@Key, @Value)
IF @TranCounter = 0
COMMIT TRANSACTION;
END TRY
BEGIN CATCH
IF @TranCounter = 0
-- Transaction started in procedure.
-- Roll back complete transaction.
ROLLBACK TRANSACTION;
ELSE
-- Transaction started before procedure
-- called, do not roll back modifications
-- made before the procedure was called.
IF XACT_STATE() <> -1
-- If the transaction is still valid, just
-- roll back to the savepoint set at the
-- start of the stored procedure.
ROLLBACK TRANSACTION VerteilungNachMonatenSpeichern;
-- If the transaction is uncommitable, a
-- rollback to the savepoint is not allowed
-- because the savepoint rollback writes to
-- the log. Just return to the caller, which
-- should roll back the outer transaction.
-- After the appropriate rollback, echo error
-- information to the caller.
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT @ErrorMessage = ERROR_MESSAGE();
SELECT @ErrorSeverity = ERROR_SEVERITY();
SELECT @ErrorState = ERROR_STATE();
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
END
GO