Creating a counter using a trigger in SQL Server 6.5

[tjohnsb]

The following code displays a trigger that I have created to generate an id number that always has to display as five characters. The field type is char but the id number format has to display as a character and 4 integers, for example: T0001.&nbsp;&nbsp;The problem is that the code will work if I increment the number using T1000 it will successfully increment to T1001.&nbsp;&nbsp;If I try incrementing from T0000 than the next number displays as T2. Any suggestions for code that would be able to populate the space after the the first character with leading zeros.&nbsp;&nbsp;The code is below: <br><br>CREATE TRIGGER doctoridITrig ON tjohnso.tstvalid_doc FOR INSERT AS<br>DECLARE @maxss char(5), @maxsd int, @chrDOC char(5)&nbsp;&nbsp;&nbsp;/* FOR COUNTER-EMULATION CODE */<br><br><br>/*<br>&nbsp;* Assign doctor id number<br>&nbsp;*/<br><br>IF (SELECT Count(*) FROM inserted WHERE doctorid IS NULL) &gt; 0<br> BEGIN<br> SELECT @chrDOC = (SELECT Max(doctorid) FROM tjohnso.tstvalid_doc WHERE <br> doctorid Like '%T%')<br>/*<br>The following code seperates the letter 'T' from the four numbers and places the numbers into a variable so that the id number can be incremented by 1<br>&nbsp;*/<br> IF @chrDOC IS NULL SELECT @maxsd = convert(int,(substring(@chrDOC, 2,4)))<br> ELSE <br> BEGIN<br> SELECT @chrDOC = SUBSTRING(@chrDOC, 2,4)<br> SELECT @maxsd = CONVERT(int, @chrDOC)<br> END<br> SELECT @maxsd = @maxsd + 1<br> SELECT @chrDOC = CONVERT(char(5), @maxsd ) <br> <br> UPDATE tjohnso.tstvalid_doc SET doctorid = 'T' + @chrDOC WHERE doctorid IS NULL<br> END<br><br><br><br>GO<br><br>Any help or ideas would be greatly appreciated.&nbsp;&nbsp;Thank you.

 

[stan123]

Hopefully following code might help:<br><br>declare @nvalue char(4),<br> @id char(5),<br> @number int<br><br>--for example its 'T0022'<br>select @id = 'T0022' <br><br>select @nvalue = substring(@id,2,4)<br><br>select @number = convert(int,@nvalue)<br><br>select @number = @number + 1<br><br>select @nvalue = convert(char(4),@number)<br><br><br>select substring(@id,1,1) + replicate('0',4 - len(@nvalue)) + @nvalue

 

[LuvASP]

SELECT @maxsd = CONVERT(int, @chrDOC)<br><br>I think this is where the problem is, the datatype INT is what is converting it from 000 to 2 when you add. Try to use numeric.

 

[tjohnsb]

Thank you LuvASP and Stan123 for your suggestions.&nbsp;&nbsp;I finally created the following code for my counter:<br><br>CREATE TRIGGER doctoridITrig ON tjohnso.tstvalid_doc FOR INSERT AS<br>DECLARE @maxss char(4), @maxsd int, @chrDOC char(5), @chrDOC1 char(5)&nbsp;&nbsp;&nbsp;/* FOR COUNTER-EMULATION CODE */<br><br><br>/*<br>&nbsp;* Assign doctor id number<br>&nbsp;*/<br><br>IF (SELECT count(*) FROM inserted WHERE doctorid IS NULL) &gt; 0<br> BEGIN<br> <br><br> <br> SELECT @chrDOC = (SELECT Max(doctorid) FROM tjohnso.tstvalid_doc)<br><br> <br> IF @chrDOC IS NULL SELECT @maxsd = convert(int,(substring(@chrDOC, 2,4)))<br> ELSE <br> BEGIN<br> select @chrDOC1 = @chrDOC<br> SELECT @chrDOC = SUBSTRING(@chrDOC, 2,4)<br> sELECT @maxsd = CONVERT(int, @chrDOC)<br> END<br> SELECT @maxsd = @maxsd + 1<br> select @maxss = convert(char(4),@maxsd)<br> <br> UPDATE tjohnso.tstvalid_doc SET doctorid = <br><br>case<br>when len(ltrim(rtrim(@maxss))) = 1 then (ltrim(rtrim(substring(@chrDOC1,1,1)))) + '000' + @maxss<br>when len(ltrim(rtrim(@maxss))) = 2 then (ltrim(rtrim(substring(@chrDOC1,1,1)))) + '00' + @maxss<br>when len(ltrim(rtrim(@maxss))) = 3 then (ltrim(rtrim(substring(@chrDOC1,1,1)))) + '0' + @maxss<br>else @maxss<br>end<br><br>WHERE doctorid IS NULL<br>END<br><br><br>Now all I have to do is get the code to change to another letter once the first 9999 nubers have been used for the current letter.