Instead of trigger to update primary key

Question

Hi!

Long time reader, first time poster ;-)

I'm implementing a system based on an old system. The new system uses SQL Server 2008 and my problem comes when trying to insert new items in the main table. This will happen in two ways: It may be imported from the existing system (1) or may be created in the new system (2).

In case (1) the item already has an ID (int) which I would like to keep. In case (2) the ID will not be filled in and I'd like to generate an ID which is +1 of the maximum current value in the table. This should of course also work for inserts of mutiple rows.

As far as I can see, the solution will be to create a INSTEAD OF TRIGGER, but I can't quite figure out how this is done. Can anyone give me a hint or point me in the direction of how this can be done?

Chris

Answer 1

Undeleted

Messing around with identity columns and setting IDENTITY_INSERT on and off dependant upon whether you are inserting a new or old item doesn't seem that great both from a permissions and a concurrency POV. I suggest an approach and hopefully others will identify improvements

Create a single row, single column table to track your own Ids

--Table to hold single record with value of highest allocated "new" id
CREATE TABLE [dbo].[YourTableMaxId](
    [Lock] char(1) DEFAULT 'X' NOT NULL PRIMARY KEY CHECK (lock = 'X'),
    [MaxId] [int] NOT NULL
)

--Initially populated with some high value
insert into [dbo].[YourTableMaxId](MaxId) VALUES (1000000)

The INSTEAD OF trigger (untested but to show an approach)

CREATE TRIGGER [dbo].[tr_InsteadOfInsert] 
   ON  [dbo].[YourTable] 
   INSTEAD OF INSERT
AS 
BEGIN
    SET NOCOUNT ON;


INSERT INTO [dbo].[YourTable]
           ([Id]
          ,... Other Columns)
SELECT [Id]
      ,... Other Columns
  FROM INSERTED WHERE ([Id] IS NOT NULL)

DECLARE @NumberOfNewRecords INT

SELECT @NumberOfNewRecords = COUNT(*) FROM INSERTED
WHERE ([Id] IS NULL)

    IF (@NumberOfNewRecords > 0)
    BEGIN
        DECLARE @MaxId TABLE (MaxId INT)

        --Atomic Reservation of Sequence Numbers and output of start of sequence
        UPDATE [dbo].[YourTableMaxId]
        SET [MaxId] = [MaxId] + @NumberOfNewRecords
        OUTPUT DELETED.MaxId INTO @MaxId

        INSERT INTO [dbo].[YourTable]
           ([Id]
          ,... Other Columns)
        SELECT ROW_NUMBER() OVER (ORDER BY [Id]) + (SELECT MaxId FROM @MaxId) AS Id ,... Other Columns
        FROM INSERTED WHERE ([Id] IS NULL)
    END

END

Answer 2

How about using a stored procedure to do your inserts, with the primary key as an optional parameter. In the stored procedure, you can set the primary key when it is not passed.

I would caution that if old and new records are being inserted mix and match, your scenario will probably fail, as new records will be getting old ID's before the old records are inserted. I recommend getting the max ID of the old table right now, and in the stored procedure setting the new primary key to be the Greater value of (old max + 1, current table max)

Answer 3

Following your request of using an INSTEAD OF trigger this SQL code can get you started.

CREATE TABLE dbo.SampleTable
(
    ID INT,
    SomeOtherValue VARCHAR(100) NULL
)
GO

CREATE TRIGGER dbo.TR_SampleTable_Insert
   ON  dbo.SampleTable
   INSTEAD OF INSERT
AS 
BEGIN

    SET NOCOUNT ON;

    -- Inserting rows with IDs
    INSERT INTO dbo.SampleTable (
        ID, 
        SomeOtherValue)
    SELECT 
        ID, 
        SomeOtherValue
    FROM
        Inserted    
    WHERE
        ID IS NOT NULL

    -- Now inserting rows without IDs
    INSERT INTO dbo.SampleTable (
        ID, 
        SomeOtherValue)
    SELECT 
        (SELECT ISNULL(MAX(ID), 0) FROM dbo.SampleTable) 
            + ROW_NUMBER() OVER(ORDER BY ID DESC),
        SomeOtherValue
    FROM
        Inserted
    WHERE
        ID IS NULL

END
GO

INSERT INTO dbo.SampleTable
SELECT 1, 'First record with id'
UNION
SELECT NULL, 'First record without id'
UNION
SELECT 2, 'Second record with id'
UNION
SELECT NULL, 'Second record without id'
GO

SELECT * FROM dbo.SampleTable
GO

Answer 4

Others have shown you how to write such trigger.

Another, and often recommended, approach is to store both IDs in new database. Each record gets new ID in new system (by IDENTITY column or some other means). Additionally, if the record is imported from another system, it has associated OriginSystem and OriginID. This way you can keep old IDs for reference. This approach has additional benefit of being able to support new system to import data from, e.g. when merging or exchanging data with another system.