Is trigger comes as part of transaction in SQL Server
I have three tables in SQL Server:
- Employee
- EmployeeDetails
- EmployeeHistory
I wrote a trigger on the Employee table so that if entry is inserted into Employee, then it also inserts a row into EmployeeHistory; which is working fine.
Now I have created stored procedure with transaction and inserting records into Employee, then EmployeeDetails. After inserting the record into Employee and if there is any issue and the transaction is rolled back, then will the row inserted into EmployeeHistory also be removed or not?
sql-server database edited Nov 17 '18 at 8:29
marc_s
571k12811031251
asked Nov 17 '18 at 8:13
chandra prakash kabra
529
add a comment |
I have three tables in SQL Server:
- Employee
- EmployeeDetails
- EmployeeHistory
I wrote a trigger on the Employee table so that if entry is inserted into Employee, then it also inserts a row into EmployeeHistory; which is working fine.
Now I have created stored procedure with transaction and inserting records into Employee, then EmployeeDetails. After inserting the record into Employee and if there is any issue and the transaction is rolled back, then will the row inserted into EmployeeHistory also be removed or not?
sql-server database edited Nov 17 '18 at 8:29
marc_s
571k12811031251
asked Nov 17 '18 at 8:13
chandra prakash kabra
529
1
Why don't you try and see for yourself?
– Zohar Peled
Nov 17 '18 at 8:15
4
Execution of a trigger always takes place within a transaction that includes the original statement, so if the trigger fails, the original statement is rolled back. Rather than asking, though, you can simply test it: useRAISERRORorSELECT 1 / 0to simulate failure. Due to T-SQL's arcane rules for error handling, testing to see what actually happens is always a good idea anyway.
– Jeroen Mostert
Nov 17 '18 at 8:15
Agreed let me try myself.
– chandra prakash kabra
Nov 17 '18 at 8:20
add a comment |
I have three tables in SQL Server:
- Employee
- EmployeeDetails
- EmployeeHistory
I wrote a trigger on the Employee table so that if entry is inserted into Employee, then it also inserts a row into EmployeeHistory; which is working fine.
Now I have created stored procedure with transaction and inserting records into Employee, then EmployeeDetails. After inserting the record into Employee and if there is any issue and the transaction is rolled back, then will the row inserted into EmployeeHistory also be removed or not?
sql-server database edited Nov 17 '18 at 8:29
marc_s
571k12811031251
asked Nov 17 '18 at 8:13
chandra prakash kabra
529
I have three tables in SQL Server:
- Employee
- EmployeeDetails
- EmployeeHistory
I wrote a trigger on the Employee table so that if entry is inserted into Employee, then it also inserts a row into EmployeeHistory; which is working fine.
Now I have created stored procedure with transaction and inserting records into Employee, then EmployeeDetails. After inserting the record into Employee and if there is any issue and the transaction is rolled back, then will the row inserted into EmployeeHistory also be removed or not?
sql-server database sql-server database edited Nov 17 '18 at 8:29
marc_s
571k12811031251
asked Nov 17 '18 at 8:13
chandra prakash kabra
529
edited Nov 17 '18 at 8:29
marc_s
571k12811031251
asked Nov 17 '18 at 8:13
chandra prakash kabra
529
edited Nov 17 '18 at 8:29
marc_s
571k12811031251
edited Nov 17 '18 at 8:29
marc_s
571k12811031251
edited Nov 17 '18 at 8:29
marc_s
571k12811031251
571k12811031251
asked Nov 17 '18 at 8:13
chandra prakash kabra
529
asked Nov 17 '18 at 8:13
chandra prakash kabra
529
asked Nov 17 '18 at 8:13
chandra prakash kabra
529
529
1
Why don't you try and see for yourself?
– Zohar Peled
Nov 17 '18 at 8:15
4
Execution of a trigger always takes place within a transaction that includes the original statement, so if the trigger fails, the original statement is rolled back. Rather than asking, though, you can simply test it: useRAISERRORorSELECT 1 / 0to simulate failure. Due to T-SQL's arcane rules for error handling, testing to see what actually happens is always a good idea anyway.
– Jeroen Mostert
Nov 17 '18 at 8:15
Agreed let me try myself.
– chandra prakash kabra
Nov 17 '18 at 8:20
add a comment |
1
Why don't you try and see for yourself?
– Zohar Peled
Nov 17 '18 at 8:15
4
Execution of a trigger always takes place within a transaction that includes the original statement, so if the trigger fails, the original statement is rolled back. Rather than asking, though, you can simply test it: useRAISERRORorSELECT 1 / 0to simulate failure. Due to T-SQL's arcane rules for error handling, testing to see what actually happens is always a good idea anyway.
– Jeroen Mostert
Nov 17 '18 at 8:15
Agreed let me try myself.
– chandra prakash kabra
Nov 17 '18 at 8:20
1
1
Why don't you try and see for yourself?
– Zohar Peled
Nov 17 '18 at 8:15
Why don't you try and see for yourself?
– Zohar Peled
Nov 17 '18 at 8:15
4
4
Execution of a trigger always takes place within a transaction that includes the original statement, so if the trigger fails, the original statement is rolled back. Rather than asking, though, you can simply test it: use
RAISERROR or SELECT 1 / 0 to simulate failure. Due to T-SQL's arcane rules for error handling, testing to see what actually happens is always a good idea anyway.– Jeroen Mostert
Nov 17 '18 at 8:15
Execution of a trigger always takes place within a transaction that includes the original statement, so if the trigger fails, the original statement is rolled back. Rather than asking, though, you can simply test it: use
RAISERROR or SELECT 1 / 0 to simulate failure. Due to T-SQL's arcane rules for error handling, testing to see what actually happens is always a good idea anyway.– Jeroen Mostert
Nov 17 '18 at 8:15
Agreed let me try myself.
– chandra prakash kabra
Nov 17 '18 at 8:20
Agreed let me try myself.
– chandra prakash kabra
Nov 17 '18 at 8:20
add a comment |
1 Answer
1
active
oldest
votes
By default, the option XACT_ABORT is ON in a trigger. It can be seen here. When this feature is ON, any error that occurs break off/abort the batch, so your whole transaction will be rolled back.
Microsoft documentation says:
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a
run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL
statement that raised the error is rolled back and the transaction
continues processing. Depending upon the severity of the error, the
entire transaction may be rolled back even when SET XACT_ABORT is OFF.
answered Nov 17 '18 at 11:21
StepUp
7,34174473
I do not understand the terminology XACT_ABORT here but i tried and it is working like trigger value is also coming in transaction if roll back then all part is going to roll back.
– chandra prakash kabra
Nov 17 '18 at 11:24
@chandraprakashkabra please, read my updated answer again to see whatxact_abortfor.
– StepUp
Nov 17 '18 at 11:27
Yup now understood. But in your first post you are saying that By default XACT_ABORT is ON and in second it is OFF.? Little confusion here.
– chandra prakash kabra
Nov 17 '18 at 11:30
@chandraprakashkabra for trigger XACT_ABORT is ON, but for other situations XACT_ABORT is OFF. Please, read this article lextonr.wordpress.com/tag/xact_abort
– StepUp
Nov 17 '18 at 11:37
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%2f53349469%2fis-trigger-comes-as-part-of-transaction-in-sql-server%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
By default, the option XACT_ABORT is ON in a trigger. It can be seen here. When this feature is ON, any error that occurs break off/abort the batch, so your whole transaction will be rolled back.
Microsoft documentation says:
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a
run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL
statement that raised the error is rolled back and the transaction
continues processing. Depending upon the severity of the error, the
entire transaction may be rolled back even when SET XACT_ABORT is OFF.
answered Nov 17 '18 at 11:21
StepUp
7,34174473
I do not understand the terminology XACT_ABORT here but i tried and it is working like trigger value is also coming in transaction if roll back then all part is going to roll back.
– chandra prakash kabra
Nov 17 '18 at 11:24
@chandraprakashkabra please, read my updated answer again to see whatxact_abortfor.
– StepUp
Nov 17 '18 at 11:27
Yup now understood. But in your first post you are saying that By default XACT_ABORT is ON and in second it is OFF.? Little confusion here.
– chandra prakash kabra
Nov 17 '18 at 11:30
@chandraprakashkabra for trigger XACT_ABORT is ON, but for other situations XACT_ABORT is OFF. Please, read this article lextonr.wordpress.com/tag/xact_abort
– StepUp
Nov 17 '18 at 11:37
add a comment |
By default, the option XACT_ABORT is ON in a trigger. It can be seen here. When this feature is ON, any error that occurs break off/abort the batch, so your whole transaction will be rolled back.
Microsoft documentation says:
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a
run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL
statement that raised the error is rolled back and the transaction
continues processing. Depending upon the severity of the error, the
entire transaction may be rolled back even when SET XACT_ABORT is OFF.
answered Nov 17 '18 at 11:21
StepUp
7,34174473
I do not understand the terminology XACT_ABORT here but i tried and it is working like trigger value is also coming in transaction if roll back then all part is going to roll back.
– chandra prakash kabra
Nov 17 '18 at 11:24
@chandraprakashkabra please, read my updated answer again to see whatxact_abortfor.
– StepUp
Nov 17 '18 at 11:27
Yup now understood. But in your first post you are saying that By default XACT_ABORT is ON and in second it is OFF.? Little confusion here.
– chandra prakash kabra
Nov 17 '18 at 11:30
@chandraprakashkabra for trigger XACT_ABORT is ON, but for other situations XACT_ABORT is OFF. Please, read this article lextonr.wordpress.com/tag/xact_abort
– StepUp
Nov 17 '18 at 11:37
add a comment |
By default, the option XACT_ABORT is ON in a trigger. It can be seen here. When this feature is ON, any error that occurs break off/abort the batch, so your whole transaction will be rolled back.
Microsoft documentation says:
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a
run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL
statement that raised the error is rolled back and the transaction
continues processing. Depending upon the severity of the error, the
entire transaction may be rolled back even when SET XACT_ABORT is OFF.
answered Nov 17 '18 at 11:21
StepUp
7,34174473
By default, the option XACT_ABORT is ON in a trigger. It can be seen here. When this feature is ON, any error that occurs break off/abort the batch, so your whole transaction will be rolled back.
Microsoft documentation says:
When SET XACT_ABORT is ON, if a Transact-SQL statement raises a
run-time error, the entire transaction is terminated and rolled back.
When SET XACT_ABORT is OFF, in some cases only the Transact-SQL
statement that raised the error is rolled back and the transaction
continues processing. Depending upon the severity of the error, the
entire transaction may be rolled back even when SET XACT_ABORT is OFF.
answered Nov 17 '18 at 11:21
StepUp
7,34174473
edited Nov 17 '18 at 11:49
answered Nov 17 '18 at 11:21
StepUp
7,34174473
answered Nov 17 '18 at 11:21
StepUp
7,34174473
answered Nov 17 '18 at 11:21
StepUp
7,34174473
7,34174473
I do not understand the terminology XACT_ABORT here but i tried and it is working like trigger value is also coming in transaction if roll back then all part is going to roll back.
– chandra prakash kabra
Nov 17 '18 at 11:24
@chandraprakashkabra please, read my updated answer again to see whatxact_abortfor.
– StepUp
Nov 17 '18 at 11:27
Yup now understood. But in your first post you are saying that By default XACT_ABORT is ON and in second it is OFF.? Little confusion here.
– chandra prakash kabra
Nov 17 '18 at 11:30
@chandraprakashkabra for trigger XACT_ABORT is ON, but for other situations XACT_ABORT is OFF. Please, read this article lextonr.wordpress.com/tag/xact_abort
– StepUp
Nov 17 '18 at 11:37
add a comment |
I do not understand the terminology XACT_ABORT here but i tried and it is working like trigger value is also coming in transaction if roll back then all part is going to roll back.
– chandra prakash kabra
Nov 17 '18 at 11:24
@chandraprakashkabra please, read my updated answer again to see whatxact_abortfor.
– StepUp
Nov 17 '18 at 11:27
Yup now understood. But in your first post you are saying that By default XACT_ABORT is ON and in second it is OFF.? Little confusion here.
– chandra prakash kabra
Nov 17 '18 at 11:30
@chandraprakashkabra for trigger XACT_ABORT is ON, but for other situations XACT_ABORT is OFF. Please, read this article lextonr.wordpress.com/tag/xact_abort
– StepUp
Nov 17 '18 at 11:37
I do not understand the terminology XACT_ABORT here but i tried and it is working like trigger value is also coming in transaction if roll back then all part is going to roll back.
– chandra prakash kabra
Nov 17 '18 at 11:24
I do not understand the terminology XACT_ABORT here but i tried and it is working like trigger value is also coming in transaction if roll back then all part is going to roll back.
– chandra prakash kabra
Nov 17 '18 at 11:24
@chandraprakashkabra please, read my updated answer again to see what
xact_abort for.– StepUp
Nov 17 '18 at 11:27
@chandraprakashkabra please, read my updated answer again to see what
xact_abort for.– StepUp
Nov 17 '18 at 11:27
Yup now understood. But in your first post you are saying that By default XACT_ABORT is ON and in second it is OFF.? Little confusion here.
– chandra prakash kabra
Nov 17 '18 at 11:30
Yup now understood. But in your first post you are saying that By default XACT_ABORT is ON and in second it is OFF.? Little confusion here.
– chandra prakash kabra
Nov 17 '18 at 11:30
@chandraprakashkabra for trigger XACT_ABORT is ON, but for other situations XACT_ABORT is OFF. Please, read this article lextonr.wordpress.com/tag/xact_abort
– StepUp
Nov 17 '18 at 11:37
@chandraprakashkabra for trigger XACT_ABORT is ON, but for other situations XACT_ABORT is OFF. Please, read this article lextonr.wordpress.com/tag/xact_abort
– StepUp
Nov 17 '18 at 11:37
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.
Some of your past answers have not been well-received, and you're in danger of being blocked from answering.
Please pay close attention to the following guidance:
- 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%2f53349469%2fis-trigger-comes-as-part-of-transaction-in-sql-server%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
1
Why don't you try and see for yourself?
– Zohar Peled
Nov 17 '18 at 8:15
4
Execution of a trigger always takes place within a transaction that includes the original statement, so if the trigger fails, the original statement is rolled back. Rather than asking, though, you can simply test it: use
RAISERRORorSELECT 1 / 0to simulate failure. Due to T-SQL's arcane rules for error handling, testing to see what actually happens is always a good idea anyway.– Jeroen Mostert
Nov 17 '18 at 8:15
Agreed let me try myself.
– chandra prakash kabra
Nov 17 '18 at 8:20