-
Insert triggers and max values
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
-
Insert triggers and max values (reply)
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
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
|