SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 INSTEAD OF triggers do not support direct recursio
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

agruner
Starting Member

Germany
2 Posts

Posted - 01/29/2013 :  10:23:47  Show Profile  Reply with Quote
Hello,
I work with an ASP.Net Project which uses a SQL Server 2008 database, but it was originally written for an SQL Server 2005 database. There is implemented a trigger for a certain Update-Statement. At runtime this Update-Statement always rolls back with the following error message:

INSTEAD OF triggers do not support direct recursion. The trigger execution failed.

Searching for this error I got some hints to cope with that, but they all failed to work. I tried the following:

EXEC sp_configure 'nested triggers', 1
RECONFIGURE

EXEC sp_dboption '<name of db>', 'recursive triggers', 'true'

In addition I set the compatibility level of my DB to SQL Server 2005 (and tried to set it to 100 as well):

ALTER DATABASE database_name
SET COMPATIBILITY_LEVEL = 90

But the error message occurs nevertheless.
Do you have any suggestions to me how I can handle this? I don’t want to edit the trigger, since it is a bit complex and I’m new to triggers in SQL.

Thank you for your answers
Antje

visakh16
Very Important crosS Applying yaK Herder

India
52317 Posts

Posted - 01/29/2013 :  10:26:27  Show Profile  Reply with Quote
without understanding what trigger is doing its hard to suggest. Only thing we can guess is the logic instead is prompting it to call trigger again and causing recursion.

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Go to Top of Page

agruner
Starting Member

Germany
2 Posts

Posted - 01/29/2013 :  10:37:11  Show Profile  Reply with Quote
Here's the trigger which causes the problem I guess. I just thought that SQL Server 2008 supports INSTEAD of triggers with recursion, I read something like that.

Antje


USE [ICService]
GO
/****** Object:  Trigger [dbo].[TRIG_onEditPosition]    Script Date: 01/29/2013 15:45:15 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[TRIG_onEditPosition]
	ON [dbo].[positionen]
INSTEAD OF UPDATE
AS
BEGIN
	DECLARE @id int;
	SET @id = (SELECT id FROM Deleted);
	--prüfen, ob die Spalten start, oder ende betroffen waren, wenn ja löschen und neuer instert... (Columns_updated)	
	--IF (substring(columns_updated(),1,1) & 6) > 0 --es wird geprüft, ob die zweite und/oder dritte Spalte geändert wurde, das sollten start und ende sein.
	IF ( (SELECT verbucht FROM Deleted d JOIN servicescheine ss ON d.saschein_id = ss.id) = 1)
		RAISERROR (N'Es wurde versucht eine verbuchte Position zu ändern! Das ist unzulässig!',12,12) WITH LOG;
	ELSE
	IF ( UPDATE (start) OR UPDATE (ende) )
		--PRINT 'es ist entweder start, oder ende vom Update betroffen.'
		IF (((SELECT start FROM deleted) <> (SELECT start FROM inserted)) OR ((SELECT ende FROM deleted) <> (SELECT ende FROM inserted)))
		BEGIN	
			IF ((SELECT start FROM inserted) <> (SELECT ende FROM inserted))
			BEGIN
				--es wurde eines der Datums geändert! - deshalb muss die Prozedur aufgerufen werden!
				-- ausser es handelt sich um einen stunden-datensatz...
				--alten Datensatz löschen
				DELETE FROM positionen WHERE id = @id;
				--den kompletten Datensatz neu anlegen (dadurch keine Probleme bei Datumsübertrag);
				-- außerdem Sicherung möglich.... bzw Protokollierung
				DECLARE @sd datetime, @ed datetime, @a varchar(32), @b varchar(256), @kstK int,
					 @sas int, @atc int, @wlbe int, @f bit, @pause decimal(3,2), @stdSatz money;
				SELECT  @sd = start,
						@ed = ende,
						@a = arbeit,
						@kstK = kostenstelle_id,
						@sas = saschein_id,
						@atc = arbeitstyp_id,
						@f = fahrt,
						@wlbe = wlbEintrag,
						@b = bemerkungen,
						@pause = pause,
						@stdSatz = stdSatz
				FROM inserted;
				EXECUTE insertPosition @sd, @ed, @a, @kstK, @sas, @atc, @f, @wlbe, @pause, @stdSatz, @b;
			END; 
			else
				UPDATE positionen
					SET start = I.start,
					ende = I.ende,
					arbeit = I.arbeit,
					bemerkungen = I.bemerkungen,
					kostenstelle_id = I.kostenstelle_id,
					saschein_id = I.saschein_id,
					arbeitstyp_id = I.arbeitstyp_id,
					wlbEintrag = I.wlbEintrag,
					fahrt = I.fahrt,
					stunden = I.stunden,
					pause = I.pause,
					stdSatz = I.stdSatz,
					provision_id = I.provision_id,
					bezahlteStunden = I.bezahlteStunden
				FROM positionen p, inserted I
				WHERE p.id = I.id;
		END;
	--wenn die Datumswerte nicht betroffen waren, ganz normales Update ausführen:
		else 
			BEGIN
			--PRINT 'start oder Ende betroffen, aber nicht geändert, Ganz normales Update';
				UPDATE positionen
					SET start = I.start,
					ende = I.ende,
					arbeit = I.arbeit,
					bemerkungen = I.bemerkungen,
					kostenstelle_id = I.kostenstelle_id,
					saschein_id = I.saschein_id,
					arbeitstyp_id = I.arbeitstyp_id,
					wlbEintrag = I.wlbEintrag,
					fahrt = I.fahrt,
					stunden = I.stunden,
					pause = I.pause,
					stdSatz = I.stdSatz,
					provision_id = I.provision_id,
					bezahlteStunden = I.bezahlteStunden
				FROM positionen p, inserted I
				WHERE p.id = I.id;
			END;
	ELSE 
	BEGIN
		--PRINT 'Weder start, noch Ende betroffen, Ganz normales Update';
		UPDATE positionen
			SET start = I.start,
			ende = I.ende,
			arbeit = I.arbeit,
			bemerkungen = I.bemerkungen,
			kostenstelle_id = I.kostenstelle_id,
			saschein_id = I.saschein_id,
			arbeitstyp_id = I.arbeitstyp_id,
			wlbEintrag = I.wlbEintrag,
			fahrt = I.fahrt,
			stunden = I.stunden,
			pause = I.pause,
			stdSatz = I.stdSatz,
			provision_id = I.provision_id,
			bezahlteStunden = I.bezahlteStunden
		FROM positionen p, inserted I
		WHERE p.id = I.id;
	END;
END;


Go to Top of Page

James K
Flowing Fount of Yak Knowledge

3587 Posts

Posted - 01/29/2013 :  10:41:10  Show Profile  Reply with Quote
Adding to Visakh's suggestion:

From MSDN, "If an INSTEAD OF trigger defined on a table executes a statement against the table that would ordinarily fire the INSTEAD OF trigger again, the trigger is not called recursively" http://msdn.microsoft.com/en-us/library/ms189799.aspx

So this is the expected behavior. What you want to find out is whether there really is a need for a recursive instead of trigger. If there is, you will need to dig through the logic to figure out how else the logic can be implemented.

But if you don't need to fire the recursive instead of triggers, then one option would be to disable recursive triggers. I would be very careful doing this if it that option is currently enabled for fear that you may break some existing functionality.

Read through that MSDN link that I posted - it has some very useful information about the two options (nested triggers and recursive triggers).
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000