Getting the Id of a row updated in Sql Server for a table with trigger

Question

There is good answer here: https://stackoverflow.com/a/1610530/630169 However if updated table have a trigger that does not work as SQL Server produces error: The target table 'my_table' of the DML statement cannot have any enabled triggers if the statement contains an OUTPUT clause without INTO clause. What to do if table has trigger?

Answer 1

Find the answer, need to use INTO option like mentioned in documentation: http://msdn.microsoft.com/en-us/library/ms177564.aspx, example:

DECLARE @MyTableVar TABLE
(
    id INT
);
UPDATE my_table 
SET column = value
OUTPUT INSERTED.primaryId INTO @MyTableVar
WHERE idColumn = idValue;
SELECT * FROM @MyTableVar