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

Question

In the trigger, I have code like:

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

Is it possible to write above in one line? Something conceptual:

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

Obviously, I tried above without success; Am I just stuck in the first method?

Even if possible, is that a good idea?

Thanks!

Answer 1

yes, use the first method.

Or...

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

Answer 2

However, this is the main red flag if you expect to set variable values ​​similar to this in a trigger. This usually means that the trigger is poorly designed and needs to be reviewed. This code expects only one record to be inserted, and in all cases this will not be true. Insertion or updating of several records will contain several records, and the trigger should take this into account (without using the trigger !!!). Triggers should never be written to handle only single-line inserts / updates or deletes. They must be recorded to process data sets.

Example for inserting values ​​from inserted into another table, where the trigger is in 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 

Answer 3

No, It is Immpossible. SET takes on a single purpose and meaning. AFAIK.