Trigger - Not Roger Lloyd Pack

Debbie

 

My best guess would be that something in the @body_text query might be causing sp_send_dbmail to fail though not sending the email nor updating the table. You could try creating a simple table with a varchar field and insert the value of @body_text before trying to send the email and then check the values for the ones that fail.

 

Fernando Margueirat
Business Analyst
The National Ballet of Canada
470 Queens Quay West
Toronto, Ontario
M5V 3K4
P: 416 345 9686 x453
F: 416 345 8323

 

 

Hi Fernando,

Its very simple. The trigger looks like this:

 

 

 

 

 

 

 

 

 

 

 

 

declare

 

@id int, @check varchar(1), @body_text nvarchar(max), @create_date datetime

select

 

@id = id_key from inserted

set

 

@create_date = GETDATE()

set

 

@body_text =(SELECT 'Customer: ' + convert(varchar(10),customer_no) + ' ' + name + ', with Attribute: ' + attribute + ', purchased tickets for Performance: ' + performance + ' on ' + CONVERT(varchar(10), perf_date, 103) + '. Purchased on: ' + convert(varchar(10), order_date, 103) from lt_ovt_report_check where id_key = @id and (email_sent is null or email_sent = ' ' or email_sent = 'N'))

BEGIN

 

EXEC msdb.dbo.sp_send_dbmail

 

 

 @recipients = 'debbie.harland@oldvictheatre.com',

@profile_name

= 'OVIC',

@subject

= 'Illuminate Ticket Purchase',

@body

= @body_text

 

UPDATE LT_OVT_REPORT_CHECK set email_sent = 'Y' where id_key = @id

END

I've got a scheduled task which checks once an hour for tickets bought by Customers with a specific attribute. If it finds one, it does an insert into my local table with all the relevant information. The trigger then picks up that information and puts it into an email.

The trigger has worked today, but didn't fire a couple of times yesterday. The record was inserted into the local table with all the information, just no email sent and the 'email_sent' field was still set to 'N'.

I think it might be something to do with the timing, but I've not figured it yet.

Thanks for looking.

Debbie

Debbie

If you share your code we can give it a look and see what could be causing the problem.

Fernando

This message was sent automatically to you by www.tessituranetwork.com because you subscribed to the Tessitura Technical Forum. You may reply to this message to post to the Technical forum or visit the site to search, read and post to the forums. In the interest of keeping the forum posts from becoming cluttered, we encourage you to delete previous message text from your reply before sending. Thank you!