Is trigger comes as part of transaction in SQL Server

user_6019384 0
Viewed 525

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

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.

user_16462400
Related