Is there a way to ensure that a SQL Server trigger will be executed?

Yes. Triggers occur as an implicit nested transaction within the scope of the caller's transaction. The trigger will commit or roll back as part of this transaction.

See:

• Nesting Transactions
• Rollbacks and Commits in Stored Procedures and Triggers
• ROLLBACK TRANSACTION (Transact-SQL)

There are quite a few situations when triggers do not fire, such as:

• A table is dropped.

• A table is truncated.

• Settings for nested and/or recursive triggers prevent a trigger from firing.

So, in SQL Server a trigger is NOT guaranteed to execute. Sometimes it can be bypassed.

Also a trigger may be just incorrect. Either way, you may end up with orphans in your database.

If you post what you are trying to accomplish with your trigger, maybe we can help you implement it with constraints.