Is trigger comes as part of transaction in SQL Server












4














I have three tables in SQL Server:




  1. Employee

  2. EmployeeDetails

  3. 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







share|improve this question




















  • 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 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
















4














I have three tables in SQL Server:




  1. Employee

  2. EmployeeDetails

  3. 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







share|improve this question




















  • 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 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














4












4








4







I have three tables in SQL Server:




  1. Employee

  2. EmployeeDetails

  3. 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







share|improve this question















I have three tables in SQL Server:




  1. Employee

  2. EmployeeDetails

  3. 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






share|improve this question















share|improve this question













share|improve this question




share|improve this question








edited Nov 17 '18 at 8:29









marc_s

571k12811031251




571k12811031251










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: 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














  • 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 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








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












1 Answer
1






active

oldest

votes


















0














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.







share|improve this answer























  • 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










  • 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











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
});


}
});














draft saved

draft discarded


















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









0














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.







share|improve this answer























  • 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










  • 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
















0














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.







share|improve this answer























  • 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










  • 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














0












0








0






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.







share|improve this answer














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.








share|improve this answer














share|improve this answer



share|improve this answer








edited Nov 17 '18 at 11:49

























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 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










  • @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










  • @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










  • @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


















draft saved

draft discarded




















































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.




draft saved


draft discarded














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





















































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







-

Popular posts from this blog

How to change which sound is reproduced for terminal bell?

Image
3 Recently upgrade to 18.10 from 18.04, and using the terminal is becoming annoying: the new alert sound is a drip ( /usr/share/sounds/gnome/default/alerts/drip.ogg ), and the previous one is still located at ( /usr/share/sounds/ubuntu/stereo/bell.ogg ). My issue is that at the gnome-terminal, when autocompleting or pressing left BOTH sounds are reproduced, at the same time. I want to just have ubuntu/stereo/bell.ogg active, not both . At the moment I have been only able to reduce the volume of the alerts (but still both are played), or turn the alerts down (but I dislike both "solutions"). gnome-terminal 18.10 teminal-bell share | improve this question ...
Read more

Can I use Tabulator js library in my java Spring + Thymeleaf project?

Image
0 I want to add Tabulator js to my java project. I added tabulator.min.js, tabulator.min.css to resources folder. After that added to my html page these dependencies as recommending in readme.md. <link rel="stylesheet" th:href="@{/css/tabulator.min.css}"> <script type="text/javascript" th:src="@{/js/tabulator.min.js}"></script> Now my tables view all elements, that I have in the html page, but not correct. In the documentation of Tabulator is example how to create header and table, var table = new Tabulator("#example-table", { columns:[ {title:"Name", field:"name", sorter:"string", width:200, editor:true}, {title:"Age", field:"age", sorter:"...
Read more

Title Spacing in Bjornstrup Chapter, Removing Chapter Number From Contents

Image
up vote 5 down vote favorite 2 I would like to make it so that the vertical space below the title of each chapter is the same when using the Bjornstrup package (i.e. so that the descenders do not affect the spacing - as shown by the red arrow in the picture below). I think I'm fairly happy with the spacing around "A chapter", so I'd really like titles without descenders (e.g. "Contents") to have the same vertical spacing as "A chapter" below the title. I would also like to remove the large "0" in the contents title. Any help appreciated, as always. documentclass[11pt,a4paper]{book} % Packages[![enter image description here][1]][1] usepackage[left=3.5cm, right=3.5cm, top=4.3cm, bottom=4.25cm]{geometry} usepackage[utf8]{inputenc} usepackage{libertine} usepackage[libertine]{newtx...
Read more