Good Morning SQL Geeks!
I've set up a trigger on a local table to send an email when a record gets inserted. Works beautifully, except when it doesn't. I can't figure out why it would fire sometimes and not fire other times. All expected records are in the table, I set a flag when the email has been sent and it stays at 'N' when the email hasn't been sent.
Any ideas on why some inserts will fire the trigger and some won't? It's a very simple table and all the relevant information is in the correct fields.
Brain scratching time for a Friday.
Thanks all, Debbie
Debbie
If you share your code we can give it a look and see what could be causing the problem.
Fernando
Hi Fernando,
Its very simple. The trigger looks like this:
declare
@id int, @check varchar(1), @body_text nvarchar(max), @create_date datetime select
select
@id = id_key from inserted
set
@create_date = GETDATE()
@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
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.