Triggers within CLR - advantage over TSQL
You may be aware the DML and DDL triggers can be nested up to 32 levels, because any reference to such trigger code counts as one-level in the nesting limit. Even though it is possible to control whether AFTER triggers can be nested through the nested triggers server configuration option.
So how it can be escaped using CLR and how procedural and recursive code works much better than in TSQL. No doubt that code usage within C# & .NET have been offering better support on proceduring, as they are designed from the ground up to support per method call-based stack frames and activation records. Because of this feature it has advantage of not suffering from limits such as a maximum call depth of 32 in recursive operations. In addition to this feature the stack frames are not shared by any other process, that is far better for concurrent performance. With Common Table Expressions (CTE) you can still manage the recursion process that is default and configurable option.
For SQL newbie(s), nesting means that when a trigger is fired, it will also cause another trigger to be fired. If a trigger creates an infinitive loop, the nesting level of 32 will be exceeded and the trigger will cancel with an error message. Recursive triggers When a trigger fires and performs a statement that will cause the same trigger to fire, recursion will occur.
So to disable the nesting and recursive execution of triggers you could execute:
SP_CONFIGURE 'nested_triggers',0
GO
RECONFIGURE
GO
For complete stop for trigger recursion:
ALTER DATABASE databasename
SET RECURSIVE_TRIGGERS ON | OFF
The main (major) advantage of TSQL over CLR is on outer joins too, between the 2 tabular results. For instance when you perform this with CLR it will have to compare them on a row-by-row, column-by-column basis, moving and copying matching rows around. This is where it loses the deal by having a slow performance and very difficult to manage the generic loops & routines, hence TSQL will take upper hand.
