Custom Increment Based on Another Column''s Max Value
-
I am struggling with trying to come up with the best way (default preferred or trigger) to increment a column's value in a custom way.
Scenario:
A given log's document number starts incrementing from 1 and MUST be consecutively incremented based on the last document number assigned for the given log.
DDL:
CREATE TABLE Document(
DocumentNumber int NOT NULL,
Log int NOT NULL,
CONSTRAINT PKConstraint PRIMARY KEY CLUSTERED (DocumentNumber, Log),
CONSTRAINT RefLog FOREIGN KEY (Log)
REFERENCES Log(Log),
)
GO
SampleData:
DocumentNumber Log
1 1
2 1
3 1
4 1
1 2
2 2
The document number needs to be incremented within each log.
The PKConstraint will only enforce uniqueness within each log, it will NOT enforce incrementing from the last document number for a given log.
How can I create a default to poplulate this document number automatically?
Something like the following is desired:
SELECT MAX (DocumentNumber) + 1 FROM Document WHERE Document.Log = @InputLogValue
Thanks in advance for assistance given!
-
Don't you have a DocumentId somewhere in the db?? that would be much simpler to implement, not to mention much more safe.
-
The requirement was for the Document Number (aka DocumentId) to be incremented separately for each log rather than have a unique id (e.g. identity) for every document regardless of which log it appears on. This is like a sequence number for order details within a given order.
Any thoughts are appreciated!
-
You cannot maintain this value thru a default.
If the value is used for sorting, then why must the item be sequential?
Thats fine if its a requirement, but requirements should tell you what to do not how to do it. So if you are required to maintain a value so it will sort properly then sequential numbers should not matter.
If you can get out of the sequential requirement, then use identity in this table
SampleData:
DocumentNumber Log Identity
1 1 1
2 1 2
3 1 3
4 1 4
1 2 5
2 2 6
Order by Identity and your Document log entries will be correct.
Otherwise maybe Create a table that stores the Document number and sequential #
on insert you have to retreive the next number, increment the number and use the value for the insert.
If new DocumentNumber Created, you have to populate thes sequential table also.
Trigger will work too, but could unnecessarily hold transactions open longer if the table starts to get large.
Pain, total pain.
-
You'll probably want to encapsulate the code in a transaction and have it work as a trigger or, better yet, encapsulate such logic in the stored procedure that does the document insert.
To reliably get the max(documentID) for a given log, you will need to perform a select on the table using
with (holdlock)
after the table name in the select statement. You will also need to make it an exclusive lock - you could do this by further modifying your select statement or by setting your transaction isolation level appropriately (serializable is the highest level).
This will ensure that your transaction keeps the relevant rows used to determine the maximum locked for the duration of your insert - it will slow things down a LOT if you are doing lots of simultaneous inserts of documents as each will be performed serially to ensure the maximum value is reliably calculated. Look in SQL Server books online for the appropriate commands for locking, transaction isolation levels and the "with" command - I can't remember them off the top of my head at the moment
Hope it helps you though... But as others before said, you are probably better off trying to avoid such tricky schemes and rely on an identity if you need an incrementing number. Do you create a new log every day? If so, you could do something odd like have a table called "log" that does nothing else other than let you insert a blank row - it will have a single column of type identity(1,1). Then, in your document proc, you insert a single row into Log, get the identity value and use it to properly do the insert for documentID in your document-log table.
At the end of the day (or end of the log period) you just truncate that log table and ensure its identity is reseeded back to 1.
This assumes that only one log is active at a time.
Cheers,
Ian
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply