Need to put Query result into Variable for sendmail

I don't know how sp_sendmail works, so I can't tell how you'd go about with that specific procedure.

But I'll try to answer. Yes, you can save results from a query into a variable. But be extremely aware that an update-, insert- or delete-trigger is fired once per operation, not once per row. If you make an insert of 1000 rows in one insert-statement, you'll get the trigger fired once, not 1000 times.

In the trigger, you're able to read which rows have been affected using the INSERTED and DELETED virtual tables. In an INSERT-trigger, you only have the INSERTED table filled. In a DELETE-trigger, you only have the DELETED table. In an UPDATE-trigger, you have both. For UPDATE, you have both because an UPDATE is esentially a DELETE followed by an INSERT (logically, in real life UPDATE is atomic).

These virtual tables are identical to the table for which the trigger has been fired. So if you insert ten rows into TableA, the INSERTED virtual table will have ten rows and the same columns as TableA. Using these rows in the INSERTED-table, you can find out what has been inserted.

Yes, but as Magnus says, you need to be sure that your 'result' is a one row result.

I'd also say that you would need to handle this differently if the "inserted record" has more than one column. Can you give a little more detail as to what this variable holds?

You can handle multiple rows and columns, for example by inserting the results into a temporary table structure, but you have to be aware of scope - a temporary table created before a call to sp_sendmail is not in scope to the execution of sp_sendmail, you may have to use global temporary tables.

The table , both tables have more than one column. The order table should return just one row. The item table will return multiple rows but not a lot. Thanks for the help.