Question: I have this SQL script:
DECLARE @in_TE_UID varchar(36)
DECLARE @in_ZO_BETE_Sort int
DECLARE @in_user int
SET @in_TE_UID = '9f510440-8828-44ce-bbea-6bc866902262'
SET @in_ZO_BETE_Sort = 0
SET @in_user = 12435
-- http://stackoverflow.com/questions/884334/tsql-try-catch-transaction-in-trigger
BEGIN TRANSACTION BeforeUpdate;
BEGIN TRY
IF NOT EXISTS
(
SELECT 1 FROM T_FMS_ZO_Benutzer_TeaserOrder
WHERE (T_FMS_ZO_Benutzer_TeaserOrder.ZO_BETE_Status = 1)
AND (T_FMS_ZO_Benutzer_TeaserOrder.ZO_BETE_BE_ID = @in_user)
AND
(
T_FMS_ZO_Benutzer_TeaserOrder.ZO_BETE_TE_UID = @in_TE_UID
OR
(
@in_TE_UID IS NULL
AND
T_FMS_ZO_Benutzer_TeaserOrder.ZO_BETE_TE_UID IS NULL
)
)
)
BEGIN
INSERT INTO T_FMS_ZO_Benutzer_TeaserOrder
(
ZO_BETE_UID
,ZO_BETE_BE_ID
,ZO_BETE_TE_UID
,ZO_BETE_Sort
,ZO_BETE_Status
)
VALUES
(
NEWID() --<ZO_BETE_UID, uniqueidentifier,>
,@in_user --<ZO_BETE_BE_ID, int,>
,@in_TE_UID --<ZO_BETE_TE_UID, uniqueidentifier,>
,@in_ZO_BETE_Sort --<ZO_BETE_Sort, int,>
,1 --<ZO_BETE_Status, int,>
)
;
END
ELSE
BEGIN
UPDATE T_FMS_ZO_Benutzer_TeaserOrder
SET ZO_BETE_Sort = @in_ZO_BETE_Sort
WHERE (T_FMS_ZO_Benutzer_TeaserOrder.ZO_BETE_Status = 1)
AND (T_FMS_ZO_Benutzer_TeaserOrder.ZO_BETE_BE_ID = @in_user)
AND
(
T_FMS_ZO_Benutzer_TeaserOrder.ZO_BETE_TE_UID = @in_TE_UID
OR
(
@in_TE_UID IS NULL
AND
T_FMS_ZO_Benutzer_TeaserOrder.ZO_BETE_TE_UID IS NULL
)
)
;
END
END TRY
BEGIN CATCH
IF @@TRANCOUNT > 0
ROLLBACK TRANSACTION BeforeUpdate --RollBack in case of Error
--RETURN
DECLARE @ErrorMessage NVARCHAR(4000);
DECLARE @ErrorSeverity INT;
DECLARE @ErrorState INT;
SELECT
@ErrorMessage = ERROR_MESSAGE(),
@ErrorSeverity = ERROR_SEVERITY(),
@ErrorState = ERROR_STATE();
-- Use RAISERROR inside the CATCH block to return error
-- information about the original error that caused
-- execution to jump to the CATCH block.
RAISERROR (@ErrorMessage, -- Message text.
@ErrorSeverity, -- Severity.
@ErrorState -- State.
);
END CATCH
It runs fine when I run it once.
If afterwards I run a
SELECT * FROM T_FMS_ZO_Benutzer_TeaserOrder
then it timeouts.
If I add with (nolock)
then it works.
If I look at the processes in SSMS, I see a locked select with LCK_M_S
.
If I execute the same command in code, I always get this error:
Transaction count after EXECUTE indicates a mismatching number of BEGIN and COMMIT statements. Previous count = 0, current count = 1
So really, what's wrong with this code ?
Best Answer
You haven't put a
COMMIT
in your SQL anywhere. Presumably it should go just before theEND TRY