INSERT trigger not SELECTing rows

Question

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.

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

Answer 1

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

• charliesdad: 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
• Jonathan Fite: 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.