Use Triggers Or Not In SQL Server?
Check out the discussion in the video TSQL: What Are the Disadvantages of Using Triggers. One of the troubles with triggers involves troubleshooting - pay attention to the point made in the video. In addition, keep in mind that we may accidentally create an OLPT environment with transactions (think of a compounding effect with transactions).
Check out the highest-rated Automating ETL course on Udemy, if you're interested in data.
In the past, I've seen an issue that arose because of what I mention in the video. As a note, the architecture behidn it was appropriate (as is often the case). However, because of the inherent reactive design (mentioned in the video), this was overlooked. There are better alternatives, especially when we consider what architects tend to use triggers to solve for in SQL Server.