I want a – can I call it a sub-sequence ? – to generate identity for child lines. A sequence per parent entity, starting at 1 with no holes. That is, each parent's childrens Ids should start at 1 and increment. What is a simple and reliable solution?
Detail, if it helps
- My application inserts rows into a child table for order LineItems.
- The application requires LineItems be numbered consecutively from 1
- The LineItems table itself also has child tables, in this case Variation.
- After insert, I return the inserted row with the new sequential LineItemId
- The app doesn't allow deletions
Current code (the @ symbols marks input parameters):
Insert Into Lineitems (Jobid,Id,Stage, Costprice, Sellprice, Customdescription) Values (@JobId, -- *Calculate next line id for this jobid* @Stage,@Costprice,@Sellprice,@Customdescription) ; Insert Into Variations (Lineitemjobid,Lineitemid,InstructedBy,InstructionDate,Type) Values (@jobId, *Use the just-inserted line id for this jobid* @Instructedby, @Instructiondate, @Variation_Type) ; Select * from ...etc... Where (JobId,Id)=(@jobId, *Use the just-inserted line id for this jobid*)
The places marked
*Use...* are where I want my 1-based, no-holes, per-parent, subsequence.
My First Effort
I create a view to get currentval/next val
Create View Lineitemidseq(Jobid, Currentval, Nextval) As Select Jobs.Id As Jobid, Coalesce(Max(L.Id), 0) As Currentval, 1 + Coalesce(Max(L.Id), 0) As Nextval From Jobs Left Join Lineitems L On Jobs.Id = L.Jobid Group By Jobs.Id
And then I can fill in my calculations for
next lineid &
(Select Nextval from Lineitemidseq Where JobId=@jobId)
(Select Currentval from Lineitemidseq Where JobId=@jobId)
You can see the flaw, albeit at low probability. Because I have a child of the child, if 2 people add lines simultaneously, I could pick up a wrong CurrentVal in statements 2 & 3.
- Presumably I could fix this with an explicit lock for the 3-line transaction. Is there a better way?
- If not, what is a reasonably minimalist lock that would provide correctness for such a small transaction?