Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 INSTEAD OF triggers do not support direct recursio

Author  Topic 

agruner
Starting Member

2 Posts

Posted - 2013-01-29 : 10:23:47
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

52326 Posts

Posted - 2013-01-29 : 10:26:27
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

2 Posts

Posted - 2013-01-29 : 10:37:11
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
Master Smack Fu Yak Hacker

3873 Posts

Posted - 2013-01-29 : 10:41:10
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
   

- Advertisement -