Setting multiple scalar variables from a single row in SQL Server 2008?

Question

In a trigger, I have code like:

SET @var1 = (SELECT col1 FROM Inserted);
SET @var2 = (SELECT col2 FROM Inserted);

Is it possible to write the above in a single line? Something conceptually like:

SET (@var1,@var2) = (SELECT col1,col2 FROM Inserted);

Obviously I tried the above, without success; am I just stuck with the first method?

Even if possible, is that a good idea?

Thanks!

Answer 1

yes, use first method.

Or...

SELECT
    @var1 = col1
    ,@var2 = col2
FROM
    Inserted;

Answer 2

No, it is not possible. SET accepts a single target and value. AFAIK.

Answer 3

However, it is a major red flag if you are expecting to set variable values like that in a trigger. It generally means the trigger is poorly designed and needs revision. This code expects there will be only one record in inserted and this is something that is not going to be true in all cases. A multiple record insert or update will have multiple records in inserted and the trigger must account for that (please without using a trigger!!!). Triggers should under no circumstances be written to handle only one-record inserts/updates or deletes. They must be written to handle sets of data.

Example to insert the values from inserted to another table where the trigger is on table1:

CREATE TRIGGER mytrigger on table1
AFTER INSERT
AS

INSERT table2 (field1, field2, field3)
SELECT field1, 'test', CASE WHEN field3 >10 THEN field3 ELSE 0 END
FROM inserted