email with trigger inserted row

I'm trying to create a trigger which, upon a row insert, an email is sent containing some of the inserted row information. Apparently the built-in stored procedure for email starts it's own session, so I can't use the local variables of the trigger. My solution was to create a temp table, copy the inserted row in, then refer to that from the email SP, then drop the table at the end. When I try to insert a row, it runs for a very long time, then gives an error message saying that it timed out. It was suggested I add COMMIT TRANSACTION in to force it to commit the data to the temp table. Doing this, it gives an error, saying "The transaction ended in the trigger. The batch has been aborted. Mail queued." In the table view, I'm forced to hit esc and abort the insertion. However, if I refresh the table, the row has been inserted ok, and the email does get sent with the inserted row.

Code:
--

CREATE TRIGGER [newTicket_notify]

ON [sysdba].[TICKET]

AFTER INSERT

AS

BEGIN

-- SET NOCOUNT ON added to prevent extra result sets from

-- interfering with SELECT statements.

SET NOCOUNT ON;

create table insertedTemp

(

TICKETID char(12),

ACCOUNTID char(12),

ACCOUNT varchar(128),

DIVISION varchar(64),

EMAIL varchar(128),

MAINPHONE varchar(32)
)

declare @.ticketID char(12), @.accountID char(12), @.account varchar(128),

@.division varchar(64), @.email varchar(128), @.mainphone varchar(32)

select @.ticketID = TICKETID, @.accountID = ACCOUNTID

from inserted

select @.account = ACCOUNT, @.division = DIVISION, @.email = EMAIL,

@.mainphone = MAINPHONE

from sysdba.ACCOUNT

where @.accountID = ACCOUNTID

insert into insertedTemp values (@.ticketID, @.accountID, @.account,

@.division, @.email, @.mainphone)

commit transaction

EXEC msdb.dbo.sp_send_dbmail

@.profile_name = 'Test',

@.recipients = 'user@.test.com',

@.body = 'Inserted row info',

@.subject = 'DB Test',

@.query = 'select TICKETID [Ticket ID], ACCOUNTID [Account ID],

ACCOUNT [Account], DIVISION [Division], EMAIL [Email],

MAINPHONE [Mainphone]

from dbo.insertedTEMP',

@.execute_query_database = 'database',

@.attach_query_result_as_file = '0';

drop table insertedTEMP

END

You're really doing a little too much with the trigger here. A trigger should be a quick thing.

Have you considered using Service Broker for this? Or even just a SQL Job which looks for new rows and does the emailing there? You might not get an immediate response (although if you make the SQL Job run every 10 seconds it will feel pretty immediate), but at least your initial insertion will complete happily.

Rob|||

SQL Server 2k5 uses the Sevice broker already for mail sending.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||Agreed. And it will make the trigger you write look so much easier. Can you send multiple rows to a Service Broker queue at once? If not I would still create an email queue table and build a job to send emails. Emails aren't immediate things no matter what, and it will be a lot easier to debug an email queue not working if you don't have the added excitement of your ticket system failing because of it.sql

email on insert

Hi all,

I wanted sql server to shoot an email upon insert into a table. I treated a
trigger on that table as below.

CREATE TRIGGER [emailoninsert] ON [dbo].[table_name]
FOR INSERT
AS
exec sp_sendSMTPmail 'user@.user.com', 'New records are inserted in
table_name table', 'Please investigate and take necessary actions.',
@.cc='', @.BCC = '',
@.Importance=1,
@.Attachments='', @.HTMLFormat = 0,@.From =
'notification@.sqlserver.com'

Is this solution a good method?

Thanks,

Guju"Guju" <patelroshanr@.yahoo.com.au> wrote in message
news:42508c0c_1@.news.iprimus.com.au...
> Hi all,
> I wanted sql server to shoot an email upon insert into a table. I treated
a
> trigger on that table as below.
> CREATE TRIGGER [emailoninsert] ON [dbo].[table_name]
> FOR INSERT
> AS
> exec sp_sendSMTPmail 'user@.user.com', 'New records are inserted in
> table_name table', 'Please investigate and take necessary actions.',
> @.cc='', @.BCC = '',
> @.Importance=1,
> @.Attachments='', @.HTMLFormat = 0,@.From =
> 'notification@.sqlserver.com'
>
> Is this solution a good method?

No.

It will greatly slow down insert speeds.

> Thanks,
> Guju|||Guju wrote:
> Hi all,
> I wanted sql server to shoot an email upon insert into a table. I
treated a
> trigger on that table as below.
> CREATE TRIGGER [emailoninsert] ON [dbo].[table_name]
> FOR INSERT
> AS
> exec sp_sendSMTPmail 'user@.user.com', 'New records are inserted in
> table_name table', 'Please investigate and take necessary actions.',
> @.cc='', @.BCC = '',
> @.Importance=1,
> @.Attachments='', @.HTMLFormat = 0,@.From =
> 'notification@.sqlserver.com'
>
> Is this solution a good method?
> Thanks,
> Guju

It may be a better idea to create a script that checks for new records
in the table every x number of hours (run it as an sql agent job).
the script may save the last record id that it already saw in a table
for this purpose.
As mentioned above, the solution you implemented means the email is
sent at the expense of the insert statement , making it horribly slow.
My way you can also send one mail if 10 records were inserted in stead
of 10, with the data of all 10, which you may trust me is more useful
to the sorry person actually receiving these mails.

hope this helps.

Tzvika|||Is it possible for you to post a sample script..I am attempting to get
the same result as the author.|||Is it possible for you to post a sample script..I am attempting to get
the same result as the author.

Effective Date selection in SQL

Problem: Selecting the most appropriate row based upon the row's effective date (a user defined field of the row) verses the current date and time.

Platform: Microsoft SQL Server 2000

Details:
In a table of products, each record is a product, or a version of a product. These products or versions of products are "Effective Dated", meaning, a row is activated by the date it becomes effective by (do not be confused by an active row, and an active product - active row is the currently effective row, an active product is a flag set by the user as to define whether the product is active when it's effective date is reached). Example:

Product 1 (version 1)
Effective Date: 6/1/2003 12:00:00 AM
Price: $30
Status: Active

Product 1 (version 2)
Effective Date: 7/11/2003 12:00:00 AM
Price: $20
Status: Active

Product 1 (version 3)
Effective Date: 12/25/2003 12:00:00 AM
Price: $15
Status: Active

Product 1 (version 4)
Effective Date: 1/1/2004 12:00:00 AM
Status: Inactive

In the above case, this SINGLE product has four records in the product table representing different versions. Product 1 version 1 has expired since Product 1 version 2 is effective today (7/11). Product 1 version 3 and version 4, however, have not come about yet due to their effective dating in the future. Product versions 1 through 3 are all active, and the product is no longer available after 1/1/2004, when version 4 becomes effective and the status changes to "Inactive".

The problem I'm experiencing with my SQL is that I cannot differentiate between the version effective currently and the future versions.

My current SQL looks as such:

SELECT sProdName
FROM tblProducts
WHERE NOT EXISTS
(
SELECT P.cProdCode, P.dtEffDate
FROM tblProducts AS P
WHERE
tblProducts.cProdCode = P.cProdCode AND
tblProducts.dtEffDate < P.dtEffDate
)
AND bActive = 1
AND cProdCode = 'somecode'
ORDER BY sProdName

In the above SQL, bActive is a flag that declares this product as an active product (verses inactive) and cProdCode is a code that all versions of the Product share. Ex:

iID: 1
sProdName: Product 1
cProdcode: PROD1
bActive: 1
dtEffDate: 6/1/2003 12:00:00 AM

iID: 2
sProdName: Product 1
cProdcode: PROD1
bActive: 1
dtEffDate: 7/11/2003 12:00:00 AM

iID: 3
sProdName: Product 1
cProdCode: PROD1
bActive: 0
dtEffDate: 1/1/2004 12:00:00 AM

The above SQL is effective in ignoring the expired versions of the product, but returns the current and future versions. I've attempted to massage the SQL to ignore the expired and future versions (leaving me with the single, currently active, version).

Ideally, I'd like to do this without having to create flags for the records that deam them as past, present, future, or having to export versions out into an archive table. This should be able to be determined by strictly SQL, but haven't been able to get the data to return as I'd like it..

In the end, the final result would be a single record of the version of the product which is currently effective, and alertnately, the ability to retrieve a set of records listing all of the currently effective active products.

Any ideas?Now, I should mention that I have a version of this SQL that works, but I suppose my ultimate question is "Is this the most efficient and proper way to do this or is there a better way?"

Here is the SQL that does work, but it nags at me with the use of TOP and ORDER BY. It doesn't seem as though this is the proper elegant solution:

SELECT TOP 1 *
FROM tblProducts AS P
WHERE
P.iProdID IN
(
SELECT iProdID
FROM tblProducts P2
WHERE
P2.dtEffDate < GETDATE()
)
AND P.bActive = 1
AND P.cProdCode = 'somecode'
ORDER BY P.dtEffDAte DESC

If I do not use TOP, it returns every version of a product except the future versions. If I do not use ORDER BY it returns them in an order inconsistant with their effective date. So combined together, the records are sorted most current->least current, then chopped at the top for the most current effective dated product.

Now, this comes into play heavily when we start talking about retrieving the most recent version of all of the products. Sure the above works when getting the most current version of a single product, but (due to the "TOP") does not work properly when attempting to retrieve the most current version of all active products. The above SQL's "TOP" directive is in there to drop off the expired version of the product, and in this case, TOP drops off more then just expired versions of products.. it drops everything except the lucky record at the top of the set.|||i really don't understand your various definitions of active, inactive, flags, effective dates, etc.

your design sounds fairly complicated

however, i did pick up on one thing

you said your sql works, but only for one product at a time, and "does not work properly when attempting to retrieve the most current version of all active products"

okay, i think i can translate your sql so it will work for all products:
select *
from tblProducts AS P
where dtEffDate =
( select max(dtEffDate)
from tblProducts
where cProdCode = P.cProdCode
and dtEffDate < GETDATE()
and bActive = 1
)
and bActive = 1 this is a correlated subquery so it picks the highest date in each group, where the group is defined as all product rows with the same cProdCode (the P inside the subquery is the correlation variable)

let me know if it works

rudy
http://r937.com/|||Hi Rudy,

I apologize for the complexity of the project, but I think you nailed the solution. I've done some cursory testing, and just looking over the SQL, it looks right on. Tonight I'll take some time to do some more testing, but it looks good. I hadn't even thought of using the MAX function. Thanks for your assistance, I appreciate you taking the time to go through all my details (even if they were a bit long and confusing) and coming up with a solid solution.|||It looks good.. I've done some more thorough testing and it seems to work well. Thanks again!|||It looks good.. I've done some more thorough testing and it seems to work well. Thanks again!