Image of Navigational Panel mapped to Contents / Home / Search Creating a Counter Field with NT SQL
Tech Support Question
Image of Line Break

Q. I am having trouble getting a "Record Counter" going. The problem is that I want my primary keys to have unique values. In Access I can create a field as a Counter type, but with SQL Server you don't have that luxury. How does one deal with the issue using NT SQL Server (or any other non-Access platform)?

Email: NICHOLSD@major.cei.net

A. To do a record counter with Access, just make the field type a counter field. If you are using SQL Server, or some other server based database you should use an Insert Trigger. Here's the one I use. Just replace the words [TriggerName], [TableName], and [PrimaryKey] with the appropriate values. Whenever you add an item into the table, specify zero (0) as the primary key value, and the trigger will take care of the rest. 

    CREATE TRIGGER [TriggerName] ON [TableName] FOR INSERT AS

    DECLARE @maximum_count 	int
    DECLARE @new_count 	int

    SELECT @maximum_count = (SELECT MAX([PrimaryKey]) from [TableName])
    SELECT @new_count = (SELECT [PrimaryKey] from inserted)

    IF @new_count = 0 OR @new_count  @maximum_count
    SELECT @maximum_count = @maximum_count + 1

    UPDATE [TableName] 
    SET [PrimaryKey] = @maximum_count 
    WHERE [PrimaryKey] = @new_count
Image of Arrow to Previous Article Image of Arrow to Next Article

[TECH SUPPORT TOC]
Image of Line Break
[HOME] [TABLE OF CONTENTS] [SEARCH]