USE [TriggerDemoBase]
GO

SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE 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	
	
/*******  WARNING!!! project name 'ExampleWithTriggers' is used as key, please change this name **********/
	EXEC dbo.spMatchInsert 'ExampleWithTriggers', @idResult, @mergedstring;

END
GO

ALTER TABLE [dbo].[HoldingTable] ENABLE TRIGGER [SendToMatchAfterInserting]
GO

CREATE TRIGGER [dbo].[CopyUniqeRecordsInMasterTable]
   ON  [dbo].[DuplicateCheckResult]
   AFTER INSERT, UPDATE
AS 
BEGIN
	SET NOCOUNT ON;

	Declare @HoldingId int
	Declare @Strong int
	Declare @Duplicates int
	
	SELECT  @HoldingId=HoldingId, @Strong=StrongDuplicate, @Duplicates=Duplicate 
FROM INSERTED;
	
	if (@Strong IS NOT NULL)AND(@Duplicates IS NOT NULL)
	Begin
		IF (@Strong =  0)
		Begin
			DECLARE @id int;
			DECLARE @City nvarchar(max);
			DECLARE @Zip nvarchar(max);
			DECLARE @CompanyName nvarchar(max);
			DECLARE @Industry nvarchar(max);
			DECLARE @Title nvarchar(max);
			DECLARE @State nvarchar(max);
			DECLARE @ContactName nvarchar(max);
			DECLARE @Address nvarchar(max);

			SELECT @id=Id, @City=City, @Zip=Zip, @CompanyName=CompanyName, 
@Industry=Industry, @Title=Title, @State=[State], @ContactName=ContactName, @Address=[Address] 
			FROM HoldingTable WHERE id=@HoldingId;
	
			INSERT INTO MasterTable
				(HoldingId, City, Zip, CompanyName, Industry, Title, [State], ContactName, [Address] )
			values 
				(@id, @City, @Zip, @CompanyName, @Industry, @Title, @State, @ContactName, @Address);		
		End
	End 	
END
GO
ALTER TABLE [dbo].[DuplicateCheckResult] ENABLE TRIGGER [CopyUniqeRecordsInMasterTable]
GO

CREATE TRIGGER [dbo].[AddToCachedTable]
   ON  [dbo].[MasterTable]
   AFTER INSERT
AS 
BEGIN
	SET NOCOUNT ON;
	
	DECLARE @id int;
	DECLARE @City nvarchar(max);
	DECLARE @Zip nvarchar(max);
	DECLARE @CompanyName nvarchar(max);
	DECLARE @Industry nvarchar(max);
	DECLARE @Title nvarchar(max);
	DECLARE @State nvarchar(max);
	DECLARE @ContactName nvarchar(max);
	DECLARE @Address nvarchar(max);

	SELECT @id=HoldingId, @City=City, @Zip=Zip, @CompanyName=CompanyName, 
		@Industry=Industry, @Title=Title, @State=[State], @ContactName=ContactName, @Address=[Address] 
	FROM INSERTED
		
	DECLARE @separator nvarchar(max);
	EXEC @separator = spGetSeparator;

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

	EXEC dbo.spAddToCacheTable 'ExampleWithTriggers', @mergedstring;
END
GO
ALTER TABLE [dbo].[MasterTable] ENABLE TRIGGER [AddToCachedTable]
GO
