SQL Trigger After Insert As Merge Not Firing?

Here's my Trigger code on table [dbo].[tbl_Inventory_Temp] which is loaded using SQLBulkCopy from ASP.NET app. The Temp table loads and I don't think the Trigger is firing. I'm using a remote db connection and the debugger won't run, not enough permission.

Note: Currently the [dbo].[tbl_Inventory_TEST] table is empty and I don't know if that effects the 'ON' statement in the Merge below.

Note: It would be nice to have some sort of 'Status' return message if possible to help with errors...

[Trigger Code]
USE [MyDatabaseName]
GO
/****** Object:  Trigger [dbo].[MergeInventory]    Script Date: 10/15/2013 11:58:32 AM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER TRIGGER [dbo].[MergeInventory] ON [dbo].[tbl_Inventory_Temp]

AFTER INSERT AS

BEGIN	

	SET NOCOUNT ON;

		--BEGIN TRAN;

		        --Target Table
			MERGE [dbo].[tbl_Inventory_TEST] AS T

			--Source Table
			USING [dbo].[tbl_Inventory_Temp] AS S

			ON (T.Vin = S.Vin) 

			WHEN NOT MATCHED BY TARGET
				THEN
					INSERT (StockNumber, Vin, [Year], AddedOn, ModifiedOn)
					VALUES (StockNumber, Vin, [Year], GETDATE(), ModifiedOn)

			WHEN MATCHED 
				THEN 
					UPDATE SET 
							StockNumber = S.StockNumber, 
							[Year] = S.[Year], 
                                                        ModifiedOn = GETDATE()
			
			OUTPUT $action, inserted.*, deleted.*;

			DELETE WHERE Vin = @Vin;

		--ROLLBACK TRAN;		
END

Open in new window

WorknHardrAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jogosCommented:
That delete is remainder of something ?
0
WorknHardrAuthor Commented:
It was comment-out, but I do want to incorporate it into this Trigger as well.
0
Scott PletcherSenior DBACommented:
I think you should be using the (pseudo)table "inserted" that is created for use in the trigger rather than the "[dbo].[tbl_Inventory_Temp]" table.  That is:

...
USING inserted AS S
...
0
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

WorknHardrAuthor Commented:
I'm a little embarrassed to say the problem was using the C# SqlBulkCopy and not specifying the "FireTriggers" option like so. Now the trigger fires after the bulk copy insert :)
using (SqlBulkCopy bcp = new SqlBulkCopy(cn, SqlBulkCopyOptions.FireTriggers)) 

Open in new window

0
Scott PletcherSenior DBACommented:
Unless you want to re-process everything in the [dbo].[tbl_Inventory_Temp] table every time the trigger fires, you still need to change the SOURCE for the MERGE statement.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
WorknHardrAuthor Commented:
Understood, thx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.