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 = '[email protected],
     @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.

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

1 Answer 1

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 = '[email protected]', -- fixed missing quote here
     @query = N'SELECT * FROM inserted;',
     @subject = 'New Item Inserted';
END
GO
2
  • 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 Nov 27, 2018 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. Nov 27, 2018 at 20:59

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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