If condition in trigger is not validating

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

If condition in trigger is not validating

Author

Topic

nithin.gujjar
Starting Member

19 Posts

Posted - 2008-08-18 : 08:03:33

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

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 08:11:05

Why do you want a trigger for this. Cant you use a temporary table with an identity column to insert all records at first and then append your format value to id generated from it?

nithin.gujjar
Starting Member

19 Posts

Posted - 2008-08-18 : 08:15:07

quote:
Originally posted by visakh16

Why do you want a trigger for this. Cant you use a temporary table with an identity column to insert all records at first and then append your format value to id generated from it?

See this is just a part of the whole requirment 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 dont want to use identity column . I just want this code to work , thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-08-18 : 08:18:01

quote:
Originally posted by nithin.gujjar

quote:
Originally posted by visakh16

Why do you want a trigger for this. Cant you use a temporary table with an identity column to insert all records at first and then append your format value to id generated from it?

See this is just a part of the whole requirment 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 dont want to use identity column . I just want this code to work , thanks

Even if you want to generate it based on type you can do it by identity column method. Explain how your type values will be and we can see if it can be done with identity column method.

nithin.gujjar
Starting Member

19 Posts

Posted - 2008-08-18 : 08:25:36

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

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2008-08-18 : 08:41:35

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57069

Madhivanan

Failing to plan is Planning to fail

nithin.gujjar
Starting Member

19 Posts

Posted - 2008-08-18 : 08:53:16

quote:
Originally posted by madhivanan

http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=57069

Madhivanan

Failing to plan is Planning to fail

Thanks for your reply, its a different way of doing it, but i want this code to work, so i request you to help me make this code work.I want to know what is wrong in this code and i have spent a lot of time on this .

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources