Question

Define nested trigger in SQL Server?

Answer

Nested Trigger: - In Sql Server, triggers are said to be nested when the action of one trigger initiates another trigger that may be on the same table or on the different table. For example, suppose there is a trigger t1 defined on the table tbl1 and there is another trigger t2 defined on the table tbl2, if the action of the trigger t1 initiates the trigger t2 then both the triggers are said to be nested. In SQL Server, triggers can be nested up to 32 levels. If the action of nested triggers results in an infinite loop, then after the 32 level, the trigger terminates. Since the triggers are executed within a transaction, therefore failure at any level of within nested triggers can cancel the entire transaction, and it result in total rollback.

We can also stop the execution of nested triggers through the following SQL Command:

SP _CONFIGURE 'nested _triggers',0
GO
RECONFIGURE
GO

Read More..   [1055 clicks]

Published under: SQL Server Interview Questions ·  ·  ·  ·