using generated values in instead of triggers

Question

Hi, I have a simple table

CREATE TABLE tItem (PK_Item INT ITENTITY(1,1) NOT NULL, ItemID VARCHAR(15) NOT NULL)

where ItemID is a generated identifier, generally a number, but can differ by customer and other conditions.

ItemID is a result of EXEC @TempID = pGeneralIdentifierRetrieve NULL, NULL, @ItemType

currently, I have insertion of new records written in a stored procedure and it works fine.

I would like to try to rewrite the insert into INSTEAD OF trigger. I have a single problem - how to reference rows in the INSERTED table so that I can update them with the new ItemID. I understand that I can set it in the AFTER INSERT TRIGGER but that would require the ItemID column to be NULLable.

Thanks for any hints

Answer 1

Inside the trigger you can get what you tried to insert

Select * from INSERTED 

I'm not sure where you would get the @ItemType needed by your proc to create the id

create trigger ti_tItem
on tItem
instead of insert
as
begin try
    insert into tItem(ItemID)
    select TempID from [The same code in your procedure]
    where [something] = @ItemType
end try

begin catch
    rollback transaction
end catch

Answer 2

I finally cracked it. It is not a nice solution (to me) but it works:

  SELECT * INTO #MyInserted FROM INSERTED 
    SELECT @RowCount = COUNT(*) FROM #MyInserted WHERE ItemID IS NULL

    WHILE @RowCount > 0 
    BEGIN 
       EXEC @TempID = pGeneralIdentifierRetrieve NULL, NULL, @ItemType 
       UPDATE TOP (1) #MyInserted SET ItemID = @TempID WHERE ItemID IS NULL 
       SET @RowCount = 0 
       SELECT @RowCount = COUNT(*) FROM #MyInserted WHERE ItemID IS NULL
    END 

   INSERT INTO tItem (ItemID) SELECT (ItemID) FROM #MySelected