3 months ago i posted a question about SQL SERVER 2005

I was migrate a SQL Server 2000 to SQL Server 2005 and the back-end code is
not function well.
I get from SQL Server 2005 the message
"INSTEAD OF TRIGGERS DO NOT SUPPORT DIRECT RECURSION. TRIGGER EXECUTION
FAILED."

I ASK AGAIN THERE IS NO BACKWARD COMPATIBILITY FROM SQL 2000 TO SQL2005

BECAUSE IS VERY IMPORTANT IF WE ARE REMAIN IN SQL 2000 OR MIGRATE TO 2005

Dimitris Ioannou (DimitrisIoan***@discussions.microsoft.com) writes:
What are you actually trying to do? Could you post the code of the trigger
that gives you the message? It's difficult to comment without context.

Ok but the first question is why
the back-end code is functioning well in SQL 2000 and
NOT functioning well in SQL 2005?

and second the code is too large and complicate because
is an invoice generator with unlimited issues based on company business-rules

so if you want to understand what happen in SQL 2005, you have to test an
example like my case.

Three months ago another guy like you he asked the case, i explain to him
with an example but he didn't answer to me.

The problem is that i create an instead of trigger that is called
recursivelly from a procedure and the procedure is called recursivelly from a
trigger.

So the steps are:
1) i create a transaction [T]
2) i call a procedure [A]
3) procedure [A] call an instead off trigger [B]
4) instead off trigger [B] call trigger [C]
5) trigger [C] call procedure [A]

.... steps 2 until 5 based on business Rule

n) I commit transction [T]


When in TRANSACTION [T]
the trigger was triggering from procedure for the first time it's OK but the
second time i get the message
"INSTEAD OF TRIGGERS DO NOT SUPPORT DIRECT RECURSION"

I don't understant where is the direct recursion????

This code was writting in SQL 2000 and is functioning well.

Tell me what happens?

Show quote

Hide quote

Dimitris Ioannou (DimitrisIoan***@discussions.microsoft.com) writes:
On which ground do you think it works on SQL 2000? If I understand
your description correctly:

You are re-issueing the INSERT on the table while on an excursion from
the INSTEAD OF trigger. This has never worked. You can re-issue the
INSERT statement directly in the INSTEAD of trigger, but not in any
scoped invoked by the INSTEAD of trigger.

There has however been a change. In SQL 2000 RTM the INSERT statement
was just silently ignored. In a later service pack of SQL 2000 an
error message was added. I don't know which service pack, but I would
guess on SP2.

In any case, the script below gives precisely the message you talk
about, both on SQL 2005 and SQL 2000 SP4.

CREATE TABLE alpha (a int NOT NULL)
go
CREATE PROCEDURE doalpha @a int AS
   INSERT alpha(a) VALUES (@a)
go
CREATE TRIGGER alpha_tri ON alpha INSTEAD OF INSERT AS
DECLARE @a int
IF @@rowcount <> 1
BEGIN
    RAISERROR ('Only one row at a time!', 16, 1)
    ROLLBACK TRANSACTION
END
PRINT 'Alpha_tri at nestelevel ' + ltrim(str(@@nestlevel))
SELECT @a = a - 1FROM inserted
IF @a > 4
    EXEC doalpha @a
ELSE
    INSERT alpha (a) VALUES(@a)  
go
EXEC doalpha 3
EXEC doalpha 8
go
select * from alpha
go
drop table alpha
drop proc doalpha

1) My Database is SQL 2000 with SP4

2) it's working for 4 years continuesly without bug or errors

3) It is not correct to say "In SQL 2000 RTM the INSERT statement
Maybe you don't understand the case. I HAVE SP4

4) IN Your example you create ONLY procedure [A] and instead of triggers [B].
You missed the trigger [C]

5) AND I HAVE THE EXAMPLE THAT THE CODE IS WORKING VERY WELL IN SQL2000 WITH
SP4 AND DON'T WORKING IN SQL2005 WITH SP1

Show quote

Hide quote

Dimitris Ioannou wrote:
Dimitris, if you want a solution to your problem, you should post that
example, so we all know what we are talking about.

Razvan

Dimitris Ioannou (DimitrisIoan***@discussions.microsoft.com) writes:
I composed a simplified repro from your instructions. It was twenty
past midnight, and I needed to go to bed soon.

The trigger C should not make any more legal as far as I can see.

It may be a bug that it "works". There are other situations where code
compiles in SQL 2000 that does not compile in SQL 2005. The bug is that
incorrect code is not detected in SQL 2000.

Instead of shouting, how about that you compose a repro that demonstrates
the problem? That is a script that runs with no error in SQL 2000 but
gives an error in SQL 2005. You will have to excuse me, but I don't feel
like spending time of trying to guess what you are doing.

And please keep in mind that in these newsgroups you never get less help
than you pay for.

By the way, have you tried to put the database in compatibility mode 80?

OK My friends
I am sorry about CAPITAL LETTERS in my emails
you have right that without an example we have nothing to discuss.
So i made an easy example in SQL 2000 with SP4
and i send the code of example and i explain to you what happens.

