July 24, 2008 at 8:59 am
Hi,
I have a trigger which is used to generate a ID for each record inserted , this trigger is part of a existing Enterprise software so i cannot have identity column, the problem is that when the client table is empty and a new client record is created a client id is not being generated but if i enter a client id manually for the first client record then the client ids for next client records gets generated automatically so i think that the if clause i have written for the cursor status is being skipped , can any one tell me what is wrong with the code ,so kindly help me with this.
The if condition is used to check if the table is empty or not or more precisely wether a client id exists for an existing record.
July 24, 2008 at 12:00 pm
please post the code so people can take a look and offer suggestions.
thanks
July 24, 2008 at 2:54 pm
Please don't double-post in multiple forums.
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
July 24, 2008 at 11:53 pm
CREATE TRIGGER [dbo].[clientActiveID] ON [dbo].[Client]
For Insert As
Begin
Declare @clientrefid nchar(20)
Declare @idno int
Declare curclientRefID SCROLL cursor
For Select client_activeid from client Order By client_activeid
Open curclientRefId
Fetch Last from curclientRefID into @clientrefid
Print @clientrefid
print 'ok'
If(@@fetch_status=0)
Begin
Print 'ok1'
Declare @refid int
Set @refid=Cast(Substring(@clientrefid,4,7) as int)
print @refid
Set @refid=@refid+1
Declare @reftext varchar(20)
Set @reftext = Cast(@refid as varchar(20))
print @reftext
Set @reftext =Replicate('0',7-Len(@reftext))+@reftext
print @reftext
print @clientrefid
Set @clientrefid=Stuff(@clientrefid,4,7, @reftext)
print @clientrefid
Update client
Set client_activeid=@clientrefid where client_activeid is null
End
Else
--if(@@fetch_status=-2)
Begin
print @@fetch_status
Declare @slno int
Set @slno=1
Declare @sltext varchar(20)
Set @sltext =Cast(@slno as varchar (20))
Set @sltext =Replicate('0',7-len(@sltext))+@sltext
Set @clientrefid ='PW'+'-'+@sltext
Update client
Set client_activeid=@clientrefid where client_activeid is null
End
Close curclientRefID
Deallocate curclientRefID
END
July 24, 2008 at 11:54 pm
CREATE TRIGGER [dbo].[clientActiveID] ON [dbo].[Client]
For Insert As
Begin
Declare @clientrefid nchar(20)
Declare @idno int
Declare curclientRefID SCROLL cursor
For Select client_activeid from client Order By client_activeid
Open curclientRefId
Fetch Last from curclientRefID into @clientrefid
Print @clientrefid
print 'ok'
If(@@fetch_status=0)
Begin
Print 'ok1'
Declare @refid int
Set @refid=Cast(Substring(@clientrefid,4,7) as int)
print @refid
Set @refid=@refid+1
Declare @reftext varchar(20)
Set @reftext = Cast(@refid as varchar(20))
print @reftext
Set @reftext =Replicate('0',7-Len(@reftext))+@reftext
print @reftext
print @clientrefid
Set @clientrefid=Stuff(@clientrefid,4,7, @reftext)
print @clientrefid
Update client
Set client_activeid=@clientrefid where client_activeid is null
End
Else
--if(@@fetch_status=-2)
Begin
print @@fetch_status
Declare @slno int
Set @slno=1
Declare @sltext varchar(20)
Set @sltext =Cast(@slno as varchar (20))
Set @sltext =Replicate('0',7-len(@sltext))+@sltext
Set @clientrefid ='PW'+'-'+@sltext
Update client
Set client_activeid=@clientrefid where client_activeid is null
End
Close curclientRefID
Deallocate curclientRefID
END
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply