O'Reilly logo

Microsoft® SQL Server 2012 Unleashed by Alex T. Silverstein, Chris Gallelli, Paul T. Bertucci, Ray Rankins

Stay ahead with the world's most comprehensive technology and business learning platform.

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, tutorials, and more.

Start Free Trial

No credit card required

Using Nested Triggers

Triggers can be nested up to 32 levels. If a trigger changes a table on which another trigger exists, the second trigger is fired and can then fire a third trigger, and so on. If the nesting level is exceeded, the trigger is canceled, and the transaction is rolled back.

The following error message is returned if the nesting level is exceeded:

Server: Msg 217, Level 16, State 1, Procedure ttt2, Line 2Maximum stored procedure nesting level exceeded (limit 32).

You can disable nested triggers by setting the nested triggers option of sp_configure to 0 (off):

EXEC sp_configure 'nested triggers', 0GORECONFIGURE WITH OVERRIDEGO

After the nested triggers option is turned ...

With Safari, you learn the way you learn best. Get unlimited access to videos, live online training, learning paths, books, interactive tutorials, and more.

Start Free Trial

No credit card required