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.

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