none
Infinite loop in trigger! RRS feed

  • Question

  • I have a insert trigger which validates the inserted data and passes the errors to text file.The problem is that trigger works fine for good data but if there is some wrong data than the error is not being captured by the code instead goes into infinite loop I used the following code for testing.first time I insert it then it works but if i insert it again it is supposed to show an error in text file instead it gets into infinite loop.

    The actual insert is done using SSIS package on the table.

    [code]
    insert into clientcode(cc_clientcode,cc_rmcode,cc_productname,cc_panno)
    values(13213,20003,'Equity','AAGPW6964M')
    [/code]

    If I pass some wrong value to any of the fields than it gets into infinite loop instead of passing the error into text file.


    [code]
    USE [Religare]
    GO
    /****** Object:  Trigger [dbo].[clientpanno]    Script Date: 10/17/2008 12:00:13 ******/
    SET ANSI_NULLS ON
    GO
    SET QUOTED_IDENTIFIER ON
    GO

    ALTER trigger [dbo].[clientpanno] on [dbo].[ClientCode] for insert
    As
    Begin
        Delete from sql_identity where id_tableid=10183
        Declare @panno as nvarchar(10),@panno1 as nvarchar(10)
        Declare @rmcode as nvarchar(10),@rmcode1 as nvarchar(10),@clientcode as nvarchar(10),@top as nvarchar(10)
        Declare @activeid as varchar(10)
        Declare @ccid as int,@count as int
        /*Create Table ClientCodeError
            (SerialNo int Identity(1,1),
            Remark     nchar(50) not null,
            ClientCode nchar(20) Not Null,
            RMCode     nchar(20) not null,
            PANNo       nchar(20) not null,
            Product    nchar(20) not null)*/
        Declare @InvalidRMCodematch as nvarchar(50),@InvalidRMCode as nvarchar(50),@InvalidPANNo as nvarchar(50),@DuplicateClientCode as nvarchar(50)
        Set @InvalidRMCodematch='Clientcode mapped to wrong RMCode'
        Set @InvalidRMCode='RMCode does not exist in the CRM'
        Set @InvalidPANNo='PAN No. does not exist in the CRM'
        Set @DuplicateClientCode='This Client Code already exists'
        Select @ccid=cc_clientcodeid from clientcode order by cc_clientcodeid
        Declare curClientCode SCROLL cursor
        For Select cc_panno,cc_rmcode from inserted where cc_activeid is null Order By cc_clientcodeid
        Open curClientCode
        Fetch curClientCode into @panno,@rmcode
        While(@@fetch_status=0)
        Begin
            Select @activeid=client_activeid from client where client_panno=@panno
            Select @rmcode1=user_empcode from users where user_empcode=@rmcode
            Select @panno1=client_panno from client
                where client_userid=(Select user_userid from users where user_empcode=@rmcode)
            select @count=count(cc_clientcode) from clientcode
                where cc_panno=@panno
            select @top=cc_clientcodeid from clientcode
                where cc_panno=@panno order by cc_clientcodeid desc
            if(@activeid is null)
            Begin
                Print 'The PAN No does not exist'
                Insert into    ClientCodeError
                Select @InvalidPANNo,cc_clientcode,cc_rmcode,cc_panno,cc_productname
                from clientcode where cc_panno=@panno
                delete from clientcode where cc_panno=@panno       
            End
            Else if(@rmcode1 is null)
            Begin
                Print 'The RMCode does not exist in CRM'
                Insert into ClientCodeError
                Select @InvalidRMCode,cc_clientcode,cc_rmcode,cc_panno,cc_productname
                from clientcode where cc_panno=@panno
                delete from clientcode where cc_panno=@panno
            End
            Else if(@panno1 != @panno)
            Begin
                Print 'Clientcode mapped to wrong RMCode'
                Insert into ClientCodeError
                Select @InvalidRMCodematch,cc_clientcode,cc_rmcode,cc_panno,cc_productname
                from clientcode where cc_panno=@panno
                delete from clientcode where cc_panno=@panno
            End
            Else if(@count>1)
            Begin
                Print 'This Client Code already exists'
                Insert into ClientCodeError
                Select @DuplicateClientCode,cc_clientcode,cc_rmcode,cc_panno,cc_productname
                from clientcode where cc_panno=@panno
                delete from clientcode where cc_clientcodeid=@top
            End
            Else
            Begin           
                Update clientcode set cc_activeid=@activeid where cc_panno=@panno
            End
            Fetch curClientCode into @panno,@rmcode
            Print 'The Pan No. is '+@panno
            Print 'The RMCode is '+@rmcode   
        End
        Close curClientCode
        Deallocate curClientCode
        Select @ccid=cc_clientcodeid from clientcode order by cc_clientcodeid
        Set @ccid=@ccid+1
        Insert sql_identity (id_tableid,id_nextid)values(10183,@ccid)
        Declare @bcpCommand varchar(100)
        SET @bcpCommand='bcp "SELECT * FROM Religare..ClientCodeError" queryout "c:\ClientCodeError.txt" -U sa -P SA1 -c'
        EXEC master..xp_cmdshell @bcpCommand
        Delete from ClientCodeError

    End


    [/code]
    Friday, October 17, 2008 8:47 AM

All replies

  • I am using Cusror because I am using SSIS package to insert records into the table.Since the SSIS uses bulk insert I can cannot validate individual record .So the cursor holds the result set and code goes through every record individually and that way every record can be validated.
    Friday, October 17, 2008 9:07 AM