I'm working with a application and I have no control over the application source code, only SQL.
The entire process is this:
- Row inserted via an application
- 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
- 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.
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 = 'firstname.lastname@example.org, @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.