December 27, 2004 at 6:21 pm
Hi, Iam converting an old in-house Indexed flat file system to .net & SQL. Ive got a lot of it converted, but I need help for the best way to implement the "Next Quote Number". In the old system when the Quote is ready to be saved the system reads with lock the "master record" that has the Julian Date in it & the next available Quote number, if the Julian Date is not equal to today then it put todays Julian Date in it & set the next Quote number to 2, updates the Master record & uses 1 for the Quote number, if the Julian Date is the same as today it uses the Quote number. adds 1 to it updates the Master record. What is the best way to do this with SQL?
Thanks for any help!
Charlie
December 28, 2004 at 12:39 am
Most developers would use a stored procedure that your application would call to generate the next number.
Since I'm more of a DBA than a developer, I would use an identity primary key and a trigger on the quote table:
create trigger trigger_name on quote_table for insert as
-- this is so the trigger works only when one record is entered at time
if @@rowcount = 1
begin
declare @quote int, @today smalldatetime
select @today = convert(varchar(10),getdate(),101)
-- use a transaction to insure two users do not get the same quote number
begin tran
-- get the next quote number
select @quote = max(quote)+1
from master_table
where date = @today
-- if there is no quote number, it is a new day
if @quote is null
begin
select @quote = 1
insert into master_table (date, quote)
values (@today, @quote)
end
-- if there was a quote number, increment the quote number for the date
else
begin
update master_table
set quote = @quote
where date = @today
end
commit
-- put the quote number in the quote table (id field is a primary key)
update q
set quote = @quote
from quote_table q
join inserted i
on i.id = q.id
end
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply