CODEWITHSUNDEEP
sqlsql-servert-sqlsql-server-2008
johansson
johansson

Reputation: 1497

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

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!

Upvotes: 4

Views: 2415

Answers (3)

HLGEM
HLGEM

Reputation: 96580

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

Upvotes: 2

Sky Sanders
Sky Sanders

Reputation: 37084

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

Upvotes: 0

gbn
gbn

Reputation: 432311

yes, use first method.

Or...

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

Upvotes: 8

Related Questions