0

I'm working with a application and I have no control over the application source code, only SQL.

The entire process is this:

  1. Row inserted via an application
  2. Vendor trigger fires and calls an SP to insert the new row into my table. This new row is returned when I do a SELECT
  3. I have an INSERT trigger on my table that selects the row and executes sp_send_dbmail to email the row just inserted (and will then truncate my table when live)

The issue is that I get the email (so the INSERT is picked up and the trigger executes) but it's blank.

If I then INSERT a second row, the trigger fires and I get an email containing the first row inserted.

My trigger:

ALTER TRIGGER [dbo].[SendEmail_INS]
ON [dbo].[tbl_UNIT_INS]
FOR INSERT
-- AFTER INSERT
AS

DECLARE @thequery NVARCHAR(MAX)

    SELECT @thequery =
    'SET NOCOUNT ON
     SELECT  contract,
         cont_line,
         site_ref,
         comp_id,
         cont_line as cont_line2,
         item,
         qty,
         ser_num,
         cust_num,
         description,
         unit_stat_code
    FROM tbl_UNIT_INS'

EXEC msdb.dbo.sp_send_dbmail
     @query_result_header = 0,
     @exclude_query_output = 1,
     @profile_name = 'Outbound',
     @recipients = 'me@email.com,
     @query = @thequery,
     @subject = 'New Item Inserted'

I've tried using AFTER INSERT as well - same result.

It seems the trigger is firing before the row is inserted but the fact I get an email means the insert has happened so the insert has completed?

SQL Server 2012.

  • 1
    Not sure if it's actually part of your query but you're missing an end quote after the @recipients email address. – Shooter McGavin Nov 26 '18 at 17:30
  • Don't you need delimiters when using multiple statements in @query? – mustaccio Nov 26 '18 at 18:47
  • 1
    What happens if a rollback needs to occur and you have already sent the email? – Michael Kutz Nov 26 '18 at 21:49
2

I think you will have better luck pulling from inserted instead of from the base table (and of course fixing the typo that should have prevented compilation), but you should also consider not using e-mail from a trigger at all.

ALTER TRIGGER [dbo].[SendEmail_INS]
ON [dbo].[tbl_UNIT_INS]
FOR INSERT -- AFTER INSERT is the exact same thing as FOR INSERT.
AS
BEGIN
  EXEC msdb.dbo.sp_send_dbmail
     @query_result_header = 0,
     @exclude_query_output = 1,
     @profile_name = 'Outbound',
     @recipients = 'me@email.com', -- fixed missing quote here
     @query = N'SELECT * FROM inserted;',
     @subject = 'New Item Inserted';
END
GO
  • Thanks for the suggestions. I've taken Aaron's suggestion and moved the sp_send_dbmail functionality into a SP and called it from trigger. All working. Thanks – charliesdad Nov 27 '18 at 14:01
  • you are still e-mailing from the trigger, even if it's one layer removed. I think Aaron was trying to convince you to accomplish your task a different way. – Jonathan Fite Nov 27 '18 at 20:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.