We are in the process of creating a new application where various tables are used to hold enumerations. Since the IDs in these tables will be used in bitwise comparisons, we'd like to ensure new rows inserted will automatically use the decimal number that corresponds to the next bit being "set".
For instance, if we had an alarm clock application, we might include a "Weekdays" table which could be used in the following way:
CREATE TABLE dbo.Weekdays ( WeekdayID INT NOT NULL CONSTRAINT PK_Weekdays PRIMARY KEY CLUSTERED , WeekdayName VARCHAR(20) ); INSERT INTO dbo.Weekdays VALUES (1, 'Sunday') , (2, 'Monday') , (4, 'Tuesday') , (8, 'Wednesday') , (16, 'Thursday') , (32, 'Friday') , (64, 'Saturday'); CREATE TABLE dbo.AlarmsDefined ( AlarmID INT NOT NULL CONSTRAINT PK_AlarmsDefined PRIMARY KEY CLUSTERED , Weekdays INT NOT NULL ); INSERT INTO dbo.AlarmsDefined (AlarmID, Weekdays) VALUES (1, 50); SELECT W.WeekdayName FROM dbo.AlarmsDefined AD INNER JOIN dbo.Weekdays W ON (AD.Weekdays & W.WeekdayID) = W.WeekdayID
The results of the above
I'd like to be able to define the Weekdays table in the above example such that if we added another row to the table, the next
WeekdayID would be automatically set as 128, which is the next decimal number that can be used in bitwise comparisons.
This of course can be accomplished by putting in the values by hand into the primary key column.
But is there a way to specify an automatically incrementing value which would adhere to the flag values? For example these would be the unique PK values generated:
ID -- 1 2 4 8 16 32 ...
and the trigger would automatically look at the last value and generate the next in the sequence needed?