x

Need to put Query result into Variable for sendmail

I have a SQL 7 database where I want to set a trigger that takes the inserted record into a variable so that I can send it elsewhere using sp_sendmail? Can't use xp_senddmail for this because of the way everything is set up. Is this possible?
more ▼

asked Aug 21, 2012 at 08:35 PM in Default

Starhawk gravatar image

Starhawk
0 1 1 2

Thanks for the replies. There is an orders table and a line items table. The order table would be a single row whereas there might be one or one rows in the line items tables. Most orders would not have a lot of line items. This is an old system (obviously) that we are in the process of replacing but until that happens I have to make it work a little while longer.
Aug 22, 2012 at 02:30 PM Starhawk
(comments are locked)
10|1200 characters needed characters left

3 answers: sort voted first

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.

more ▼

answered Aug 21, 2012 at 09:51 PM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.4k 16 19 33

(comments are locked)
10|1200 characters needed characters left

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.
more ▼

answered Aug 22, 2012 at 01:17 PM

Kev Riley gravatar image

Kev Riley ♦♦
52.8k 47 49 76

(comments are locked)
10|1200 characters needed characters left
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.
more ▼

answered Aug 22, 2012 at 06:41 PM

Starhawk gravatar image

Starhawk
0 1 1 2

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x21

asked: Aug 21, 2012 at 08:35 PM

Seen: 918 times

Last Updated: Aug 22, 2012 at 06:41 PM