How to control trigger recursion

backgroundA table update, take B table A column again UPDATE a table, which triggers a table of update triggers, my goal is to trigger only once, whether set the nested triggers option to turn off recursive triggers. AnalysisFirst, you must understand the definition of a trigger recursion, which has two recursive methods: 1.     Directly recursive a trigger on table A to change (insert/delete/update) a table data, causing a table trigger to trigger again, this condition called direct recursion; 2. The trigger on the indirect recursive a table updates table B data, which triggers the B table trigger, and the B-table trigger changes a table data, which causes a table trigger to trigger again, a condition called indirect recursion Solving Method -- option configuration (triggers that affect all scopes)SQL Server provides database-level and server-level configuration to determine whether recursive triggers are allowed: 1. Server-level (configured using stored procedure sp_configure) server trigger recursion option (SQL Server 200 5) Decide whether to allow server-level triggers Direct recursive excitationWhen this option is set to 1 (on, default), a server-level trigger is allowed to fire recursively, and a server-level trigger cannot be fired recursively when set to 0 (off). Nested triggers option determines whether triggers are allowed Indirect recursive excitationWhen this option is set to 1 (on, default), triggers are allowed to fire recursively, and triggers cannot be fired recursively when set to 0 (off). 2. Database-level recursive_triggers database option setting determines whether triggers in the database are allowed Direct Recursive triggering; The default value is off, and direct recursive firing is not allowed. This option can be set through stored procedures sp_dboption, and for SQL Server 2005, you can also use a T-SQL setting similar to the following: ALTER DATABASE [dbname] SET recursive_triggers on usage options When determining the behavior of a recursive trigger, you need to be aware of the valid range of option settings: the nested triggers option determines whether all triggers Indirect recursive excitation, which means that this is a SQL Server instance-level option, and the settings will affect all triggers. The server trigger recursion option is available in SQL Server 2005 (SQL Server 2005 has a server-level trigger). The recursive_triggers option affects all triggers in the database in which it is configured. Other workaround (for a specific trigger) If you want only specific triggers to allow or disable triggers, SQL Server does not have the option to do so, and if you do need such a feature, you can implement control in the trigger code: 1.        Use the update (column name) function This function applies to control of update. For "A table update, take B table A column again update a table", if only update a table of some columns to trigger update B, and B table again update a table does not contain those columns a table triggers update B, then the touch of Table A sender, use the IF Update (column) to determine if update B is expected. 2.        Use @ @NESTLEVEL the variable value to determine the number of nesting layers. For "A table update, a table in table B is updated again", if the trigger is not a stored procedure, update A's table trigger @ @NESTLEVEL = 1, to update B, the B table trigger @ @NEST Level = 2,  @ @NESTLEVEL = 3 when table B triggers are then UPDATE A. So if the @ @NESTLEVEL >=3, the general expression is recursive (of course, if the trigger of UPDATE A does not have two levels of recursion, that is, it cannot be stored procedure to call the stored procedure to UPDATE a.) 3.        returns the caller's object_id using the @ @PROCID the global variable. If you need A table to trigger a table B trigger, a table trigger does not respond when a table trigger is triggered again by a B-table trigger, and it is used in a table trigger to determine who the trigger is, and if it is a B-table trigger, it is not processed, similar to the following: if OBJECT_ID (N ' B table Trigger name ') = @ @PROCID BEGIN     PRINT ' B table triggers, do not process '     return '