USE [FlowingExampleWithTriggers]
GO
/****** Object:  Trigger [dbo].[SendToMatchAfterInserting]    Script Date: 6/27/2018 1:15:23 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


ALTER TRIGGER [dbo].[SendToMatchAfterInserting]
   ON  [dbo].[HoldingTable]
   AFTER INSERT
AS 
BEGIN	
	SET NOCOUNT ON;

	DECLARE @id INT
	DECLARE @State nvarchar(max)
	DECLARE @ContactName nvarchar(max)

	SELECT  @id=Id, @State=[State], @ContactName=[ContactName]  FROM    INSERTED;
		
	INSERT INTO DuplicateCheckResult
				(HoldingId, StrongDuplicate, Duplicate) values (@id, -1, -1);

	DECLARE @idResult bigint;
	SELECT @idResult=id FROM DuplicateCheckResult WHERE HoldingId=@id;

	DECLARE @separator nvarchar(max);
	EXEC @separator = spGetSeparator;

	DECLARE @mergedstring nvarchar(max);
	SET @mergedstring=@State + @separator + @ContactName	

	print @mergedstring

	EXEC dbo.spMatchInsert 'ExampleWithTriggers', @idResult, @mergedstring;

	--UPDATE dbo.Example1_ins_res SET StrongDuplicate=2, Duplicate=3 WHERE id=@idResult;
	/*
	DECLARE cursorElement CURSOR FOR
            SELECT  Id, Fullname, company
            FROM    INSERTED	
	
	OPEN cursorElement

		-- get the first inserted row
		FETCH NEXT FROM cursorElement INTO @id, @Fullname, @Company

		-- For every inserted row:
		WHILE ( @@FETCH_STATUS = 0 )
		BEGIN
			-- 1. create a row for results into the result table
			INSERT INTO dbo.Example1_ins_res 
				(idParent, StrongDuplicate, Duplicate) values (@id, -1, -1);
			-- find primary key the created row
			DECLARE @idResult bigint
			DECLARE resultCursor CURSOR FOR	SELECT id FROM dbo.Example1_ins_res WHERE idParent=@id;
			OPEN resultCursor
				FETCH NEXT FROM resultCursor INTO @idResult
			CLOSE resultCursor			
			DEALLOCATE resultCursor

			-- 2. call the stored procedure
			DECLARE @mergedstring nvarchar(max);
			SET @mergedstring=@Fullname + '!!&!!' + @Company		
			EXEC dbo.spMatchInsert "MyDemoDb", @idResult, @mergedstring

			-- get next row
			FETCH NEXT FROM cursorElement INTO @id, @Fullname, @Company
		END         
	CLOSE cursorElement
	DEALLOCATE cursorElement
	*/
END

