How do I automatically generate a key? 1

[Zid]

Please I need some help, I am using SQL Server 7 and need to make a trigger that automatically updates a table with a key when rows are inserted or updated. The key has to be in the form 'XYY00001', where 'X' is a character, 'YY' is the last to digits of the current year and '00001' is a serial number.

 

[tlbroadbent]

Is the "key" field the primary key of the table being upodated or inserted? If so you'll not be able to insert it via a trigger.

If it is not a primary key, then you should be able to handle insertion/update with a triger. However, I need more information before providing an answer. Are you updating the same table with the trigger or another table. What is the source of the character "X" and the serial number? Are these fields on the updated or inserted record? What should happen if the field already contains a value? Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Zid]

The 'key' is the primary key which is in the same table, what I really need is something like an IDENTITY, but with the character 'X' and the last to digits of the year added on the front. Is there another way to update the field automatically like an IDENTITY would do.

 

[Zid]

I basically ment to say how do I create an auto-increment field, but with a few characters appended to the front?

 

[tlbroadbent]

I suggest creating a table with two columns and one row to track the "serial number".

Create Table SerialNo (RecID int, SerNo int)
/* initialize the table */
Insert SerialNo values(1,0)

Then create a stored procedure to insert the new record in the table. Call the stored procedure and pass variablkes to be inserted into the table.

Create proc spInsertRec @var1 varchar(10), @var2 int, ... As

Declare @key char(8)
declare @serno int

/* Update the serial number table and return the new value in @serno variable */
Update SerialNo Set @serno=serno=serno+1
Where RecID=1

/* Create the key */
Select @key = 'X' +
right(convert(char(4), year(getdate())),2) +
replace(str(@serno,5),' ','0')

/* Insert record in the table. Assume all values are passed to SP except the key. */
Insert tbl values (@key, @var1, @var2, ....)

Hope this makes sense to you. Please ask questions if something isn't clear or doesn't work. Terry Broadbent

"The greatest obstacle to discovery is not ignorance -- it is the illusion of knowledge." - Daniel J Boorstin

 

[Zid]

Thanks Terry, looks great