// this table is the invoice table
// the column NUMOFCP (NumberOfCopies) (A Simple Business Rule)
// means that when i put a new record and this column has the number 3
// and i call a procedure to issue this record then
// will be create in salestran also 3-1=2 invoices issued. (Recursive Calls)
// isissue = 0 means not issue
// isissue=1 means issue
// PARENTID means that when we have copy then new record has parentid=slsid of
// the previous record was issued
//FPARENTID is the first SLSID of Invoices that when issued made all copies

CREATE TABLE SALESTRAN
(
    SLSID              NUMERIC(10)           NOT NULL,
    ISISSUE            VARCHAR(1)            NOT NULL
        DEFAULT '0'
        CONSTRAINT CKC_ISISSUE_SALESTRAN CHECK (ISISSUE IN ('0','1')),
    DESCR              VARCHAR(25)            NOT NULL,
    NUMOFCP            NUMERIC(4)            NOT NULL,
    PARENTID          NUMERIC(10)            NULL,
    FPARENTID          NUMERIC(10)           NULL,
    CONSTRAINT PK_SALESTRAN PRIMARY KEY (SLSID)
)
GO



// This table has the relation between previous SLSID Invoice and Next
// from Copy Invoice SLSIDTO

CREATE TABLE SLSTRNTRANSTO
(
    SLSTRNTRANSID      NUMERIC(10)           IDENTITY,
    SLSID              NUMERIC(10)           NOT NULL,
    SLSIDTO            NUMERIC(10)           NULL    ,
    CONSTRAINT PK_SLSTRNTRANSTO PRIMARY KEY (SLSTRNTRANSID)
)
GO



CREATE VIEW VIEW_SLSTRNTRANSTO WITH ENCRYPTION, VIEW_METADATA  AS
SELECT SLSTRNTRANSTO.SLSTRNTRANSID, SLSTRNTRANSTO.SLSID,
SLSTRNTRANSTO.SLSIDTO,
SALESTRAN.DESCR
FROM SLSTRNTRANSTO, SALESTRAN
WHERE SLSTRNTRANSTO.SLSIDTO = SALESTRAN.SLSID

GO 





// This procedure is the first step of all that i call to issue Invoice

CREATE PROCEDURE FINALSALES
   @VSLSID NUMERIC(10)
WITH ENCRYPTION
AS
DECLARE @VNUMOFCP NUMERIC(4)
DECLARE @VFPARENTID NUMERIC(10)
BEGIN
SET NOCOUNT ON

SELECT @VNUMOFCP=NUMOFCP-1, @VFPARENTID=FPARENTID
FROM SALESTRAN WITH (UPDLOCK)
WHERE SLSID = @VSLSID


UPDATE SALESTRAN
        SET ISISSUE='1'
WHERE SLSID=@VSLSID

IF (@VFPARENTID IS NULL)
BEGIN
    UPDATE SALESTRAN
            SET FPARENTID=SLSID
    WHERE SLSID=@VSLSID

END

IF (@VNUMOFCP>0)
BEGIN
    INSERT INTO VIEW_SLSTRNTRANSTO (SLSTRNTRANSID,
               SLSID,
               SLSIDTO,DESCR)
        VALUES (-1, @VSLSID, NULL,'*')

END


END

GO



// With this trigger i start to make a copy

CREATE TRIGGER INS_VIEW_SLSTRNTRANSTO ON VIEW_SLSTRNTRANSTO
WITH ENCRYPTION
INSTEAD OF INSERT
AS
BEGIN
SET NOCOUNT ON
    INSERT INTO SLSTRNTRANSTO (SLSID,
                       SLSIDTO)
          SELECT  SLSID,
                  SLSIDTO
          FROM INSERTED

END


GO





//This trigger make copy in SALESTRAN and Call recursive the procedure
//SALESTRAN

CREATE TRIGGER SLSTRNTRANSTO_INS ON SLSTRNTRANSTO
WITH ENCRYPTION
FOR INSERT
AS
DECLARE @VSLSTRNTRANSID NUMERIC(10)
DECLARE @VSLSID NUMERIC(10)
DECLARE @VNEWSLSID NUMERIC(10)
DECLARE @VNUMOFCP NUMERIC(4)
DECLARE @VFPARENTID NUMERIC(10)
DECLARE @VPARENTID NUMERIC(10)
DECLARE @VDESCR VARCHAR(25)
BEGIN
SET NOCOUNT ON
SAVE TRANSACTION TRAN1

SELECT @VSLSTRNTRANSID=SLSTRNTRANSID, @VSLSID=SLSID
FROM INSERTED

SELECT @VDESCR=DESCR,
       @VNUMOFCP=NUMOFCP-1,
       @VPARENTID=@VSLSID,
       @VFPARENTID=FPARENTID
FROM SALESTRAN
WHERE SLSID=@VSLSID

SELECT @VNEWSLSID=MAX(SLSID)+1
FROM SALESTRAN

