SQL Server Performance Forum – Threads Archive

Trigger to Generate Specific Primary Key

Hello. I am a developer, but we do not have a DBA, so I also have to handle the DB side of things, too. Therefore, I need some guidance, as much as possible, to implement a requirement. We are using MS SQL 2000 7.0. We have a table, we will say TableA, that has a primary key â€“ let#%92s call it ID – that is made up of two parts. 1) Today#%92s date (e.g. 01/20/2004), and 2) a counter variable (e.g. 0001). E.g. 012020040001 I want to make a trigger so when I pass the query to the database on insert into the table, the ID is generated and inserted at the same time. Here is the last catch, each time the day changes I need the counter part of the ID to start back from zero. How do I do it? Remember, clarity is a great asset.

Are you using sql 2000? or sql 7? or sql 2000 with 70 compatbility level? There are probably as many ways of doing what you want as there are dbas out there. One way would be to keep a table with the current days highest id and increment and get if from there in your trigger. Have a job that sets this value to zero at midnight each day. Like I said, there are other ways, but this would be fast and easy. Chris

Here is a way to do it without a trigger.
Create a table where you hold the current date, id (and last modified date if you need it). CREATE TABLE my_seq
(
my_date smalldatetime,
my_id int,
last_modified datetime
)
The stored procedure bellow will get you the next id, assuming there is 1 row in the table. You could handle this case as well (if there are no rows – insert one) but I was trying to avoid openning a transactions.
create procedure get_next_id
(
@today datetime,
@my_PK varchar(30) output
)
AS
set nocount on declare @new_id int update my_seq set
my_date =
case
— different day from today, updating column
when datediff(dd, my_date, @today) >= 1 then dateadd(dd, 0, datediff(dd, 0, @today)) –(zero the timepart)
— same day – column remains the same
else my_date
end,
@new_id = my_id =
case
— different day – zero the counter
when datediff(dd, my_date, @today) >= 1 then 1
— same day – incrementing the counter
else my_id + 1
end,
last_modified = @today — this will construct the PK according to the date and id above
select @my_PK = replace(convert(varchar(10), @today, 101),’/’,”) + right(replicate(‘0’,4) + convert(varchar,(@new_id)),4)
Here is a call to the procedure
declare @next_PK varchar(20), @today datetime
select @today = getdate()
exec get_next_id @today, @next_PK output
select @next_PK
Run it a few times, then run an update on the date update my_seq set my_date = dateadd(dd, -1, my_date) And see how the counter is reset to 1. Bambola.

]]>