Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Hello friends, I don’t know what made me to write a blog, either it could be because i sit next to latheesh every day or inspired by reading his blogs daily, Any how, let me join with my Guru in sharing my experience.

we recently got a requirement to maintain the data integrity between two tables on a column which does not have any relationship between them.  Here is the scenario

we have two tables as listed below:



CREATE TABLE dbo.PEOPLE ( PEOPLE_ID INT ,EMPNAME VARCHAR(50), EMP_EMAIL VARCHAR(100))
CREATE TABLE dbo.USERS ( USERID INT ,USERNAME VARCHAR(50), USER_EMAIL VARCHAR(100))

INSERT INTO PEOPLE VALUES (1,'KIRAN','KIRANREDDY@GMAIL.COM')
INSERT INTO USERS VALUES (1,'KIRAN','KIRANREDDY2@GMAIL.COM')

I would like to maintain the same email in both tables for an employee, and email can be updated from either of these two tables.

So i have tried implementing the triggers on both tables when ever there is an update on email, I have to update the same email on both tables.

Here is the code I have implemented:



CREATE TRIGGER UPD_TRG_PEOPLE ON PEOPLE AFTER UPDATE AS
BEGIN
IF UPDATE(EMP_EMAIL)
UPDATE U SET USER_EMAIL=I.EMP_EMAIL FROM USERS U INNER JOIN INSERTED  I ON U.USERID=I.PEOPLE_ID 
END

CREATE TRIGGER UPD_TRG_USERS ON USERS AFTER UPDATE AS
BEGIN
IF UPDATE (USER_EMAIL)
UPDATE P SET P.EMP_EMAIL=I.USER_EMAIL FROM PEOPLE P INNER JOIN INSERTED  I ON P.PEOPLE_ID=I.USERID 
END

When I tried updating the email from people, It has resulted the following error:



Msg 217, Level 16, State 1, Procedure upd_trg_users, Line 3
Maximum stored procedure, function, trigger, or view nesting level exceeded (limit 32).

Solution :
Initially I have tried avoiding this error by changing the nested triggers Server Configuration Option


EXEC sp_configure 'show advanced options', 1;  
GO  
RECONFIGURE ;  
GO  
EXEC sp_configure 'nested triggers', 0 ;  
GO  
RECONFIGURE;  
GO

Even though this error has not appeared when I tried changing email, I feel that this is not an Ideal solution . As this is server level configuration which is applicable across  all databases. This may have other detrimental impact.

Hence i have found another solution with simple tweak in the trigger code:



CREATE TRIGGER UPD_TRG_PEOPLE ON PEOPLE AFTER UPDATE AS
BEGIN
IF UPDATE(EMP_EMAIL) AND  EXISTS (SELECT 1 FROM USERS   U INNER JOIN INSERTED I ON U.USERID = I.PEOPLE_ID WHERE I.EMP_EMAIL <> U.USER_EMAIL )
UPDATE U SET USER_EMAIL=I.EMP_EMAIL FROM USERS U INNER JOIN INSERTED  I ON U.USERID=I.PEOPLE_ID 
END

CREATE TRIGGER UPD_TRG_USERS ON USERS AFTER UPDATE AS
BEGIN
IF UPDATE (USER_EMAIL) AND  EXISTS (SELECT 1 FROM PEOPLE   P INNER JOIN INSERTED I ON P.PEOPLE_ID=I.USERID  WHERE P.EMP_EMAIL <> I.USER_EMAIL )
UPDATE P SET P.EMP_EMAIL=I.USER_EMAIL FROM PEOPLE P INNER JOIN INSERTED  I ON P.PEOPLE_ID=I.USERID 
END

Hope you enjoyed this post, please share your thoughts on the same.

Advertisements

In-Memory OLTP: Compatibility Check for SQL Server and Database

In continuation with my last post, I would like to further move on to the topic by starting with the limitations of In-Memory OLTP feature.

“Entire Data in Memory” looks such a wonderful idea to improve the performance by reducing the locking/latching etc, however, Is it true to adopt for all systems? NO. There are lots of restrictions (at least in SQL Server 2014 and hoping many would be taking off in future versions) with In-Memory OLTP feature.
You can refer those limitation in the below MSDN

Transact-SQL Constructs Not Supported by In-Memory OLTP

Here, I would like to share a mechanism to verify the compatibility level of existing environment to migrate to In-Memory Tables. By now(once you read the above link), as you all know the limitations are more, I would like to split the script in multiple snippets for simplicity.

Please refer the below script to verify the compatibility level of your environment at Server and Data base level.


SET NOCOUNT ON

PRINT '************************************************'
PRINT 'DATABASE SERVER COMPATIBILITY CHECK'
PRINT '************************************************'
IF NOT EXISTS(SELECT 1 FROM (SELECT SUBSTRING(@@VERSION,22,4)) A (VERSIONNUMBER) WHERE VERSIONNUMBER >=2014)
	PRINT 'IN-MEMORY OLTP WILL ONLY SUPPORT IN SQL SERVER 2014 OR LATER VERSIONS.'

IF EXISTS(SELECT 1 FROM (SELECT @@VERSION) A (BITVERSION) WHERE CHARINDEX('(64-BIT)',BITVERSION)=0)
	PRINT 'IN-MEMORY OLTP WILL ONLY SUPPORT IN 64 BIT VERSIONS.'

IF EXISTS(SELECT * FROM MASTER.SYS.SERVER_TRIGGERS)
	PRINT HOST_NAME() + ' IS ENABLED WITH SERVER LEVEL TRIGGER AND IN-MEMORY OLTP WILL NOT SUPPORT SERVER LEVEL TRIGGERS.'


PRINT '************************************************'
PRINT 'DATABASE COMPATIBILITY CHECK'
PRINT '************************************************'
	
IF (DB_NAME() NOT IN (SELECT DB_NAME(DATABASE_ID) FROM SYS.MASTER_FILES WHERE TYPE_DESC = 'FILESTREAM'))
	PRINT 'IN-MEMORY OLTP WILL ONLY SUPPORT WITH MEMORY_OPTIMIZED_DATA FILEGROUP. THE CURRENT DATABASE WILL NOT SUPPORT.'
	
IF (DB_NAME() IN (SELECT NAME FROM SYS.DATABASES WHERE IS_MERGE_PUBLISHED & IS_SUBSCRIBED & IS_DISTRIBUTOR & IS_PUBLISHED = 1))
	PRINT DB_NAME() + 'HAS BEEN REPLICATED AND IN-MEMORY OLTP WILL NOT SUPPORT REPLICATED DATABASE'

IF EXISTS(SELECT * FROM SYS.DATABASES WHERE DB_ID () = DATABASE_ID AND IS_AUTO_CLOSE_ON = 1)
	PRINT DB_NAME() + 'HAS BEEN CONFIGURED WITH AUTO CLOSE OPTION AND IN-MEMORY OLTP WILL NOT SUPPORT AUTO_CLOSE FEATURE'

We will cover more on other aspects like datatypes/constraints etc in future posts, until then, happy learning….