CODEWITHSUNDEEP
sqlsql-server-2005triggers
user278618
user278618

Reputation: 20262

Trigger to change autoincremented value

I have a simple table with tax rates

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[TaxRates](
    [Id] [bigint] IDENTITY(1,1) NOT NULL,
    [Name] [nvarchar](50) NULL,
 CONSTRAINT [PK_TaxRates] PRIMARY KEY CLUSTERED 
(
    [Id] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

If user deleted record I want to not change autoincrementer while next insert.

To have more clearance.

Now I have 3 records with id 0,1 and 2. When I delete row with Id 2 and some time later I add next tax rate I want to have records in this table like before 0,1,2. There shouldn't be chance to have a gap like 0,1,2,4,6. It must be trigger.

Could you help with that?

Upvotes: 1

Views: 92

Answers (2)

gbn
gbn

Reputation: 432451

You need to accept gaps or don't use IDENTITY

  1. id should have no external meaning
  2. You can't update IDENTITY values
  3. IDENTITY columns will always have gaps
  4. In this case you'd update the clustered Pk which will be expensive
  5. What about foreign keys? you'd need a CASCADE
  6. Contiguous numbers can be generated with ROW_NUMBER() at read time
  7. Without IDENTITY (whether you load this table or another) won't be concurrency-safe under load
  8. Trying to INSERT into a gap (by an INSTEAD OF trigger) won't be concurrency-safe under load
  9. (Edit) History tables may have the deleted values anyway

Upvotes: 1

Frank Pearson
Frank Pearson

Reputation: 872

An option, if the identity column has become something passed around in your organization is to duplicate that column into a non-identity column on the same table and you can modify those new id values at will while retaining the actual identity field.

turning identity_insert on and off can allow you to insert identity values.

Upvotes: 0

Related Questions