October 17, 2008 at 2:47 am
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.
insert into clientcode(cc_clientcode,cc_rmcode,cc_productname,cc_panno)
values(13213,20003,'Equity','AAGPW6964M')
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.
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 intoClientCodeError
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
October 17, 2008 at 3:07 am
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.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply