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