I know you are asking about SQL Server, but in the Oracle world (in the past), temporary tables had a very high cost, so cursor based procedures and triggers were quicker and lower "cost" to the server. In SQL Server, cursors used to have far higher cost than temp tables, so writing cursor based code was discouraged. I'm pretty sure these discrepancies have been eliminated in the past decade.
To cope with these situations, most people have a general rule to avoid putting business logic into the database. If you can absolutely totally always do that, then there won't be any reason for procedural logic in neither T-SQL nor PL/SQL. Relational databases are great at set-based logic. Most modern programming languages are great at procedural logic. It is best to use each one for what they are good at.
Some auditing triggers that I've worked with had rather complicated rules for what had to be checked, and where things had to be updated/logged. Some were for keeping reporting systems in sync with transactional systems (it wasn't my choice, but they wanted it that way). Some were for a formulary system. A formulary is a list of drugs, and for each insurance company, what they will/won't cover, and if prescribed drug_X what replacements are covered by insurance. It was also common for different group policies at the same insurance company to pay for different drugs.