Maximum Stored Procedure, Function, Trigger, or View Nesting Level Exceeded (limit 32). Hatasının Çözümü

Trigger’larda DML komutu çalıştırıldığında tetiklendiğini ve tablo ile alakalı veri değiştirme-ekleme ya da log tutmak gibi işlemler için kullandığımızı biliyoruz. Bazen ihtiyaç halinde bir tablo üzerinde birden fazla Trigger yazma ihtiyacı doğabilir. Farklı amaçlar için yazılmış bu Trigger’lar tetiklenirken işlemini gerçekleştirememekte hata döndürmektedir. Tanımladığımız Trigger’larda oluşan bu hatayı inceleyip 4 adet çözüm yoluna beraber örneklendirerek bakalım;

İlk adım olarak problemimize örnek teşkil edecek bir tablo oluşturalım.

CREATE TABLE TRIGGERTEST

(

PK_TRIGGERTEST INT IDENTITY,

COMMENT NVARCHAR(100),

TRIGGERCONTENT NVARCHAR(100),

CREATEDTIME DATETIME DEFAULT GETDATE(),

UPDATEDTIME DATETIME DEFAULT GETDATE(),

)

Daha sonra tablomuzda update işlemi gerçekleştirildikten sonra tetiklenecek olan birinci Trigger’ımızı oluşturalım. GETDATE() fonksiyonu ile otomatik olarak işlem yapılan zamanı tablodaki alanımıza güncelleyelim.

CREATE TRIGGER TG_UPDATEDTIME

ON TRIGGERTEST AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON

UPDATE TRIGGERTEST

SET UPDATEDTIME = GETDATE()

FROM TRIGGERTEST T

INNER JOIN inserted I ON T.PK_TRIGGERTEST = I.PK_TRIGGERTEST

SET NOCOUNT OFF

END

Şimdi oluşturduğumuz tabloda bir veri ekleyip hemen sonrasında Update yaparak yukarıdaki Trigger’ımızın tetiklenmesini sağlayalım.

INSERT TRIGGERTEST (COMMENT)

VALUES (‘MSHOWTO1’)

UPDATE TRIGGERTEST

SET COMMENT = ‘MSHOWTO1 DEĞİŞTİRİLDİ’

Resim-1

Buraya kadar her şey normal. Daha sonra 2. Trigger oluşturup tabloya veri ekleme-değiştirme işlemini tekrar gerçekleştirelim.

CREATE TRIGGER TG_UPDATEDTIME2

ON TRIGGERTEST

AFTER UPDATE

AS

BEGIN

SET NOCOUNT ON

UPDATE TRIGGERTEST

SET TRIGGERCONTENT = ‘UPDATED ‘ + CAST(GETDATE() AS NVARCHAR(20))

FROM TRIGGERTEST t

INNER JOIN inserted I ON T.PK_TRIGGERTEST = I.PK_TRIGGERTEST

SET NOCOUNT OFF

END

Daha sonra Tablomuza veri girme işlemini tekrar edelim.

INSERT TRIGGERTEST (CONTENT)

VALUES (‘MSHOWTO2’)

UPDATE TRIGGERTEST

SET CONTENT=‘MSHOWTO2 DEĞİŞTİRİLDİ’

Aşağıdaki hatayı aldık:

Msg 217, Level 16, State 1, Procedure TG_UPDATEDTIME2, Line 5 [Batch Start Line 39]

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

Bu hatayı detaylandırırsak, bir tabloda bir DML çalıştırdığımızda birden fazla Trigger bulunduğundan bu Trigger’lara yazdığımız UPDATE işlemleri tekrar Trigger’ı tetiklediğinden bir döngü oluşturmaktadır ve tekrar tekrar tetiklenmesine neden olmaktadır. Nesting Level seviyesine ulaşıldığında ise bize hata olarak geri bildirim döndürmektedir. Bu sorunun 3 tane çözümü bulunmaktadır:

Çözüm Adımı 1:

Nesting Level seviyesini kontrol etmek ve güncelleme işlemini bir kez çalıştırmaktır.

Trigger’larımızı aşağıdaki şekilde güncelleyelim,

ALTER TRIGGER TG_UPDATEDTIME

ON TRIGGERTEST AFTER UPDATE

AS

BEGIN

IF TRIGGER_NESTLEVEL() > 1

RETURN

UPDATE TRIGGERTEST

SET UPDATEDTIME = GETDATE()

FROM TRIGGERTEST T

INNER JOIN inserted I ON T.PK_TRIGGERTEST = I.PK_TRIGGERTEST

END

–#######################################–

ALTER TRIGGER TG_UPDATEDTIME2

ON TRIGGERTEST

AFTER UPDATE

AS 

IF TRIGGER_NESTLEVEL() > 1

RETURN

UPDATE TRIGGERTEST

SET TRIGGERCOMMENT =‘UPDATED ‘+CAST(GETDATE() AS NVARCHAR(20))

FROM TRIGGERTEST t

INNER JOIN inserted I ON T.PK_TRIGGERTEST = I.PK_TRIGGERTEST

Çözüm Adımı 2:

SP_configure ayarlarından nested_triggers ayarını pasif hale getirmektir. Bu sayede Nested kontrolü sağlanmayacaktır. Tanımladığımız Trigger’ların herhangi bir takılmaya uğramadan çalışmasını sağlayacaktır.

EXEC sp_configure ‘show advanced options’, 1;

GO

RECONFIGURE;

GO

EXEC sp_configure ‘nested triggers’, 1 ;

GO

RECONFIGURE;

GO

Çözüm Adımı 3:

Aynı tabloya bağlanmış olan birden fazla Trigger’ı tek Trigger içerisinde toplamak ve gerçekleştirilecek işlemleri akışa bağlayarak tek seferde tamamlamaktır. Bu sayede birden fazla Trigger’ların çakışmasını önlemek adına atılmış en güzel adım olacaktır.

Çözüm Adımı 4:

Veri tabanından Trigger’ların yineleme işlemini kapatabilirsiniz. Buradaki amaç sadece ilgili veri tabanında yinelemeyi durdurmaktır. Çözüm adımı 2’de yer alan işlem tüm server için geçerli olmaktadır.

USE AdventureWorks2016

GO

ALTER DATABASE AdventureWorks2016

SET RECURSIVE_TRIGGERS OFF

GO

Bu konuyla ilgili sorularınızı  alt kısımda bulunan yorumlar alanını kullanarak sorabilirsiniz.

Referanslar

www.mshowto.org

https://stackoverflow.com/questions/34747476/nested-triggers-in-sql-server

https://www.codeproject.com/Questions/364657/Maximum-stored-procedure-function-trigger-or-view

https://docs.microsoft.com/en-us/sql/relational-databases/triggers/create-nested-triggers?view=sql-server-2017

TAGs: Trigger,Maximum Stored Procedure, Function, Trigger, or View Nesting Level Exceeded (limit 32). Error, Maximum Stored Procedure, Function, Trigger, or View Nesting Level Exceeded (limit 32). hatası