Triggers on TSQL inserting a non-identity table

Question

Hello everybody.

I'm working on a trigger which needs to re-insert its data on another table.

The destination table has a primary key INT NOT NULL WITHOUT Identity, so I have 2 choices:

1. Calculate the maximum and insert from here.
2. Take max value from a sequences table.

I use to always create a table variable with identity and insert shifting from the value calculated before.

CREATE TRIGGER trg
   ON  [dbo].[table] 
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    DECLARE @t TABLE (sec INT IDENTITY(1, 1), id INT)
    DECLARE @ini INT

    SELECT @ini = ISNULL(MAX(id), 0) FROM tableDest
    -- SELECT @ini = value FROM sequencesTable WHERE seqId = 987

    INSERT INTO @t (id) SELECT id FROM inserted

    INSERT INTO tableDest 
     (id, field1, field2) 
    SELECT @ini + t.sec, field1, field2
    FROM @t t
     JOIN inserted ON t.id = inserted.id


    -- SELECT @ini = @ini + MAX(t.sec) FROM @t
    -- UPDATE sequencesTable SET value = @ini WHERE seqId = 987
END

Is it any better way to do this?

Thanks in Advance.

Answer 1

Does tableDest really need an artificial primary key?

Also, concurrency: is there any likelihood of other inserts happening on tableDest between you calculating the value of @ini and actually doing the insert?

Answer 2

Assuming SQL 2005+, you can use ROW_NUMBER():

CREATE TRIGGER trg
   ON  [dbo].[table] 
   AFTER INSERT
AS
BEGIN
    SET NOCOUNT ON;

    INSERT INTO tableDest
        (id, field1, field2)
    SELECT
        Seed.Value + ROW_NUMBER() OVER(ORDER BY Id), 
        field1, 
        field2
    FROM Inserted
    CROSS JOIN (
       SELECT MAX(id) as Value
       FROM tableDest
    ) as Seed
END

Doing a CROSS JOIN instead of getting the seed value directly saves you from the concurrency headache of MAX(Id) changing between getting the value and inserting it. Otherwise, you'd need a SERIALIZABLE transaction to prevent new rows from being inserted into tableDest after you read it.

Answer 3

I guess I'll ask the obvious question, why not set an identity value on the destination table? (Note, if you do this, make darn sure that you are not using @@identity to get the identity value from the source table as that isn't the one you will get - you'll get the identity from the destination table.)

Or could you use the identity from the source table as the id for the other table. That strikes me as the original intent of the design. Otherwise, it might be hard to match these records up to the orginal data.

Answer 4

Hi,

Just to clarify, if I run this statement, will it always give me a unique Bar in table Foo, even though many simultaneous statements are executed at once in parallel?

INSERT INTO Foo (Bar)
  SELECT
    CASE WHEN Bar = -1 
    THEN (SELECT Seed.Value + ROW_NUMBER() OVER(ORDER BY AutoId) 
          FROM inserted CROSS JOIN (SELECT MAX(Bar) as Value FROM Foo) as Seed)
    ELSE Bar
    END

In that case, what is it that makes the statement concurrency safe? Is it because the select that generates the new value for Bar is nested in the insert statement? Or is it the cross join that prevents simultaneous read access to the table Foo? How about the case statement, does it have an impact on the result, in terms of concurrency?

Thanks!