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
https://stackoverflow.com/questions/34747476/nested-triggers-in-sql-server
https://www.codeproject.com/Questions/364657/Maximum-stored-procedure-function-trigger-or-view
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ı