Dynamic SQL in trigger

I'm fairly certain that you can do whatever you want inside triggers (for the most part). But I would caution using triggers in the first place. They tend to be performance hogs and can sometimes lead to confusion when debugging complex databases.

As for the dynamic SQL, if you're going to use it, they are also potential performance, security, and maintenance issues. Because they are generated live each execution, you won't have any benefits from the SQL engine being able to save its execution plan. Secondly, dynamic SQL opens you up potentially for SQL injection unless you're careful about how you use your inputs. Finally, it'll be that much harder to debug because you'll be looking at code writing code.

You can do it, but just make sure you're aware of the potential influences that decision will have on the system around it. I would make sure a trigger (and even dynamic SQL) is even necessary before embarking down that road. I would personally lock down the system to only use stored procedures and have them execute the 'dynamic code' over using a trigger.

But, if you must, here is a link that might help[^].