INSERT INTO SALESTRAN
(
SLSID, ISISSUE, DESCR, NUMOFCP, PARENTID, FPARENTID
)
VALUES
(
@VNEWSLSID, '0', @VDESCR, @VNUMOFCP, @VPARENTID, @VFPARENTID
)


    UPDATE SLSTRNTRANSTO
        SET SLSIDTO=@VNEWSLSID
    WHERE SLSTRNTRANSID=@VSLSTRNTRANSID

    EXEC FINALSALES @VNEWSLSID


END

GO



So in My Previous e-mail
we have the following steps
1) Procedure SALESTRAN that when issue an Invoice call
an instead of Insert Trigger INS_VIEW_SLSTRNTRANSTO depends From Business Rule

2)  the instead of Insert Trigger INS_VIEW_SLSTRNTRANSTO call the
FOR Insert trigger SLSTRNTRANSTO_INS

3) the trigger FOR Insert SLSTRNTRANSTO_INS call the procedure SALESTRAN

.... the Recursive calls from 1 to 3 depends from number in field NUMOFCP - 1


SO I CREATE A NEW DATABASE name="TEST" IN SQL2000 with SP4

I RUN THE PREVIOUS CODE THAT MAKES 2 TABLES, 1 VIEW, 1 PROCEDURE,
1 ISTEAD OF INSERT VIEW TRIGGER, 1 FOR INSERT TABLE TRIGGER

AFTER I RUN THE FOLLOWING

INSERT INTO SALESTRAN (SLSID, ISISSUE, DESCR, NUMOFCP)
VALUES(1,'0','INVOICE',3)

DECLARE @ID NUMERIC(10)
SET @ID=1
EXEC SALESTRAN @ID

AFTER EXECUTION YOU SEE IN SALESTRAN 3 INVOICES ISSUED

SLSID ISISSUE     DESCR      NUMOFCP       PARENTID     FPARENTID
1        '1'            'INVOICE'          3                                
      1
2        '1'            'INVOICE'          2                   1            
     1
3        '1'            'INVOICE'          1                   2            
     1


after all I DETACH THE Database "TEST" From SQL 2000 SP4
and I Attach The Database "TEST" to SQL 2005 SP1

i insert

INSERT INTO SALESTRAN (SLSID, ISISSUE, DESCR, NUMOFCP)
VALUES(4,'0','INVOICE',3)

DECLARE @ID NUMERIC(10)
SET @ID=4
EXEC SALESTRAN @ID

and I get From SQL2005 The Message
"INSTEAD OF TRIGGERS DO NOT SUPPORT DIRECT RECURSION. TRIGGER EXECUTION
FAILED"


SO IF I CREATE A NEW DATABASE IN SQL2005 AND RUN THE EXAMPLE CODE
I GET THE SAME ERROR MESSAGE

I THINK THAT THESE EXAMPLE IS NOT A DIRECT RECURSION.
AND IN SQL2000 FUNCTION WELL

Thank you in advance for your time that spend for me.

I believe that i help you and Please Tell me if you find anything BECAUSE it
is OUR SCOPE to migrate in SQL 2005.

Show quote

Hide quote

Dimitris Ioannou (DimitrisIoan***@discussions.microsoft.com) writes:
This is something that I would never let go unnoticed in a code
review: since inserted can hold many rows, you can read the columns
into variables.

And if you do this, you should add a check in the beginning.

   IF @@rowcount > 1
   BEGIN
      ROLLBACK TRANSACTION
      RAISERROR('This trigger does not handle multi-row operations', 16, 1)
      RETURN
   END

Permit me to point out that your soluition is not very robust even on
SQL 2000: try setting NOOFCP to 13 and see what happens.

It should be possible to rewrite this in single, set-based operations.
It does help to use a table of numbers. That is, a table with numbers
1, 2, and up. I might look into that, but it may not happen until the
weekend.

As for the issue as such, it seems that Razvan found that this is a
change documented in Books Online.

Erland Sommarskog wrote:
Erland, I think that there is (at least one) another change: Books
Online topic "Behavior Changes to Database Engine Features in SQL
Server 2005" says:

   [In SQL Server 2000] Direct recursion of triggers occurs only
   when a trigger fires and performs an action that causes the
   same trigger to fire again.

   [In SQL Server 2005] Direct recursion of triggers occurs in either
   of the following circumstances:
    - A trigger fires and performs an action that causes the
      same trigger to fire again.
    - The same trigger is called again, but after a trigger of a
      different type (AFTER or INSTEAD OF) is called.
   Indirect recursion occurs when a trigger fires and performs
   an action that causes another trigger of the same type
   (AFTER or INSTEAD OF) to fire. This second trigger performs
   an action that causes the original trigger to fire again.

Razvan

Razvan Socol (rso***@gmail.com) writes:
Ah! Big thanks for finding this, Razvan!

A .NET Framework error occurred during execution of user defined r
sql statement (how to)
Update Information in SQL from a VBS script
Return a City in a column on max count from another column in a group by
SQL statement increase question