PHYSICAL ELEMENTS OF DATA MODELS - A Developer's Guide to Data Modeling for SQL Server - page 75

You can also control trigger nesting and recursion behavior. With
nested triggers turned on, one trigger firing can perform a DML and cause
another trigger to fire. For example, inserting a row into TableA causes
TableA's insert trigger to fire. TableA's insert trigger in turn updates a
record in TableB, causing TableB's update trigger to fire. That is trigger
nesting —one trigger causing another to fire—and this is the default be-
havior. With nested triggers turned on, SQL Server allows as many as 32
triggers to be nested. The INSTEAD OF trigger can nest regardless of the
setting of the nested triggers option.
Server trigger recursion specifies whether or not a trigger can per-
form a DML statement that would cause the same trigger to fire again. For
example, an update trigger on TableA issues an additional update on
TableA. With recursive triggers turned on, it causes the same trigger to fire
again. This setting affects only direct recursion; that is, a trigger directly
causes itself to fire again. Even with recursion off, a trigger could cause an-
other trigger to fire, which in turn could cause the original trigger to fire
again. Be very careful when you use recursive triggers. They can run over
and over again, causing a performance hit to your server.
CLR Integration
As of SQL Server 2005, we gained the ability to integrate with the .NET
Framework Common Language Runtime (CLR). Simply put, CLR inte-
gration allows you to use .NET programming languages within SQL Server
objects. You can create stored procedures, user-defined functions, triggers,
and CLR user-defined types using the more advanced languages available
in Microsoft .NET. This level of programming is beyond the scope of this
book, but you need to be aware of SQL Server's ability to use CLR. You
will likely run into developers who want to use CLR, or you may find your-
self needing to implement a complex business rule that cannot easily be
implemented using standard SQL Server objects and T-SQL. So if you are
code savvy or have a code-savvy friend, you can create functions using CLR
to enforce complex rules.
Implementing Supertypes and Subtypes
We discuss supertypes and subtypes in Chapter 2. These are entities
that have several kinds of real-world objects being modeled. For example,
we might have a supertype called phone with subtypes for corded and