Insert triggers and max values

[tcronin]

I have a situation where I need to create an insert trigger on table a which will create a corresponding record in table b. However before I insert the record i must obtain the max value for the record in table b and increment it by one. I have all this working. My question is if I just put a begin and commit with this statement is there a chance that when 2 users insert at the same time the max value may be incorrect say for instance

CREATE TRIGGER tr_cms_prov_ins ON provider
FOR INSERT
as
declare @ndentPrid char(3),
@nxtgenPrid char(7),
@fname varchar(40),
@lname varchar(40)
begin tran
select @ndentPrid = max(provider_id) from providerdnt
if @ndentPrid is null
set @ndentPrid = 1
else set @ndentPrid = @ndentPrid + 1

insert into dental..provider (provider_id, first_name, last_name, collections_go_to)
select @ndentPrid, first_name, last_name','YYYYYYYYYYYYYYYNNNNN' from inserted
commit tran

Will this do it or do I need to enforce some type of locking to handle the max value. There are no inserts into
table b directly only by the trigger insert on table a

[BL]

I've done something similar in which I update a counter (which is just a single row single column table) and grab that value, basically as a home-grown IDENTITY mechanism. That proc specifies SET TRANSACTION ISOLATION LEVEL SERIALIZABLE, which means that any write access to the counter is exclusive.

I definitely don't know enough about your scenario (or perhaps just plain enough) to say this with authority, but i would guess that a serializable transaction might be too restrictive for your entire transaction, but that using MAX + 1 might not be sufficiently atomic.

Using a table as a counter requires a bit more maintenance, but I think it gives you more control.

HTH.
------------
tcronin at 11/20/00 4:50:42 PM


I have a situation where I need to create an insert trigger on table a which will create a corresponding record in table b. However before I insert the record i must obtain the max value for the record in table b and increment it by one. I have all this working. My question is if I just put a begin and commit with this statement is there a chance that when 2 users insert at the same time the max value may be incorrect say for instance

CREATE TRIGGER tr_cms_prov_ins ON provider
FOR INSERT
as
declare @ndentPrid char(3),
@nxtgenPrid char(7),
@fname varchar(40),
@lname varchar(40)
begin tran
select @ndentPrid = max(provider_id) from providerdnt
if @ndentPrid is null
set @ndentPrid = 1
else set @ndentPrid = @ndentPrid + 1

insert into dental..provider (provider_id, first_name, last_name, collections_go_to)
select @ndentPrid, first_name, last_name','YYYYYYYYYYYYYYYNNNNN' from inserted
commit tran

Will this do it or do I need to enforce some type of locking to handle the max value. There are no inserts into
table b directly only by the trigger insert on table a