Stop using ROLLBACK in triggers; THROW an error
A problem I’ve seen raised on numerous occasions is users that are getting a non-descript error when they are doing some kind of DML/DDL operation, and they don’t know why they are getting the error, and what is causing it. That error is: Msg 3609, Level 16, State 2, Line 1 The transaction ended in the trigger. The batch has been aborted. So, for the "unsuspecting" user that’s performing an INSERT or maybe an ALTER statement, all they know is that the statement failed in a trigger. There are no details what what trigger caused the error, and no details…