Incremental Default Value based on Composite Key Constraint
-
How would one appropriately created an incremental default value based upon a composite key constraint? For example, if you had a table IncidentLog that contained columns such as Location,Time,Description,Action, and Transaction; and you wanted to increment an integer in column Transaction based upon the last number entered for that location.
So, say you entered/inserted the first record for Location = 'NYC' into the table, the default value to Transaction would equal 1.
Then, you entered a record for 'Chicago', transaction would equal 1.
Then, you entered another record for 'NYC', the check constraint would make the default value of transaction to 2.
Hopefully, I explained it appropriately.
"Life without progression is entropy"
Sam Jaynes -
To achieve this there are two ways (probably more)
By Creating trigger or By Creating an User Defined function and making the column as computed using the function call as formula
see this
CREATE
FUNCTION [dbo].[GetCount](@chrCity char(3))
RETURNS
int
AS
BEGIN
return(select count(*) from IncidentLog where Location=@chrCity)
END
Make Transaction column as computed column by specifying the following formula
([dbo].[TestFunction]([Location]))
hope this will solve your problem
-
sorry misspelt use
([dbo].[GetCount]([Location]))
in formula field
-
I would use max(transaction) instead of count(*) in case a row got deleted. This would result in trying to insert a duplicate key.
Otherwise, it looks good.
Steve B
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply