Instead of trigger to update primary key

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 th开发者_Python百科e 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

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

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)

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.