August 18, 2008 at 6:01 am
HI,
I have to generate a ID for every record in the format PW0000001,PW0000002... and so on, for which I have a trigger which uses cursors to check a previous record and generate a next ID but if this is the first record then it will create the whole format for the ID.My problem is that if its a new record i am not getting the ID but if we already have a record with ID say PW0000001 then the next ID is getting generated. So I think the problem is with the iF-Else condition can someone please help me with this.Th field which holds the ID is client_activeid, and the table is Client.
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
Declare @refid int
Set @refid=Cast(Substring(@clientrefid,3,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,3,7, @reftext)
print @clientrefid
Update client
Set client_activeid=@clientrefid where client_deleted is null and client_activeid is null
End
Else
Begin
Declare @slno1 int
Set @slno1=1
Print @slno1
Declare @sltext1 varchar(20)
Set @sltext1 =Cast(@slno1 as varchar (20))
Print @sltext1
Set @sltext1 =Replicate('0',7-len(@sltext1))+@sltext1
Print @sltext1
Set @clientrefid ='PW'+@sltext1
Print @sltext1
Update client
Set client_activeid=@clientrefid where client_deleted is null and client_activeid is null
End
Close curclientRefId
Deallocate curclientRefId
End
August 18, 2008 at 6:14 am
Would I be correct in assuming each client has a different first two digits?
Unless I've missed something there is a much more economical way of doing this and I would heartily recommend avoiding cursors where possible, especially on triggers that could potentially have a high run frequency.
I'll reply once I know about the client digit question.
Adam
August 18, 2008 at 6:16 am
See this is just a part of the whole requirement I am just splitting it in parts and testing it. I will have field called type so based on the type only I need to generate the code.So I don't want to use identity column . I just want this code to work , thanks
August 18, 2008 at 6:26 am
See the Id should actually be check digit number. I still have to write code for that, I am testing this code because i need to use this format in the second version I will write a code based on some algorithm and the ID need not be PW0000001 and so on, it could be similiar to this but with check digit, and I have to generate password which i have already written but i have removed from the sample code. I know identity column but i want to use this code only, so please..... help me with this code
August 18, 2008 at 6:30 am
You haven't got a where clause on your cursor definition, so no matter what is inserted into the table, the first part of the IF will run and the second part will never execute.
Is this the issue?
August 18, 2008 at 6:36 am
where and why do i require the where clause. The cursor has
For Select client_activeid from client Order By client_activeid
Open curclientRefId
Fetch Last from curclientRefId into @clientrefid
all the rows have to be in an order so that i can get the previous ID , so I have the last row from result set. But any ways if you think any thing is wrong please tell me where what needs to be changed
August 18, 2008 at 7:21 am
Ok, this is an interesting approach and a good attempt, but it is not a good way to do this.
From the code, the ID is always 'PW' followed by a padded integer value that is always incrementing. The 'PW' never changes and the integer value does not reset.
The best approach for this in SQL Server is to use an identity column in the table. Then, add a persisted computed column that concatenates the string together with the identity value. Anything else will be slower. Your particular approach will tend to cause major lock conflicts when you have multiple users because your inserts cause a lot of reads against the same table. It is just not a scalable solution.
Now, if the 'PW' part of the code varies and the incrementing value needs to start at 1 for all of the possible values, you should use an additional table that contains the "next" value to use and then use a default constraint on this table with a function that gets the next value and increments it in the other table. This is similar to the Oracle approach. It will mean inserts have to wait for each other (as they should) but will not be locked by other activity in your table.
August 18, 2008 at 7:30 am
Thanks for the reply I might use that method in fact I have been told before to use the method you have advised,but I just want to know what is wrong in this code and why is it not working
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply