IF EXISTS (SELECT * FROM sys.triggers WHERE object_id = OBJECT_ID(N'[dbo].[ReservationAddress_Update_Words]'))
DROP TRIGGER [dbo].[ReservationAddress_Update_Words]
GO
CREATE TRIGGER [dbo].[ReservationAddress_Update_Words] ON [dbo].[RESERVATIONADDRESS]
AFTER INSERT, UPDATE
AS
BEGIN
    SET NOCOUNT ON;

	IF UPDATE(FirstName) OR UPDATE(LastName) OR UPDATE(ATTENTIONNAME) OR UPDATE(ADDRESS1) OR UPDATE(Phone) OR UPDATE(Cell) OR UPDATE(Email) OR UPDATE(ZIPCODE) OR UPDATE(City)
	BEGIN

		DELETE FROM dbo.ReservationAddressWord
		FROM dbo.ReservationAddressWord W
		INNER JOIN deleted D ON D.AddressNumber=W.AddressNumber

		;WITH T
		AS
		(
			SELECT 1 ColumnID, AddressNumber, FirstName [Value]FROM inserted WITH (NOLOCK) WHERE FirstName IS NOT NULL AND FirstName<>'' AND (ISNULL(AddressAnonymized,0)=0 OR AddressType<>1)
			UNION ALL
			SELECT 2 ColumnID, AddressNumber, LastName FROM inserted WITH (NOLOCK) WHERE LastName IS NOT NULL AND LastName<>'' AND (ISNULL(AddressAnonymized,0)=0 OR AddressType<>1)
			UNION ALL
			SELECT 3 ColumnID, AddressNumber, ATTENTIONNAME FROM inserted WITH (NOLOCK) WHERE ATTENTIONNAME IS NOT NULL AND ATTENTIONNAME<>'' AND ISNULL(AddressAnonymized,0)=0
			UNION ALL
			SELECT 4 ColumnID, AddressNumber, ADDRESS1 FROM inserted  WITH (NOLOCK) WHERE ADDRESS1 IS NOT NULL AND ADDRESS1<>'' AND (ISNULL(AddressAnonymized,0)=0 OR AddressType<>1)
			UNION ALL
			SELECT 5 ColumnID, AddressNumber, REPLACE(Phone,' ','') FROM inserted  WITH (NOLOCK) WHERE Phone IS NOT NULL AND REPLACE(Phone,' ','')<>'' AND (ISNULL(AddressAnonymized,0)=0 OR AddressType<>1)
			UNION ALL
			SELECT 5 ColumnID, AddressNumber, REPLACE(Cell,' ','') FROM inserted  WITH (NOLOCK) WHERE Cell IS NOT NULL AND REPLACE(Cell,' ','')<>'' AND (ISNULL(AddressAnonymized,0)=0 OR AddressType<>1)
			UNION ALL
			SELECT 6 ColumnID, AddressNumber, Email FROM inserted  WITH (NOLOCK) WHERE Email IS NOT NULL AND Email<>'' AND (ISNULL(AddressAnonymized,0)=0 OR AddressType<>1)
			UNION ALL
			SELECT 7 ColumnID, AddressNumber, ZIPCODE FROM inserted  WITH (NOLOCK) WHERE ZIPCODE IS NOT NULL AND ZIPCODE<>'' AND (ISNULL(AddressAnonymized,0)=0 OR AddressType<>1)
			UNION ALL
			SELECT 8 ColumnID, AddressNumber, City FROM inserted  WITH (NOLOCK) WHERE City IS NOT NULL AND City<>'' AND (ISNULL(AddressAnonymized,0)=0 OR AddressType<>1)
		)
		INSERT INTO dbo.ReservationAddressWord
		SELECT DISTINCT T.AddressNumber, W.[Value], T.ColumnID 
		FROM T
		CROSS APPLY STRING_SPLIT(T.[Value], ' ') W
		WHERE RTRIM(W.Value) <> ''
		AND LEFT(RTRIM(W.Value),1) NOT IN ('#', '&', '?','<','/')
	END
END
