Mixing
Rollback nested transaction and log error - in Trigger, Sql Server 2008
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ height:90px;width:728px;box-sizing:border-box;
}
I have a trigger (that may modify a value on the insert) on insert statements to a table.
If an error occurs in the trigger, I want to log it. And the INSERT must still be inserted.
So I use a TRY / CATCH block, where the catch part, will do the logging.
The problem is that I can only rollback the whole transaction and then log. But then the insert is also rolled back.
Running on Sql Server 2008...
So far so good.
To test the logging in the catch part, I am using RAISERROR. (Maybe that is a problem? I will return to that part). Then I am running the insert to the table.
In the trigger I have placed a BEGIN TRANSACTION triggerTransaction2. I named it, so I could rollback that specific transaction. Note also the insert statement has started a transaction.
But the problem is that I cannot rollback the nested (triggerTransaction2) transaction. Only the whole transaction (using ROLLBACK without a name).
If I look at XACT_STATE() then it is -1. Which should mean that "it can only request a full rollback of the transaction.".
I have tried to simplify the code, and hope it still makes sense.
Also I have followed this example - and that works for me, but is is also not with a nested transaction.
So I am thinking, either is it not good to use RAISERROR with the selected values.
Pseudocode for the trigger:
CREATE TRIGGER [dbo].[Trigger_SomeTableStatus]
ON [dbo].[SomeTable]
FOR INSERT
AS
BEGIN
SET NoCount ON
-- declaring som variables; DECLARE @isActive BIT;
BEGIN TRY
BEGIN TRANSACTION triggerTransaction2
-- SAVE TRANSACTION triggerTransaction2
-- some logic ...
RAISERROR(' Test error message; *failed* ', 16, 1);
COMMIT TRANSACTION triggerTransaction2
END TRY
BEGIN CATCH
-- HERE XACT_STATE() = -1)
ROLLBACK TRANSACTION triggerTransaction2 -- Cannot rollback with the name
BEGIN TRY
BEGIN TRANSACTION triggerTransactionLog
-- Do the logging..
COMMIT TRANSACTION triggerTransactionLog
END TRY
BEGIN CATCH
-- -- Logging of error failed...
END CATCH
END CATCH
END
tsql transactions rollback nested-transactions
asked Jan 3 at 15:20
jma73jma73
366
add a comment |
I have a trigger (that may modify a value on the insert) on insert statements to a table.
If an error occurs in the trigger, I want to log it. And the INSERT must still be inserted.
So I use a TRY / CATCH block, where the catch part, will do the logging.
The problem is that I can only rollback the whole transaction and then log. But then the insert is also rolled back.
Running on Sql Server 2008...
So far so good.
To test the logging in the catch part, I am using RAISERROR. (Maybe that is a problem? I will return to that part). Then I am running the insert to the table.
In the trigger I have placed a BEGIN TRANSACTION triggerTransaction2. I named it, so I could rollback that specific transaction. Note also the insert statement has started a transaction.
But the problem is that I cannot rollback the nested (triggerTransaction2) transaction. Only the whole transaction (using ROLLBACK without a name).
If I look at XACT_STATE() then it is -1. Which should mean that "it can only request a full rollback of the transaction.".
I have tried to simplify the code, and hope it still makes sense.
Also I have followed this example - and that works for me, but is is also not with a nested transaction.
So I am thinking, either is it not good to use RAISERROR with the selected values.
Pseudocode for the trigger:
CREATE TRIGGER [dbo].[Trigger_SomeTableStatus]
ON [dbo].[SomeTable]
FOR INSERT
AS
BEGIN
SET NoCount ON
-- declaring som variables; DECLARE @isActive BIT;
BEGIN TRY
BEGIN TRANSACTION triggerTransaction2
-- SAVE TRANSACTION triggerTransaction2
-- some logic ...
RAISERROR(' Test error message; *failed* ', 16, 1);
COMMIT TRANSACTION triggerTransaction2
END TRY
BEGIN CATCH
-- HERE XACT_STATE() = -1)
ROLLBACK TRANSACTION triggerTransaction2 -- Cannot rollback with the name
BEGIN TRY
BEGIN TRANSACTION triggerTransactionLog
-- Do the logging..
COMMIT TRANSACTION triggerTransactionLog
END TRY
BEGIN CATCH
-- -- Logging of error failed...
END CATCH
END CATCH
END
tsql transactions rollback nested-transactions
asked Jan 3 at 15:20
jma73jma73
366
add a comment |
I have a trigger (that may modify a value on the insert) on insert statements to a table.
If an error occurs in the trigger, I want to log it. And the INSERT must still be inserted.
So I use a TRY / CATCH block, where the catch part, will do the logging.
The problem is that I can only rollback the whole transaction and then log. But then the insert is also rolled back.
Running on Sql Server 2008...
So far so good.
To test the logging in the catch part, I am using RAISERROR. (Maybe that is a problem? I will return to that part). Then I am running the insert to the table.
In the trigger I have placed a BEGIN TRANSACTION triggerTransaction2. I named it, so I could rollback that specific transaction. Note also the insert statement has started a transaction.
But the problem is that I cannot rollback the nested (triggerTransaction2) transaction. Only the whole transaction (using ROLLBACK without a name).
If I look at XACT_STATE() then it is -1. Which should mean that "it can only request a full rollback of the transaction.".
I have tried to simplify the code, and hope it still makes sense.
Also I have followed this example - and that works for me, but is is also not with a nested transaction.
So I am thinking, either is it not good to use RAISERROR with the selected values.
Pseudocode for the trigger:
CREATE TRIGGER [dbo].[Trigger_SomeTableStatus]
ON [dbo].[SomeTable]
FOR INSERT
AS
BEGIN
SET NoCount ON
-- declaring som variables; DECLARE @isActive BIT;
BEGIN TRY
BEGIN TRANSACTION triggerTransaction2
-- SAVE TRANSACTION triggerTransaction2
-- some logic ...
RAISERROR(' Test error message; *failed* ', 16, 1);
COMMIT TRANSACTION triggerTransaction2
END TRY
BEGIN CATCH
-- HERE XACT_STATE() = -1)
ROLLBACK TRANSACTION triggerTransaction2 -- Cannot rollback with the name
BEGIN TRY
BEGIN TRANSACTION triggerTransactionLog
-- Do the logging..
COMMIT TRANSACTION triggerTransactionLog
END TRY
BEGIN CATCH
-- -- Logging of error failed...
END CATCH
END CATCH
END
tsql transactions rollback nested-transactions
asked Jan 3 at 15:20
jma73jma73
366
I have a trigger (that may modify a value on the insert) on insert statements to a table.
If an error occurs in the trigger, I want to log it. And the INSERT must still be inserted.
So I use a TRY / CATCH block, where the catch part, will do the logging.
The problem is that I can only rollback the whole transaction and then log. But then the insert is also rolled back.
Running on Sql Server 2008...
So far so good.
To test the logging in the catch part, I am using RAISERROR. (Maybe that is a problem? I will return to that part). Then I am running the insert to the table.
In the trigger I have placed a BEGIN TRANSACTION triggerTransaction2. I named it, so I could rollback that specific transaction. Note also the insert statement has started a transaction.
But the problem is that I cannot rollback the nested (triggerTransaction2) transaction. Only the whole transaction (using ROLLBACK without a name).
If I look at XACT_STATE() then it is -1. Which should mean that "it can only request a full rollback of the transaction.".
I have tried to simplify the code, and hope it still makes sense.
Also I have followed this example - and that works for me, but is is also not with a nested transaction.
So I am thinking, either is it not good to use RAISERROR with the selected values.
Pseudocode for the trigger:
CREATE TRIGGER [dbo].[Trigger_SomeTableStatus]
ON [dbo].[SomeTable]
FOR INSERT
AS
BEGIN
SET NoCount ON
-- declaring som variables; DECLARE @isActive BIT;
BEGIN TRY
BEGIN TRANSACTION triggerTransaction2
-- SAVE TRANSACTION triggerTransaction2
-- some logic ...
RAISERROR(' Test error message; *failed* ', 16, 1);
COMMIT TRANSACTION triggerTransaction2
END TRY
BEGIN CATCH
-- HERE XACT_STATE() = -1)
ROLLBACK TRANSACTION triggerTransaction2 -- Cannot rollback with the name
BEGIN TRY
BEGIN TRANSACTION triggerTransactionLog
-- Do the logging..
COMMIT TRANSACTION triggerTransactionLog
END TRY
BEGIN CATCH
-- -- Logging of error failed...
END CATCH
END CATCH
END
tsql transactions rollback nested-transactions
tsql transactions rollback nested-transactions
asked Jan 3 at 15:20
jma73jma73
366
asked Jan 3 at 15:20
jma73jma73
366
edited Jan 3 at 15:34
jma73
asked Jan 3 at 15:20
jma73jma73
366
asked Jan 3 at 15:20
jma73jma73
366
asked Jan 3 at 15:20
jma73jma73
366
366
add a comment |
add a comment |
1 Answer
1
active
oldest
votes
Yes, this is because when you make a ROLLBACK the full transaction is cancelled.
You have to create a table in memory like:
DECLARE @Log AS TABLE
(
Description NVARCHAR(100)
);
Then, insert what you want to log in the @Log table.
Last, insert the contents from the @Log table into the Log table in your DB before the END TRY and the END CATCH (or just before the end of the SP)
answered Jan 3 at 15:32
Angel M.Angel M.
1,312415
1
Yes, but I want to rollback only the inner transaction (the sql in the trigger) - shouldn't that be possible?
– jma73
Jan 3 at 15:37
Yes, that's the trick of using a memory table, ROLLBACK does not affect to any variable and memory table are included in that set
– Angel M.
Jan 3 at 15:39
add a comment |
Your Answer
StackExchange.ifUsing("editor", function () {
StackExchange.using("externalEditor", function () {
StackExchange.using("snippets", function () {
StackExchange.snippets.init();
});
});
}, "code-snippets");
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "1"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: true,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: 10,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54025130%2frollback-nested-transaction-and-log-error-in-trigger-sql-server-2008%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
1 Answer
1
active
oldest
votes
1 Answer
1
active
oldest
votes
active
oldest
votes
active
oldest
votes
Yes, this is because when you make a ROLLBACK the full transaction is cancelled.
You have to create a table in memory like:
DECLARE @Log AS TABLE
(
Description NVARCHAR(100)
);
Then, insert what you want to log in the @Log table.
Last, insert the contents from the @Log table into the Log table in your DB before the END TRY and the END CATCH (or just before the end of the SP)
answered Jan 3 at 15:32
Angel M.Angel M.
1,312415
1
Yes, but I want to rollback only the inner transaction (the sql in the trigger) - shouldn't that be possible?
– jma73
Jan 3 at 15:37
Yes, that's the trick of using a memory table, ROLLBACK does not affect to any variable and memory table are included in that set
– Angel M.
Jan 3 at 15:39
add a comment |
Yes, this is because when you make a ROLLBACK the full transaction is cancelled.
You have to create a table in memory like:
DECLARE @Log AS TABLE
(
Description NVARCHAR(100)
);
Then, insert what you want to log in the @Log table.
Last, insert the contents from the @Log table into the Log table in your DB before the END TRY and the END CATCH (or just before the end of the SP)
answered Jan 3 at 15:32
Angel M.Angel M.
1,312415
1
Yes, but I want to rollback only the inner transaction (the sql in the trigger) - shouldn't that be possible?
– jma73
Jan 3 at 15:37
Yes, that's the trick of using a memory table, ROLLBACK does not affect to any variable and memory table are included in that set
– Angel M.
Jan 3 at 15:39
add a comment |
Yes, this is because when you make a ROLLBACK the full transaction is cancelled.
You have to create a table in memory like:
DECLARE @Log AS TABLE
(
Description NVARCHAR(100)
);
Then, insert what you want to log in the @Log table.
Last, insert the contents from the @Log table into the Log table in your DB before the END TRY and the END CATCH (or just before the end of the SP)
answered Jan 3 at 15:32
Angel M.Angel M.
1,312415
Yes, this is because when you make a ROLLBACK the full transaction is cancelled.
You have to create a table in memory like:
DECLARE @Log AS TABLE
(
Description NVARCHAR(100)
);
Then, insert what you want to log in the @Log table.
Last, insert the contents from the @Log table into the Log table in your DB before the END TRY and the END CATCH (or just before the end of the SP)
answered Jan 3 at 15:32
Angel M.Angel M.
1,312415
answered Jan 3 at 15:32
Angel M.Angel M.
1,312415
answered Jan 3 at 15:32
Angel M.Angel M.
1,312415
answered Jan 3 at 15:32
Angel M.Angel M.
1,312415
1,312415
1
Yes, but I want to rollback only the inner transaction (the sql in the trigger) - shouldn't that be possible?
– jma73
Jan 3 at 15:37
Yes, that's the trick of using a memory table, ROLLBACK does not affect to any variable and memory table are included in that set
– Angel M.
Jan 3 at 15:39
add a comment |
1
Yes, but I want to rollback only the inner transaction (the sql in the trigger) - shouldn't that be possible?
– jma73
Jan 3 at 15:37
Yes, that's the trick of using a memory table, ROLLBACK does not affect to any variable and memory table are included in that set
– Angel M.
Jan 3 at 15:39
1
1
Yes, but I want to rollback only the inner transaction (the sql in the trigger) - shouldn't that be possible?
– jma73
Jan 3 at 15:37
Yes, but I want to rollback only the inner transaction (the sql in the trigger) - shouldn't that be possible?
– jma73
Jan 3 at 15:37
Yes, that's the trick of using a memory table, ROLLBACK does not affect to any variable and memory table are included in that set
– Angel M.
Jan 3 at 15:39
Yes, that's the trick of using a memory table, ROLLBACK does not affect to any variable and memory table are included in that set
– Angel M.
Jan 3 at 15:39
add a comment |
Thanks for contributing an answer to Stack Overflow!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fstackoverflow.com%2fquestions%2f54025130%2frollback-nested-transaction-and-log-error-in-trigger-sql-server-2008%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
