sql server - Incrementing revision numbers in table's composite key -

i'm running sql server 2014 locally database deployed azure sql v12 database.

i have table stores values of extensible properties business-entity object, in case 3 tables this:

create table widgets (     widgetid bigint identity(1,1),     ... )  create table widgetproperties (     propertyid int identity(1,1),     name       nvarchar(50)     type       int -- 0 = int, 1 = string, 2 = date, etc )  create table widgetpropertyvalues (     widgetid   bigint,     propertyid int,     revision   int,     datetime   datetimeoffset(7),     value      varbinary(255)      constraint [pk_widgetpropertyvalues] primary key clustered (         [widgetid] asc,         [propertyidid] asc,         [revision] asc     ) )  alter table dbo.widgetpropertyvalues check add constraint fk_widgetpropertyvalues_widgetproperties foreign key( propertyid ) references dbo.widgetproperties ( propertyid )  alter table dbo.widgetpropertyvalues check add  constraint fk_widgetpropertyvalues_widgets foreign key( widgetid ) references dbo.widgets ( widgetid ) 

so see how widgetid, propertyid, revision composite key , table stores entire history of values (the current values obtained getting rows biggest revision number each widgetid + propertyid.

i want know how can set-up revision column increment 1 each widgetid + propertyid. want data this:

widgetid, propertyid, revision, datetime, value ------------------------------------------------        1           1         1               123        1           1         2               456        1           1         3               789        1           2         1               012 

identity wouldn't work because it's global table , same applies sequence objects.

update can think of possible solution using instead of insert trigger:

create trigger widgetpropertyvalueinserttrigger on widgetpropertyvalues     instead of insert begin     declare @maxrevision int     select @maxrevision = isnull( max( revision ), 0 ) widgetpropertyvalues widgetid = inserted.widgetid , propertyid = inserted.propertyid      insert widgetpropertyvalues values (         inserted.widgetid,         inserted.propertyid,         @maxrevision + 1,         inserted.datetime,         inserted.value,     ) end 

(for uninitiated, instead of insert triggers run instead of insert operation on table, compared normal insert-trigger runs before or after insert operation)

i think concurrency-safe because insert operations have implicit transaction, , associated triggers executed in same transaction context, should mean it's safe. unless can claim otherwise?

you code has race condition - concurrent transaction might select , insert same revision between select , insert. cause occasional (primary) key violations in concurrent environment (forcing retry entire transaction).

instead of retrying whole transaction, better strategy retry insert. put code in loop, , if key violation (and only key violation) happens, increment revision , try again.

something (writing head):

declare @maxrevision int = (     select         @maxrevision = isnull(max(revision), 0)             widgetpropertyvalues             widgetid = inserted.widgetid         , propertyid = inserted.propertyid );  while 0 = 0 begin      set @maxrevision = @maxrevision + 1;      begin try          insert widgetpropertyvalues         values (             inserted.widgetid,             inserted.propertyid,             @maxrevision,             inserted.datetime,             inserted.value,         );          break;      end try     begin catch          -- error different key violation,         -- in case pass caller.         if error_number() <> 2627             throw;          -- otherwise, key violation, , can let loop          -- enter next iteration (to retry incremented value).      end catch  end